You dont have to use SET. Instead you can do that using DTExec.exe /CONFIG parameter as below:
SQL Agent Job --> Steps --> General --> Type (Operating System(CmdExec)) ---> command
"Drive\Microsoft SQL Server\110\DTS\Binn\DTExec.exe" /FILE "D:\SSIS\SSIS_PackageName.dtsx" /DECRYPT "STRONG_PASSWORD" /CONFIGFILE "D:\SSIS\CONFIG_FILE_PATH.dtsConfig" /CHECKPOINTING OFF /REPORTING E
EDIT: Below is an excerpt from msdn
/Conf[igFile] filespec
(Optional). Specifies a configuration file to extract values from. Using this option, you can set a run-time configuration that differs from the configuration that was specified at design time for the package. You can store different configuration settings in an XML configuration file and then load the settings before package execution by using the /ConfigFile option.
You can use the /ConfigFile option to load additional configurations at run time that you did not specify at design time. However, you cannot use the /ConfigFile option to replace configured values that you also specified at design time. To understand how package configurations are applied, see SSIS Package Configurations and Behavior Changes to Integration Services Features in SQL Server 2008 R2.
You have asked on How to override SSIS 2008 package config file path?
What you are talking about is SET switch -- Overrides the configuration of a variable, property, container, log provider, Foreach enumerator, or connection within a package.
I have been dealing with SSIS dev's and I use /CONFIG when deploying it to PROD .. provided the paths are same in the package.
EDIT: 2
Agree with OP that the behaviour has changed in 2008 and up :
In SQL Server 2008 Integration Services, events occur in the following order:
- The utility first applies the design-time configurations.
- The utility then applies the run-time options that you specified on the command line when you started the utility.
- Finally, the utility reloads and reapplies the design-time configurations.
In above scenario, you can do as below:
option 1:
DECLARE @ssis_cmd VARCHAR(4000)
DECLARE @Packagepath VARCHAR(50)
DECLARE @FileName VARCHAR(50)
SET @Packagepath = 'D:\SSIS\SSIS_PackageName.dtsx' -- SSIS package location
SET @FileName = 'D:\SSIS\CONFIG_FILE_PATH.dtsConfig' --Passing dynamic variable i.e 'file location' into ssis package.
SELECT @ssis_cmd = 'dtexec /F "' + @Packagepath + '"'
SELECT @ssis_cmd =
@ssis_cmd + ' /SET \Package.Configurations[Config].Properties[ConfigurationString];"' + @FileName + '"'
EXEC master..xp_cmdshell @ssis_cmd
Option 2:
As described here, you can create table SSIS_Configurations and then load them during runtime. Good example is given here.
Found a connect item here
HTH
I could not resolve this issue and changed my approach to utilize a SQL Job instead of running the package directly from a powershell script.
Summary of solution:
- SQL Job with one Job Step
- The Job Step executes the SSIS package
To run a SQL Job with a custom you must also create:
- an instance-level Credential
- a Proxy Account which should be mapped to the Credential
I still wonder what's different about this approach versus the 'interactive' approach listed above. Both approaches run with the exact same identity and privileges!
For an in-depth description of the steps required to configure a SQL Job, look at this example code for Automated Deployment of a SQL Job and Job Step.
Hope this helps someone else in the same situation -
Best Answer
Try the combination of LoadFromSqlServer and SaveToDtsServer together
If I'm understanding you correctly, you want to load you package from a file to SQL Server. Then, do the reverse, to write package to a file from SQL Server. I think the magic might be in the following code snippet here:
Here's some code I haven't tested that's a combination of the following two articles shown immediately after. Here the code, loads UsingExecuteProcess.dtsx TO SQL Server. Then, the code uses LoadFromSQLServer to get the package back into the variable pkgIn FROM SQL Server. Finally, SaveToDtsServer writes pkgIn back to the file system (again no compilations yet so there may be syntax error or two to work through--I essentially combined the code from two articles listed).
Note:
If your local computer is really over the network, you could use a UNC (\MyComputer\MyShare\MyPackage.dtsx) naming convention to save the file to your system from SQL Server over the network--instead of using a local drive letter that's only available on SQL Server.
https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.dts.runtime.application.loadfromsqlserver?redirectedfrom=MSDN&view=sqlserver-2017#Microsoft_SqlServer_Dts_Runtime_Application_LoadFromSqlServer_System_String_System_String_System_String_System_String_Microsoft_SqlServer_Dts_Runtime_IDTSEvents_
https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.dts.runtime.application.savetodtsserver?view=sqlserver-2017