We have many SSIS packages stored on a network drive. Every package has a connection manager called config_connection and all the package configurations are stored in a database that config_connection manager points to.
On execution we would like to override the connection string being used for the config_connection connection manager. This should be possible using the /connection parameter on dtexec.exe
This only actually works in one situation though. If the original connection string is inaccessible then eventually the override works after it fails to connect to the existing connection string.
I've used dtexecui to create the command line execution and it works when the original connection is blank so it doesn't seem to be an issue with the command line.
DTExec.exe /FILE "\"C:\PackageName.dtsx\"" /CONNECTION "\"config_connection\"";"\"Provider=MSOLEDBSQL.1;Persist Security Info=False;Data Source=ServerName;Use Encryption for Data=True;Authentication=ActiveDirectoryIntegrated;Initial Catalog=ETL_Management\"" /CHECKPOINTING OFF /REPORTING V /CONSOLELOG XMT
I've tested this on dtexec 2012, 2017 and 2019 and all have the same behaviour. I've done quite a bit of googling and there was a link on another post here that may have explained some more detail but it was a broken link and a search didn't help.
If anyone has any ideas or links to documentation that would explain this behaviour I'd appreciate it.
Edit 2023-05-23:
I've created a brand new package with 3 connections: config_connection, logging_ole and Recyclebin_OLE
At design time all 3 of these connect to the same server. The xml of the configuration in the package is:
<DTS:Configurations>
<DTS:Configuration
DTS:ConfigurationString=""config_connection";"[dbo].[DWConfigurations]";"Logging_OLE";"
DTS:ConfigurationType="7"
DTS:CreationName=""
DTS:DTSID="{E9527CAF-6936-40A2-BFEC-415F80CA1BBF}"
DTS:ObjectName="Logging_OLE" />
<DTS:Configuration
DTS:ConfigurationString=""config_connection";"[dbo].[DWConfigurations]";"RecycleBin_OLE";"
DTS:ConfigurationType="7"
DTS:CreationName=""
DTS:DTSID="{A76E0FC1-E657-482E-933B-3A8E56CFE436}"
DTS:ObjectName="RecycleBin_OLE" />
</DTS:Configurations>
I've added a variable vServerName with the type of string.
Then I've created 3 execute sql tasks which set the value of vServerName to the server name returned by
SELECT @@SERVERNAME AS ServerName
then used the script task to echo the value.
running the package without the override returns the following result as expected.
Started: 14:01:49
Info: 2023-05-23 14:01:49.73
Code: 0x40016040
Source: Package1
Description: The package is attempting to configure from SQL Server using the configuration string ""config_connection";"[dbo].[DWConfigurations]";"Logging_OLE";".
End Info
Info: 2023-05-23 14:01:49.94
Code: 0x40016040
Source: Package1
Description: The package is attempting to configure from SQL Server using the configuration string ""config_connection";"[dbo].[DWConfigurations]";"RecycleBin_OLE";".
End Info
Info: 2023-05-23 14:01:49.96
Code: 0x40016040
Source: Package1
Description: The package is attempting to configure from SQL Server using the configuration string ""config_connection";"[dbo].[DWConfigurations]";"Logging_OLE";".
End Info
Info: 2023-05-23 14:01:49.98
Code: 0x40016040
Source: Package1
Description: The package is attempting to configure from SQL Server using the configuration string ""config_connection";"[dbo].[DWConfigurations]";"RecycleBin_OLE";".
End Info
Progress: 2023-05-23 14:01:51.81
Source: Execute SQL Task config_connection
Executing query "SELECT @@SERVERNAME AS ServerName".: 100% complete
End Progress
Info: 2023-05-23 14:01:51.84
Code: 0x00000000
Source: Script Task config_connection SCR Echo Back
Description: User::vServerName : az-bpsqmiXX.XXXXXXXX.database.windows.net
End Info
Progress: 2023-05-23 14:01:52.02
Source: Execute SQL Task Logging_OLE
Executing query "SELECT @@SERVERNAME AS ServerName".: 100% complete
End Progress
Info: 2023-05-23 14:01:52.02
Code: 0x00000000
Source: Script Task logging_ole SCR Echo Back
Description: User::vServerName : END-BPSQ08-VS01\INSTANCE01
End Info
Progress: 2023-05-23 14:01:52.12
Source: Execute SQL Task Recyclebin_OLE
Executing query "SELECT @@SERVERNAME AS ServerName".: 100% complete
End Progress
Info: 2023-05-23 14:01:52.12
Code: 0x00000000
Source: Script Task Recyclebin_OLE SCR Echo Back
Description: User::vServerName : END-BPSQ08-VS01\INSTANCE01
End Info
DTExec: The package execution returned DTSER_SUCCESS (0).
Started: 14:01:49
Finished: 14:01:52
Elapsed: 2.531 seconds
I have confirmed repeatedly that the configuration stored in the table are pointing to the correct xml path
\Package.Connections[RecycleBin_OLE].Properties[ConnectionString]
\Package.Connections[Logging_OLE].Properties[ConnectionString]
Interestingly if in the designer I blank the connection string for config_connection, do this in offline mode if you don't want designer to freeze while it checks the invalid connection string, then all 3 are overridden correctly. Even though I didn't blank out the others.
Started: 14:03:40
Info: 2023-05-23 14:03:40.44
Code: 0x40016040
Source: Package1
Description: The package is attempting to configure from SQL Server using the configuration string ""config_connection";"[dbo].[DWConfigurations]";"Logging_OLE";".
End Info
Error: 2023-05-23 14:03:40.45
Code: 0xC0202009
Source: Package1 Connection manager "config_connection"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E4D Description: "Invalid authorization specification".
End Error
Info: 2023-05-23 14:03:40.45
Code: 0x40016040
Source: Package1
Description: The package is attempting to configure from SQL Server using the configuration string ""config_connection";"[dbo].[DWConfigurations]";"RecycleBin_OLE";".
End Info
Error: 2023-05-23 14:03:40.45
Code: 0xC0202009
Source: Package1 Connection manager "config_connection"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E4D Description: "Invalid authorization specification".
End Error
Warning: 2023-05-23 14:03:40.45
Code: 0x80012059
Source: Package1
Description: Failed to load at least one of the configuration entries for the package. Check configuration entries for "Logging_OLE; RecycleBin_OLE" and previous warnings to see descriptions of which configuration failed.
End Warning
Info: 2023-05-23 14:03:40.45
Code: 0x40016040
Source: Package1
Description: The package is attempting to configure from SQL Server using the configuration string ""config_connection";"[dbo].[DWConfigurations]";"Logging_OLE";".
End Info
Info: 2023-05-23 14:03:41.97
Code: 0x40016040
Source: Package1
Description: The package is attempting to configure from SQL Server using the configuration string ""config_connection";"[dbo].[DWConfigurations]";"RecycleBin_OLE";".
End Info
Progress: 2023-05-23 14:03:43.22
Source: Execute SQL Task config_connection
Executing query "SELECT @@SERVERNAME AS ServerName".: 100% complete
End Progress
Info: 2023-05-23 14:03:43.24
Code: 0x00000000
Source: Script Task config_connection SCR Echo Back
Description: User::vServerName : az-bpsqmiXX.XXXXXXXX.database.windows.net
End Info
Progress: 2023-05-23 14:03:44.32
Source: Execute SQL Task Logging_OLE
Executing query "SELECT @@SERVERNAME AS ServerName".: 100% complete
End Progress
Info: 2023-05-23 14:03:44.32
Code: 0x00000000
Source: Script Task logging_ole SCR Echo Back
Description: User::vServerName : az-bpsqmiXX.XXXXXXXX.database.windows.net
End Info
Progress: 2023-05-23 14:03:45.40
Source: Execute SQL Task Recyclebin_OLE
Executing query "SELECT @@SERVERNAME AS ServerName".: 100% complete
End Progress
Info: 2023-05-23 14:03:45.41
Code: 0x00000000
Source: Script Task Recyclebin_OLE SCR Echo Back
Description: User::vServerName : az-bpsqmiXX.XXXXXXXX.database.windows.net
End Info
DTExec: The package execution returned DTSER_SUCCESS (0).
Started: 14:03:40
Finished: 14:03:45
Elapsed: 5.125 seconds
It's such a strange issue!
You're using a classic configuration pattern for the package deployment model. One bootstraps to the actual configuration repository and then consumes values from there.
I built what I assume is a valid repro of your situation
I have a connection named
config_connectionRather than use Configuration, I run a query against the database asking what the Server Name is. The value is then assigned into ServerName.You can see the design-time value of
Test.The SCR Echo Values does nothing more than raise an Information event allowing me to "print" values to the Output window. https://billfellows.blogspot.com/2016/04/biml-script-task-test-for-echo.html
As a control, I ran the following
The results are
The variable
User::ServerNameis populated with ERECH\dev2017Now, updating the previous dtexec call with an explicit Connection value
That yields
We see the value for my connection has changed to "ERECH\DEV2019UTF8"
And because I horde databases
Yup, we are definitely switching the servers around.
Adding extraneous quotes as your example shows
has no adverse affect.
For a package where this does not work, open it up in a modern-ish version of Visual Studio and verify that there is not a little
fxglyph next to the connection managerThat icon means that there is an expression on the Connection Manager. If I put an Expression on the ServerName property of .\dev2017, the same dtexec call I just used with an explicit /CONNECTION no longer changes the server name as the local expression overrides the external. So check and see if that's a factor in your packages.
Some of this behaviour changed between 2005 to 2008 and John Welch calls it out https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008/cc671625(v=msdn.10)?redirectedfrom=MSDN
At this point, I don't deal with Package Configuration but for StackOverflow questions and so I've forgotten a lot of how this stuff works. But feel free to pop a screenshot of a package, the Configuration screen and probably the output from running the dtexec call like I have and maybe we can figure out where things have gone awry. That or copy a package and rip it down to something insignificant like my sample package and confirm the minimal reproduction does/doesn't work as expected.
Updates 2023-05-19
The comment had me excited as it seemed there might have been a sequence issue but things are working as I'd expect them.
I added 2 Variables: ConfiguredValue1 and ConfiguredValue2 both Int32 and initialized to 0.
My config_connection points to tempdb so I ran the following command to generate the config table + values.
I ran this on dev2017 with values of 1 and 10 and the dev2019UTF8 instance used values of 2 and 20 (below)
I then added the two ConfiguredValues to the Echo Values task and ran the package as-is. I see values of 0 and 0, which is expected as the Configuration isn't wired up yet.
Into the Configuration menu I go, and when I select the filter, it reports that can overwrite or re-use existing --- I choose the latter.
Open/Close the package and I see that it's pulled the values from configuration as my variables now show 1/10 but let's run it.
Supplying an explicit Connection configuration does what is expected---it consumes values from dev2019UTF8 and I see the 2/20 values in the log.
When you run the package, use the Verbose flag /rep v or at least EIW (errors, information, warning) and see if anything "pops" out at you. You ought to see the following for each item it configures
The other thing to watch out for is a missed configuration. I know XML/.dtsconfig not being reachable/existing would raise a warning and not error out which SSIS would "helpfully" use the design-time values which lead to Bad Things[TM] in production.