SSIS Package configuration is a great feature with SQL Server integration services and MSBI (Microsoft Business Intelligence). SSIS Package configuration is primarily used for two purposes.
- By using SSIS Package configuration feature you can read or update the property of SSIS package, tasks, components, connection managers etc. at run time. For example, you can set the connection string property of a SQL Server connection manger at run time
- Secondly, we use Configuration so that it helps to move the SSIS packages from Development, testing to production environment. The Package configuration will use the property value available in different environment (dev, sit, prod) and we do not need to change anything when we move the package or update it frequently.
In this post, I will share how you can setup the SSIS Package configuration and how you can use DTEXEC utility to run the SSIS package. I will also share how you can create a batch file which you can use to schedule a SSIS package run.
Let us take example of a simple SSIS package. In the picture below we have the design of a SSIS Package. We have a Data Flow Task. In Data Flow Task we have two components. We have a OLE DB Source and a Flat File Destination task.
We will use SSIS Package Configuration to set the Flat File connection manager property. For the demonstration purpose we will only set one properly called Connection string.
To start with we will choose from menu SSIS => Package Configurations.
This will open the Package Configuration Organizer window. We will click on the checkbox “Enable package configuration” and click on Add.
Next we will choose configuration type from the drop down list. We have five choices here - XML configuration File, Environment variable, Registry Entry, Parent package variable and SQL Server. In this space we are selecting our source where the SSIS Package will store the configuration information.
Next we will select all the properties which we want to store in our Configuration file. We have selected the “ConnectionString” of Flat File connection manager as we want to store only this property.
- SSIS: Read public data of Twitter Users?
- SSIS: How to pull Tweets from Twitter?
- SSIS: How to pull Currency Rates from European Central Bank
- SSIS: How to pull Stock Quotes from Google Finance
- SSIS: How to load data into SQL table from Google Spreadsheet?
- SSIS: How to Download images from Picasa album?
- SSIS: How to Load images into Picasa web album?
Next we will click on Next button. Here we have to provide name to our Configuration.
Our SSIS Package is ready and we are done with our SSIS Configuration setting. We have set one property “ConnectionString” of Flat File connection manager to be part of the configuration. Next we will see how we can use DTEXECUI utility to generate syntax for our Batch file which we will to execute our SSIS package. We will go to START=>RUN=> Type DTEXECUI. This will open the Execute Package Utility window.
From the Package Source select File System and from the browse button next to Package select the SSIS package we have created above.
Click on "Configuration" and click on "Add" to add the configuration file which we created above.
Next click on Command Line and you will see the command line syntax; copy the entire syntax.
Open Command Prompt window and write DTEXEC and paste the syntax code you copied from DTEXECUI window.
You can see that SSIS package is running and it completed successfully.
Next if you want to create a batch file to run this SSIS package as a window scheduler job. Go ahead and open a notepad and write the same code you have written on the Command Prompt window and save it as a .bat file. Now you can either double click the bat file to run the SSIS package or set it as a window scheduler job to run it periodically.
