DTEXEC override of connection string is not working

Question

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="&quot;config_connection&quot;;&quot;[dbo].[DWConfigurations]&quot;;&quot;Logging_OLE&quot;;"
      DTS:ConfigurationType="7"
      DTS:CreationName=""
      DTS:DTSID="{E9527CAF-6936-40A2-BFEC-415F80CA1BBF}"
      DTS:ObjectName="Logging_OLE" />
    <DTS:Configuration
      DTS:ConfigurationString="&quot;config_connection&quot;;&quot;[dbo].[DWConfigurations]&quot;;&quot;RecycleBin_OLE&quot;;"
      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 have a lot more quotes than the example on microsoft page, could it be something with that? Otherwise, it does state stuff here: https://learn.microsoft.com/en-us/sql/integration-services/packages/legacy-package-deployment-ssis?view=sql-server-ver16 You can't use the /Set or the /Connection option to override single values that are also set by a configuration. — siggemannen, May 17 '23 at 15:45
The quotes come out of the dtexecui tool. Thanks for the link I'll see what I can fathom out. — Bee_Riii, May 19 '23 at 09:34

billinkc · Answer 1 · 2023-05-19T16:48:55.970

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_connection Rather 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

"C:\Program Files (x86)\Microsoft SQL Server\140\DTS\Binn\DTExec.exe" /file C:\Users\bfellows\source\repos\SSIS_2022\SSIS_2022\SO_76272398.dtsx /rep eiw

The results are

Microsoft (R) SQL Server Execute Package Utility
Version 14.0.3460.9 for 32-bit
Copyright (C) 2017 Microsoft. All rights reserved.

Started:  4:42:03 PM
Info: 2023-05-17 16:42:04.16
   Code: 0x00000000
   Source: SCR Echo Values SCR Echo Back
   Description: User::ServerName=>ERECH\DEV2017
End Info
DTExec: The package execution returned DTSER_SUCCESS (0).
Started:  4:42:03 PM
Finished: 4:42:04 PM
Elapsed:  0.515 seconds

The variable User::ServerName is populated with ERECH\dev2017

Now, updating the previous dtexec call with an explicit Connection value

"C:\Program Files (x86)\Microsoft SQL Server\140\DTS\Binn\DTExec.exe" /file C:\Users\bfellows\source\repos\SSIS_2022\SSIS_2022\SO_76272398.dtsx /rep eiw /connection "config_connection";"Data Source=.\dev2019utf8;Initial Catalog=tempdb;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;"

That yields

Microsoft (R) SQL Server Execute Package Utility
Version 14.0.3460.9 for 32-bit
Copyright (C) 2017 Microsoft. All rights reserved.

Started:  4:47:28 PM
Info: 2023-05-17 16:47:28.60
   Code: 0x00000000
   Source: SCR Echo Values SCR Echo Back
   Description: User::ServerName=>ERECH\DEV2019UTF8
End Info
DTExec: The package execution returned DTSER_SUCCESS (0).
Started:  4:47:28 PM
Finished: 4:47:28 PM
Elapsed:  0.469 seconds

We see the value for my connection has changed to "ERECH\DEV2019UTF8"

And because I horde databases

"C:\Program Files (x86)\Microsoft SQL Server\140\DTS\Binn\DTExec.exe" /file C:\Users\bfellows\source\repos\SSIS_2022\SSIS_2022\SO_76272398.dtsx /rep eiw /connection "config_connection";"Data Source=.\dev2019express;Initial Catalog=tempdb;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;"

Microsoft (R) SQL Server Execute Package Utility
Version 14.0.3460.9 for 32-bit
Copyright (C) 2017 Microsoft. All rights reserved.

Started:  4:48:53 PM
Info: 2023-05-17 16:48:53.88
   Code: 0x00000000
   Source: SCR Echo Values SCR Echo Back
   Description: User::ServerName=>ERECH\DEV2019EXPRESS
End Info
DTExec: The package execution returned DTSER_SUCCESS (0).
Started:  4:48:53 PM
Finished: 4:48:53 PM
Elapsed:  0.453 seconds

Yup, we are definitely switching the servers around.

Adding extraneous quotes as your example shows

"C:\Program Files (x86)\Microsoft SQL Server\140\DTS\Binn\DTExec.exe" /file C:\Users\bfellows\source\repos\SSIS_2022\SSIS_2022\SO_76272398.dtsx /rep eiw /connection ""config_connection"";""Data Source=.\dev2019express;Initial Catalog=tempdb;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;""

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 fx glyph next to the connection manager

That 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)

USE tempdb;

DROP TABLE IF EXISTS dbo.SO_76272398;
CREATE TABLE dbo.SO_76272398
(
    ConfigurationFilter nvarchar(255) NOT NULL
,   ConfiguredValue nvarchar(255) NULL
,   PackagePath nvarchar(255) NOT NULL
,   ConfiguredValueType nvarchar(20) NOT NULL
);
INSERT INTO dbo.SO_76272398
(
    ConfigurationFilter
,   ConfiguredValue
,   PackagePath
,   ConfiguredValueType
)
VALUES
(
    N'SO_76272398'
,   2
,   N'\Package.Variables[User::ConfiguredValue1]'
,   N'Int32'
)
,(
    N'SO_76272398'
,   20
,   N'\Package.Variables[User::ConfiguredValue2]'
,   N'Int32' 
);

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.

