Search code examples
sql-serverssisssis-2012

SSIS not using configuration when it is run on different server than where it was build

I have build SSIS packages on Server (suppose A) defining it's configuration in a XML. I have copied the package and the config file to a different server (suppose B) and run it using a Batch command as below:

echo "Running SSIS Load" >D:\samplessis\log\Mylog.log dtexec.exe /f "D:\samplessis\ag_data_import.dtsx" /CONFIGFILE "D:\samplessis\config\AgDataLoad.dtsConfig"  /MAXCONCURRENT " -1 "  /CHECKPOINTING OFF >>D:\samplessis\log\Mylog.log

This package seems to be using the configuration defined in package but not the xml(config file)

Solution

  • When DTEXEC is given an XML config file in MSSQL 2008 R2 or later, it configures itself in this order when run:

    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.

    (source: https://technet.microsoft.com/en-us/library/bb500430(v=sql.105).aspx)

    This means that the configuration in the package at runtime is Design - XML - Design. If you were to change the contents of the XML file and run the DTEXEC command on your dev-box, you would get the same results...

    To get around this, after setting up the configurations in the designer, clear out the initial values. For instance, if you have a connectino string as a parameter, design the Connection Manager, add the Connection String to the XML File, then in the Connection Manager's properties, clear out the connection string.

    When there are no values specified in design-time, the designer will configure itself based on the XML file, as well as applying that XML file when run with DTEXEC.