Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Simple Steps to Creating SSIS Package Configuration File

By Lanre Famuyide,

After putting so much effort into creating an Integration Services (SSIS) package to provide a structured solution to a set of daily routine tasks or a daunting business problem, the next thing that comes to mind is how to ensure that the solution you have implemented in the development environment is successfully deployed into the production environment. In this article we will be considering one of the steps to ensure successful deployment of SSIS packages: the use of a package configuration file.

Creating a package configuration file for SSIS solutions makes package deployment in the production environment less error prone, portable, and very easy to modify input parameters. In order to demonstrate this concept, I have created an SSIS package using Microsoft's Business Intelligence Development Studio to export contacts list from the contacts table in the Person schema of the Adventure Works database in SQL Server 2005. The contacts list is exported into a text file.

To make the package robust, I created package level variables as input parameters for the data source, export data dump directory, and the exported data file which are included in the configuration file. I have assumed prior knowledge of creating variables in SSIS packages and that they are used to supply values at runtime. The next section outlines one approach to create a configuration file for our export package.

Procedure:
Open the completed SSIS package and click on the Control flow tab if not already selected.

Right-click on a blank area in the control flow work area, then click on Package configurations.

Click on the Add button to start creating a configuration file.

Select a configuration type, and then specify configuration settings and a file name. In this example, we will use the XML configuration file type in order to make the configuration file settings format independent and editable using any text editor outside the BIDS development environment. Click the Next button to continue.

Check the configurable properties of objects in the package that are to be included in the configuration file. For simplicity, we will select the value property of the data source, the dump directory and the export file name variables. Click the Next button to continue.

Give the configuration settings a name, and review the settings. Click the Finish button to complete the configuration process.

Click on the Close button to close the configuration wizard.

Now that the configuration file for the package has been created, navigate to the location where the file is stored and make a copy. Open the original configuration file with any text or XML file editor and change the values of variables to values suitable for the target deployment environment. This is the configuration file that will be used to deploy the SSIS package. Below is a copy of the generated XML of the configuration file.

It is important to ensure that you create the configuration file after the SSIS package development is completed. Any changes to the application package that changes the value of any of the package variables will require editing the configuration file to reflect the update; else the package may not run successfully and may raise an error similar to this "Error: 0xC0017004. The expression was evaluated, but cannot be set on the property"

Conclusion
The importance of configuration files to SSIS packages cannot be over emphasized. Package configuration files make deployment of SSIS packages easier, more manageable, and less error prone compared to the process of deploying DTS packages where you might have to create one package for the development environment and one for the production environment.

As mentioned earlier in the article, we only created a basic configuration file. There are other settings that can be included in configuration files to fulfill your package needs. So feel free to create and explore more on configuration file settings.

Total article views: 18428 | Views in the last 30 days: 45
 
Related Articles
BLOG

Environment Variables in SSIS Packages and Configuration Tables

Configuration tables are a best practice in just about any SSIS environment. They make it easy to ...

FORUM

Package configurations

Package configurations

FORUM

SSIS & Environment Variables

Package Configurations not picking up changes to Environment Variable values

FORUM

SSIS Package Configuration

sql server package configuration

FORUM

ssis package configuration

dynamic package configuration

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones