Using XML Package Configurations with Integration Services SSIS

By:   |   Comments (18)   |   Related: 1 | 2 | 3 | > Integration Services Configuration Options


Problem

We have adopted XML configuration files as a standard development practice within our organization.  We use them in ASP.NET web applications as well as Windows Forms applications.  How can we use XML configuration files with our SSIS packages?

Solution

SSIS has built-in support for using XML files for package configuration.  Just about any property setting in a package or task can be retrieved at runtime from an XML configuration file.  A single XML configuration file can store as many configuration settings as you need, allowing you to use the same configuration file for multiple SSIS packages.  However, every property that is specified in the XML configuration file must exist in the SSIS package else an error is raised when opened in BIDS (or Visual Studio).  A simple work around for this behavior is to use multiple XML configuration files, each containing a group of properties that exist in every package that uses the XML configuration file.

Let's walk thru an example to demonstrate the steps required to setup an XML package configuration.  Assume that we want to populate an Excel file with sales information from the AdventureWorks database.  Ultimately we would like to execute this SSIS package from a SQL Agent job which will run on a scheduled basis.  In addition we would like the ability to run the package for any date range on demand. 

For our example we will specify the following configuration settings in our XML package configuration:

  • Connection string for the AdventureWorks database
  • Connection string (i.e. the file folder and file name) for the Excel file
  • Begin date
  • End Date

Now we are ready to setup an SSIS package to use XML package configuration.  To begin, open Business Intelligence Developer Studio (BIDS) or Visual Studio and create a new Integration Services project.  Add a new SSIS package to the project then perform the following steps:

1. Add the Begin Date and End Date variables to the package; we will set these from the XML package configuration and use them as parameters in our SQL command (right click on the Control Flow and select Variables):

variables

2. Add an OLE DB Connection Manager for AdventureWorks (right click in the Connection Managers area and add a new OLE DB connection):

connection manager

3. Add an Excel Connection Manager for our output file (right click in the Connection Managers area, select New Connection then Excel):

excel connection manager

4. Add a Data Flow Task to the Control Flow (drag/drop Data Flow Task from the Toolbox onto the Control Flow):

design

5. Add an OLE DB Source and an Excel Destination to the Data Flow Task (right click the Data Flow Task then select edit; drag/drop source and destination from the Toolbox onto the Data Flow Task):

data flow

6. Configure the OLE DB Source (right click then select Edit):

connection manager

7. Set the parameters for the SQL command text (click the Parameters button in the OLE DB Source Editor):

query parameters

8. Configure the Excel Destination (right click and select Edit)

connection manager
9. Specify the Excel create table statement (click New on the Excel Destination Editor; override the LineTotal column type as shown below):
create table

10. Enable Package Configuration (right click on the Control Flow, select Package Configurations, then check Enable package configurations):

package organizer

11. Add the XML Package Configuration (click Add on the Package Configurations Organizer):

select type

