1

I have an SSIS 2008 package with a single configuration named Config. I would like to use the dtexec /SET switch to change the path of the config file that it uses so our developers that build the package can have it point to their local path, but when we deploy it to our production servers we can specify a new path for dtexec to load the config from so it can contain prod settings.

According to MSDN we should be able to do this using the /SET command as it mentions here:

Using the /Set option to change the location from which the utility loads design-time configurations Behavior in SQL Server Integration Services 2008

Succeeds.

After the utility applies the run-time options, the utility reloads the design-time configurations from the new location.

I've tried: 

dtexec /FILE "PackagePath" /SET  "\Package.Configurations[Config].Properties[ConfigurationString]";"ConfigFilePath"

and it fails with:

The package path referenced an object that cannot be found: "\Package.Configurations[Config].Properties[ConfigurationString]". This occurs when an attempt is made to resolve a package path to an object that cannot be found.

I've also pasted a redacted version of my .dtsx file here in case anyone thinks I'm just mistyping something. And here is the contents of config that I'm trying to change it to.

I realize there are other ways of doing indirect configs using environment variables, etc... but that is not what I'm interested in so please do not offer up those suggestions. I'm specifically trying to use the /SET command since Microsoft has explicitly stated that we should be able to. Due to the way our environment is setup this method would be the best for us.

Improve this question
7
  • More supporting evidence from here: You can use the /Connection or /Set option at run time to load package configurations from a location other than the location that you specified at design time.
    – Levi W
    Apr 22 '13 at 19:38
  • SSIS package configurations are a bit messy. You will find some clarification here, in this previous answer. Please make sure that you don't miss the comments, too.
    – Marian
    Apr 22 '13 at 20:00
  • @Marian agreed they are. This very reason "if the original config file does exist, then the value is taken from the config file and even the /SET is ignored (this is used only in the case above);" is exactly why I need to use /SET to change the config file location, so it doesn't go looking for the original dev path. Microsoft says you're supposed to be able to "Use the /Set option at run time to load package configurations from a location other than the location you specified at design time" I just need to figure out how to do that, nobody seems to know!
    – Levi W
    Apr 22 '13 at 20:12
  • Well you'll find there the solution: just rename/remove the original config file, and all the other options will just be taken into consideration.
    – Marian
    Apr 22 '13 at 20:23
  • That's not very clean though, it will generate warnings about the missing config file.
    – Levi W
    Apr 22 '13 at 20:38
2

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:

  1. The utility first applies the design-time configurations.
  2. The utility then applies the run-time options that you specified on the command line when you started the utility.
  3. 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

Improve this answer
5
  • /CONFIG will not override the attributes that were set by the config file that was specified at design-time. The idea behind my deployment model would be that the developers would build their package and create a config file with test values in it, most likely that file will be on a test file share. When their package is released it will be saved to the SSIS server and the job that calls it will /SET the config file to point to a different file that contains prod settings.
    – Levi W
    Apr 22 '13 at 19:32
  • Yes, using /CONF you can specify a run-time configuration that differs from the configuration specified at design time, however that wording is confusing. It should say "a run-time attribute", because the word configuration is ambiguous. I promise you, if you have a config file that you created at design time that changes an attribute of something, you can not change it again with a file specified by /CONF. The values in the design-time config will override it. That's why you have to use /SET to change the config file path instead. It's all in the documentation I posted.
    – Levi W
    Apr 22 '13 at 19:57
  • OK... Reading up more on that ... seems that the behavior has changed in 2008. As Design time configuration will prevail/override run time configuration and that's why you have to use /SET. I have updated my answer to reflect the same.
    – Kin Shah
    Apr 22 '13 at 20:08
  • the problem is, that syntax /SET \Package.Configurations[Config].Properties[ConfigurationString];"' + @FileName + '"' doesn't work. Give it a try and you'll see. None of the /SET options in the connect link you posted work. The people in that thread are trying to find the same thing I am, nobody seems to know the syntax for /SET to change the config location.
    – Levi W
    Apr 22 '13 at 20:40
  • 2
    This is weird, as in my experience and opinion, this is contrary to normal behavior where specifying something in a command line should override the default specified. I will probably try and replicate this.
    – Kin Shah
    Apr 22 '13 at 20:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.