How can i use dtsExec command in stored procedure to execute ssis package by passing entire configuration from a table?

Question

I am using given command line to execute ssis packages in sp in sql server.

SET @dtsExecCmd = @DTSEXECCMDPATH + ltrim(rtrim(@ssisPkgFilePath)) + '" /CONFIGFILE "' + ltrim(rtrim(@ssisCommonConfigPath)) + '"'

EXEC @result = master..xp_cmdshell @dtsExecCmd  

where,

@DTSEXECCMDPATH = 'G:\"Program Files (x86)"\"Microsoft SQL Server"\110\DTS\Binn\dtexec /F "'


@ssisPkgFilePath = '\\dtsx package path\package.dtsx'

@ssisCommonConfigPath =
'\\CommonConfigurationpath\Configuration.dtsConfig'

Now i want to execute the same package by passing the configuration(present in Configuration.dtsConfig earlier) from table, how do i have to edit this command line if @config contains configuration from table.

Answer 1

I'm assuming you are not going to upgrade to project deployment for these package and stick with package deployment.

There is no DTEXEC command line switch that lets you define a SQL Server table to load a config from.

The easiest way to use package configuration from a SQL Server table is to open the package up in SSDT, go the SSIS/Package Configurations and set it up. Then you don't need to pass anything on the DTEXEC command line at all.

https://learn.microsoft.com/en-us/sql/integration-services/package-configurations?view=sql-server-2014#sql-server

If you don't want to make a package change and you insist on passing it through DTEXEC then I suppose you could write a wrapper that pulls the config data out of a table and one by one applies those configs using the /SET switch.

But I really encourage and recommend you to upgrade these packages to the project deployment model. Don't waste your time with this old method. You're just introducing technical debt.

You should also know that xp_cmdshell is generally considered to be a security issue.