12. Select the properties to include in the XML Package Configuration file (drill in to the Objects and click the check boxes for the AdventureWorks ConnectionString, Excel Connection Manager ConnectionString, v_BeginDate and v_EndDate variable's Value):

select properties

13. Complete the XML Package Configuration:

adventure works

14. Review the XML Package Configuration file created (navigate to the file name as noted in Step 13 above and open it with Internet Explorer):

configuration

Note the following in the XML package configuration file (as shown in step 14 above) :

  • Open the file using your XML editor of choice to make changes as necessary
  • The Path element refers to the object in the SSIS package
  • The ConfiguredValue element holds the value to be used in the package for the object specified in the Path element; this is the one that you need to edit

At this point the XML package configuration example is complete; you can edit the XML file as appropriate and run the SSIS package to see the results.  However, the XML file name and path is hard-coded into the SSIS package.  It would be nice to make this a variable that we can specify at runtime.  Two options come to mind:

  • Use an environment variable to specify the XML file name and path
  • Set the XML file name and path as a command line parameter at runtime

Step 11 above is where the XML file name and path was specified.  We selected the radio button "Specify configuration settings directly" then entered the full path to the file.  Instead we could specify the full path to the file in an environment variable, select the radio button "Configuration location is stored in an environment variable", and specify the environment variable.  For example:

xfl configuration

To specify the XML file name and path on the command line when executing the SSIS package, navigate to the SSIS package file using Windows Explorer and double click the file to launch the Execute Package Utility.  Select Configurations and add the configuration file as shown below then click the Execute button to run the package:

execute package utility

Clicking on Command Line will show the actual DTEXEC command line:

file

Caveats

  • Remember that any environment variable that you add while BIDS (or Visual Studio) is open will not show up in the environment variable list (e.g. Select Configuration Type dialog above) until you close the application and reopen it.
  • When you are working with an SSIS package in BIDS (or Visual Studio) , the package configuration is read when you open the package.  Any changes made to the configuration will not be reflected until you close and reopen the SSIS package.
  • When using an Excel destination the Excel file has to already exist.  In addition the default behavior is to append rows to the worksheet.  It's usually a good idea to make a backup copy of the Excel file that gets created when configuring the Excel destination (see steps 8 and 9 above).  You can then copy/rename the backup to the original Excel file name to start out with an empty worksheet before rerunning your package.
Next Steps
  • Experiment with the XML Package Configuration and see if it meets your configuration needs. 
  • Take a look at the sample package discussed in this tip here
  • Refer to the Package Configurations topic in Books on Line for additional details.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Thursday, May 9, 2013 - 5:57:07 PM - Raymond Barley Back To Top (23848)

I haven't seen a way to automatically refresh column mappings in your situation

Thursday, May 9, 2013 - 1:30:38 PM - Prasanthi Back To Top (23842)

Thanks for this post!! This was very helpful to me. In my project I had to run SSIS packages where both the source an destination DBs/tables are different everytime. The XML configuration file helps me with that where I can update the seerver name, DB name or table name. But the problem that I am not able to figure out is since the table structures on both source and destination change everytime, the column mapping is not getting refreshed . I have to do it manually.

Is there any way to refresh the column mappings as well ?

 

Friday, August 24, 2012 - 7:40:15 AM - Ray Barley Back To Top (19207)

I tried a couple variations of a simple test and cannot figure out what is going wrong.  I used SQL Server 2008 R2.

As an alternative to using XML configuration, you can put variable settings in a text file and specify the text file when you call DTEXEC; you just specify /CommandFile "yourtextfilepath" on the command line.

An example of a variable assignment: /SET "\Package.Variables[User::PlanID].Properties[Value]";"1" 

You use the package explorer in the SSIS designer to figure out what goes after the /SET

Take a look at this tip for the details: http://www.mssqltips.com/sqlservertip/1812/dtexec-command-line-parameters-using-command-files/

 

Wednesday, August 22, 2012 - 5:17:01 PM - Hanna Takla Back To Top (19178)

How can I force the Package Execution Utility to take values only from the specified configuration flie in the Configuration tab. Current the utility ignores the file I specify in the Configuration tab and use values defined in development environment.

The same applies when I try to change the configuration file in step of the SQL Job that include the package. What am I missing ?

Thanks

Friday, July 6, 2012 - 3:45:10 PM - Samay Back To Top (18376)

I somehow could not get the environment variable to work. the fix to this issue was creating a Manifest file to deploy the package. That worked perfect!

The following steps might help someone who is looking for an alternative to using environment variable:

Steps:

1. Create a Manifest file for the SSIS Project > Project > Project Properties > Configuration Manager > Deployment Utility - set CreateDeploymentUtility to True

2. this will create a manifest file.

3. Create a config file for the pkg > SSIS menu > Package configurations > specify path for config file

4. Steps to deploy: a) execute manifest file, it will open the deployment utility to copy package and config files to a file location. 

 b) it will also prompt for the config file path and also prompt to modify the properties such as connections in the configi file. This actually overwrites the config file that was specified in SSIS configurations at design time which is similar to what the environment variable attempts to accomplish.

5.  Execute the package and ensure the config file path and connections have changed as per the new environment.

 

 

Thursday, June 21, 2012 - 1:17:20 PM - Ray Barley Back To Top (18160)

Did you verify that the XML file deployed has the correct values in it?  Is the XML file in the right location?  If you get the location via an environment variable is that variable set and pointing to the right location?

 

Thursday, June 21, 2012 - 12:00:49 PM - samay Back To Top (18157)

I need to deploy my SSIS package to QA, i have created a XML configuration file to use so that QA deployer can edit the XML config to QA database settings. However, when QA deploys the package, it still puts data into the Development database so the config file does not seem to be read. Am I missing something? Please can somebody help.

Saturday, May 19, 2012 - 1:47:13 AM - Debarchan Back To Top (17546)

Thanks for the post!

Also, there is an utility now available in CodePlex which can modify SSIS Package Configuration file paths without BIDS:
http://ssisconfigeditor.codeplex.com/

Tuesday, August 11, 2009 - 6:52:51 AM - alextvino Back To Top (3862)

let me check this

thanks for your lightning replay

Tuesday, August 11, 2009 - 4:59:50 AM - raybarley Back To Top (3861)

