Introduction
If you are working on SQL Server Integration Service (SSIS) packages, it is highly unlikely that you are working in the production environment directly. Alternatively, you might be working in the development environment and later deploying to the production environment. Apart from these environments, you normally have testing and staging environments as well. For all these cases you probably have different servers and folders to work with.
Apart from different environments, sometimes, there are changes made by the client. Sometimes the client may change the drive from C to D, or change the database name or configuration. In case of SMTP mail servers, you may have to change the server IP and authentication when the environment changes.
So when the environment changes, you may have to change all the configuration settings associated with SSIS packages. You can imagine the headache when you have to change all the packages. Believe me, it is not an easy task and what if you miss one? You know the consequences.
To illustrate this let me start with simple example. Let us say we want to populate SQL Server Database from a text file.
Text file may be in D:Textfile folder and SQL Server may have SQL Server authentication. In this case I will write a simple dtsx to import data. Then we need to transfer this to the production environment. (Let us forget about test and staging environments for simplicity). Production environment may not have D drive to keep files, instead it may have an E drive and SQL Server may be set to windows authentication. So we have to make those changes again. You can imagine the work load and the associated risks when there are 1000+ packages.
Package Configuration in SQL Server 2005
In SQL Server 2005, you have an option called Package Configurations under the SSIS menu option to allow database developers to store package configurations outside the SSIS packages.
I will run through this feature by an example.
Below is the format of the table which will store the data imported from the text file.
CREATE TABLE [dbo].[SSServices](
[ID] [int] IDENTITY (1,1) ,
[Name] [varchar](50) NULL,
[ServiceCode] [varchar](50) NULL
) ON [PRIMARY]
Next, create a simple SSIS to load the text file data into the created table. Creating the SSIS will not be discussed as it will deviate from the main topic. The SSIS package looks like following.
After creating the SSIS Package, the next step is to save the package configurations outside the package. First we have to include SQL Server credentials and the database name and then we have to include the text file path for text file.
If you click the Package Configurations option, first you have to enable the package configurations option which will be in the next screen you will get after the Welcome screen. In that dialog, click the Add button and you will be taken to the following screen.
As you can see there are few configuration types available with package configurations. They are – XML configuration file, Environment variable, Registry entry, Parent package variable and SQL Server. We will consider each option so that you can select perfect option for your requirements.
After selecting a relevant option, the next step is to select necessary properties that you want save as package configurations.
For this example, I will select ‘XML Configuration file’ and in that case you need to provide the XML file location and file name.
As we then need to select database settings, we have to select LocalHost.SSIS_Package_Config connection manager which is the connection manager for the SQL Server database. Then you will need to select the properties you require to save as a package configuration from the following screen.
In this case, you can either select entire ConnectionString option or you can select ServerName, UserName, InitialCatalog to construct the connection string. The latter is the best choice as it will give you more options when there is a change.
For text file configurations, you can select the Load Txt file and in that you can select the ConnectionString and click next. Following screen will appear.
That is all you have to do for this example. The next time you load the package, your package will read the configurations from an XML file. You can verify this by changing the XML file contents and reloading the package. After reloading the package, view the connection manager properties and you can see that the connection manager properties are updated from the values in the XML files are updated for the properties.
Even though we have completed the example let us move further ahead and observe the other options available with Package Configurations.
From the above image, you can see that almost all the properties of the object that exist in your package can be saved in package configurations. However, most of the time package configurations are used to save connection settings and variables as those are the most used configurations.
Just for the comparison, the following image shows what you get for SQL Server connection property window from Visual Studio and SSIS package configurations window.
From the above image you can see that DataSourceID, DelayValidation, Expression are the missing properties in package configurations.