C:\ssisdata\Input\so_76266788>"C:\Program Files (x86)\Microsoft SQL Server\140\DTS\Binn\DTExec.exe" /file C:\Users\bfellows\source\repos\SSIS_2022\SSIS_2022\SO_76272398.dtsx /rep eiw

Microsoft (R) SQL Server Execute Package Utility
Version 14.0.3460.9 for 32-bit
Copyright (C) 2017 Microsoft. All rights reserved.

Started:  11:35:07 AM
Info: 2023-05-19 11:35:07.28
   Code: 0x40016040
   Source: SO_76272398
   Description: The package is attempting to configure from SQL Server using the configuration string ""config_connection";"[dbo].[SO_76272398]";"SO_76272398";".
End Info
Info: 2023-05-19 11:35:07.33
   Code: 0x40016040
   Source: SO_76272398
   Description: The package is attempting to configure from SQL Server using the configuration string ""config_connection";"[dbo].[SO_76272398]";"SO_76272398";".
End Info
Info: 2023-05-19 11:35:07.55
   Code: 0x00000000
   Source: SCR Echo Values SCR Echo Back
   Description: User::ConfiguredValue1=>1
End Info
Info: 2023-05-19 11:35:07.55
   Code: 0x00000000
   Source: SCR Echo Values SCR Echo Back
   Description: User::ConfiguredValue2=>10
End Info
Info: 2023-05-19 11:35:07.55
   Code: 0x00000000
   Source: SCR Echo Values SCR Echo Back
   Description: User::ServerName=>ERECH\DEV2017
End Info
DTExec: The package execution returned DTSER_SUCCESS (0).
Started:  11:35:07 AM
Finished: 11:35:07 AM
Elapsed:  0.547 seconds

C:\ssisdata\Input\so_76266788>"C:\Program Files (x86)\Microsoft SQL Server\140\DTS\Binn\DTExec.exe" /file C:\Users\bfellows\source\repos\SSIS_2022\SSIS_2022\SO_76272398.dtsx /rep eiw /connection "config_connection";"Data Source=.\dev2019utf8;Initial Catalog=tempdb;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;"

Microsoft (R) SQL Server Execute Package Utility
Version 14.0.3460.9 for 32-bit
Copyright (C) 2017 Microsoft. All rights reserved.

Started:  11:35:12 AM
Info: 2023-05-19 11:35:12.51
   Code: 0x40016040
   Source: SO_76272398
   Description: The package is attempting to configure from SQL Server using the configuration string ""config_connection";"[dbo].[SO_76272398]";"SO_76272398";".
End Info
Info: 2023-05-19 11:35:12.56
   Code: 0x40016040
   Source: SO_76272398
   Description: The package is attempting to configure from SQL Server using the configuration string ""config_connection";"[dbo].[SO_76272398]";"SO_76272398";".
End Info
Info: 2023-05-19 11:35:12.78
   Code: 0x00000000
   Source: SCR Echo Values SCR Echo Back
   Description: User::ConfiguredValue1=>2
End Info
Info: 2023-05-19 11:35:12.78
   Code: 0x00000000
   Source: SCR Echo Values SCR Echo Back
   Description: User::ConfiguredValue2=>20
End Info
Info: 2023-05-19 11:35:12.78
   Code: 0x00000000
   Source: SCR Echo Values SCR Echo Back
   Description: User::ServerName=>ERECH\DEV2019UTF8
End Info
DTExec: The package execution returned DTSER_SUCCESS (0).
Started:  11:35:12 AM
Finished: 11:35:12 AM
Elapsed:  0.5 seconds

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 package is attempting to configure from SQL Server using the configuration string

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.

Hi. Thanks for taking the time to help. To follow up I've created a brand new package with 3 connections. 1 config_connection which is the connection that the package configurations are based on. Then 2 additional connections (logging_ole and testing_ole) which I have set to pick up package configurations from config_connection server. I've then created the select into a variable and echo process for all 3. For me the echo after the config_connection returns the correct override.....see next comment — Bee_Riii, May 19 '23 at 12:58
the following connections which should pick up the new configuration are returning the original server. So it seems the config_connection is being replaced but the configurations are not being picked up from the overridden server. I am not using any expressions in the package. I am using VS 2019 and using the targetserverversion of 2019. Would you be willing to recreate this on your end and see if you see the same issue? Thanks — Bee_Riii, May 19 '23 at 13:01
@Bee_Riii Assuming I did what you did, it's working as I'd expect. If I misunderstood your approach, happy to rework my scenario — billinkc, May 19 '23 at 16:49
I think we're getting different results because I'm using configuration to override connection strings. I've edited my post to make it clearer what I'm doing and to give you some additional details. I can also share the xml of the package I'm using if that would be helpful. Thanks for your help so far! — Bee_Riii, May 23 '23 at 13:07

DTEXEC override of connection string is not working

1 Answers1

Updates 2023-05-19