Sorry I don't understand your question.  I typically deploy packages to SQL Server which means they wind up being stored in the msdb database, in particular in the table [msdb].[dbo].[sysdtspackages90].  You do not need any other configuration to make this work although you could.  When you use the SQL Server table for configuration, you have to make sure that when you deploy your packages (wherever you deploy them) when you run the package it can find the SQL Server database where you have the configuration table.  There is a connection manager in your package that specifies the connection string to the SQL Server database that has the configuration table.

In the tip I pointed you to in my earlier reply, I use the environment variable configuration to specify the connection string to the SQL Server database where the configuration table is.  So all I have to do to deploy is make sure the environment variable gets set properly and anything that's currently running (like SQL Server agent) gets restarted so it can see the environment variable that's been added.  This is a bit of a pain but you only have to do it once.

Tuesday, August 11, 2009 - 4:16:51 AM - alextvino Back To Top (3860)

Hi raybarley

     i have alredy done this my actual problem is i have created the package , added sql config and then i have imported the package to integration service  msdb folder.here i have to add configuration to run the package  but it accepts only xml config .tell me how to add sql config to imported package in msdb

 

 

Regards

ALEX

Tuesday, August 11, 2009 - 3:19:33 AM - raybarley Back To Top (3859)

Take a look at this tip: http://www.mssqltips.com/tip.asp?tip=1405  To add to existing packages, open the package and go through the same steps as described in the tip.   When specifying that you want a particular property value to be set from the SQL Server configuration you will get a prompt whether to reuse the existing configuration - answer yes. 


Tuesday, August 11, 2009 - 2:12:37 AM - alextvino Back To Top (3856)

Hi All

 

 I am  having my configuration in sql server SSIS Configurations table how can i add this to  SQL Server Integration Services (SSIS) Packages

Regards

Alex

Wednesday, July 1, 2009 - 3:31:45 AM - Luminai Back To Top (3673)

Seems that I now am passed the configuration issue. Selecting to create a deployment manifest, and then using that on the server, allowed me to specify where settings where supposed to be read from, as well as supplying the server settings. Next problem is that apparently we have not got the oledb engine for automating excel installed on servers, so now I have to battle the operations team to get that sorted :P.

Should anyone else be in need of a smiliar solution, this is what I did:

1. Created a xml-configuration with the values I wanted (I choose one config file for each SSIS package).
Tip: If the file does not exist, it is created by the wizard, so all you have to provide in the name of it, and path will be set to your SSIS project folder!

2. Activated the option Create deployment utility, which is in the projet property page under Deployment Utility.
Tip: do not keep this active during development, it can build and increment your manifests and when it has done so 4 or 5 times, it starts to get bugged... (information from a more experienced colleague)

3. Built the project

4. Copied manifest, dtsx files and dependencies (config and excel files in my case) to the server

5. Double-clicked the manifest to run it, followed the guide to install and configure my packages.
Tip: If you have more than one configuration file, pay attention to the drop-down box where the name of the configuration file is displayed - you need to choose each file you want to configure! (I almost misstook this for a regular text field and thought next config file would appear as I stepped to next step in the wizard).

Voilá! All done!

Tuesday, June 30, 2009 - 4:26:42 AM - raybarley Back To Top (3663)

When you execute the package with DTEXECUI select Reporting in the list box on the left, then click the Verbose checkbox.  This gives quite a bit of output when you run the package.  Maybe it will uncover something.


Monday, June 29, 2009 - 11:24:39 PM - Luminai Back To Top (3662)

I used the UI, and did specify my updated config file, but it did not use this file anyway... I just learned about the deployment utility option when building my package. Could it be that since I did not have a deployment version of the package, it ignored the settings I provided?

 

Edit:

I just looked at my first post, realised I did say 'after deployment', but by this I actually was referring to my copying the necessary files to the destination server and trying to use them there. Not deploying as in building with the deployment utility activated.

Monday, June 29, 2009 - 6:22:55 PM - raybarley Back To Top (3661)

If you execute the package using DTEXECUI, there is a Configurations page where you can specify the XML configuration file(s).  If you execute the package using DTEXEC, you can specify like this: /CONFIGFILE "C:\demo\SSIS-XMLConfig\SSIS-XMLConfig.dtsConfig".

Friday, June 26, 2009 - 5:49:20 AM - Luminai Back To Top (3647)

 Hi!

Am I to understand that the only way to specify a path for an xml configuration file for an SSIS package after deployment, is to use an environment variable? This seems silly and strange since there is acutally a page in the ExecutePackageUtility where it seems possible to specify which configuration files to use... Or am I missing something?















get free sql tips
agree to terms