Override SSIS configuration setting on command line?

Question

Hi everyone,

I'm trying to run an SSIS package from the SQL Server Management Studio, and am having trouble overriding a configuration setting. In my case, it's the location of a flat file. The command I'm using is:

declare @returncode INT
exec @returncode = xp_cmdshell 'dtexec 
 /SQL "\ImportData" 
 /SERVER "myserver" 
 /CONNECTION "ImportData flatfile connection";"C:\files\ballot.dat" 
 /MAXCONCURRENT " -1 "
 /CHECKPOINTING OFF /REPORTING E'

As you can see above, I'm trying to run this using c:\files\ballot.dat as the flat file in question. When doing so however, SSIS reverts to using the setting stored in its configuration file, which points to a different location (and ballot.dat file) on the hard drive.

Is there a way to override that when calling the package from the command line? Thanks for your suggestions.

Answer 1

You need to set the *full" connection string, not just the file name...

/CONNECTION "ImportData flatfile connection";"Provider=...;Data Source=C:\files\ballot.dat" 

Answer 2

What you can do is to add an SSIS Package configuration XML file. In this configuration you can specify all connection managers (just include the connection string). save this file as c:\otherconfig.xml or something like that. Edit the file, you should see your connection listed, and you can edit the connection string.

When running the package with dtexec you should be able to run it with that config file using /configuration.

Note also that there are lot of changes from 2005 to 2008 in how connections and package configurations are handled. See http://msdn.microsoft.com/en-us/library/bb500430.aspx for more details.