SSIS Package configuation alternatives-SQL SERVER Storage

After building your packages within your development workstation, it is time to deploy them to a production test environment. In this last one, parameters could be partially or entirely different from those in the development station. I can  introduce the connection string to a given SQL Server  instance, a file location within the file system or even a variable values as concrete examples. So what are techniques to render the package flexible so that it enable us change its or its given child's properties values after the development   phase. 

I. Applications development   analogy

 For people  who have an experience developing applications, they do register application  parameters within the famous registry editor to avoid hard coding properties values those could be changed after application deployment, who doesn't know the regedit  command, saving such application parameters within the registry editor as registry keys was the main technique especially for un-managed code application like he VB 6.0/ VC ++ 6.0.

The .Net technology introduced a new way to store application parameters other than putting them in the registry editor. I mean the configuration file. This is a particular XML file within which all application parameters could be stored in a more structured and secure way comparing with the previous technique. I mean the registry key one. Although explaining in more details the divers configuration file aspects is out of the scope of this post. I can give a good reference to begin dealing with the configuration file for those who are interested to dive more in the details of that

II. SSIS packages configuration strategies


The SSIS packages also adopt similar techniques to the application ones in order to store the package parameters. I this section, we'll enumerate them one by one each for its proper context through a real use case. 

II. 1 First use case: Storing package configuration within SQL Server instance     

Consider the case where you're in  a linked servers case, in case where users are mainly using SQL Server so they are identified by their SQL Server credentials to execute SSIS pacakges  or even to store package configuration within a secure location. In deed,  this technique is the most secure way for the safety of the a package configuration parameters storage, I think. In the other hand, I consider it as the most practical at all as parameter values are stored within  a SQL server table or tables. So one can query against them or even update them using T-SQL, meanwhile safety is guaranteed, even more, you can automatize the parametrization process according to your needs not for a few number packages located in a stand alone SQL Server instance but for a hundred of packages those are located in a linked servers farm this is possible through running scheduled SQL jobs given that we have right permissions to do that, Of Corse. 
  
 Some issues have to be taken in consideration

 As far as I  used this technique, I think some details should be considered in this context. The first issue is permissions. Someone could perform some tasks in one place but this doesn't mean that the same tasks could be controlled in the same permission level by the same person or group in another place, neglecting this details could lead to package execution failure especially if we are building distributed environment solution. Second, we have to consider the fact of the connection traffic or whether a fail over politic is already adopted by the information system so that the package could get the configuration information from the SQL Server instance without rupture. 


How to do to enable SSIS SQL Server package configuration

For that, I will show this through a simple case. Let's consider this small package that contains a unique small data flow task. 

I simply added a data flow task, this last one contains an ADO NET Source task that is connected to a Multicast Task. I used a Multicast only to be able to pace a DataViewer in the middle so that we can visualize the data.


I configured the ADO NET Source task to retrieve sales data view content from AdventureWork data base sample. 

Say that, I moved the data base to another SQL Server instance or even, say that I want to retrieve data from other SQL Server source, I mean another table for example. For that I go to the SSIS menu then Package Configurations as bellow

Then I check the enable configuration, after that I click Add button

In the next step, I will choose SQL Server option as follow

In the next step, we need to indicate the data base and the table where configurations would be stored. The configuration filter should be indicated, it helps query the configuration table content later. It is possible either to choose one that already exists or to simply type a new one.  

Note: Only for  demo purposes, I stored to the configuration data within tempDB data base. But it is not recommended to do so. I can give two alternatives. If your solution deal with single data base then you can add a new configuration table to that data base. If you're in case of a distributed application that deals with multiple data bases across a linked servers, then it is possible to create a new dedicated data base for the configuration storage. I recommend that it should be created within a central SQL Server instance and that should be well designed to enable permitted people or applications query against it easily. 

 Now, it is time to decide what properties, variables should be stored within the data table. It is possible to store 

1. Package properties
2. All the control flows and data flows properties those are within the package
3. Connection managers properties
4. Package variables values

In our case, I will store the connection manager properties, the SQL command property of the ADO NET Source task in addition to the TableOrViewName property and finally the TransactionOption property. 

 The next step would be simply giving a name to the configuration 

And finally, closing the wizard 

Here are data extracted from the Adventure Works data base, and exactly from the sales.Customer view, the data viewer enables us to explore the data  

The fact that, I render the package configurable from the data table configuration table and given the properties that I selected to be stored and modified, the package is now reusable. That means I can connect to another data base and explore the data that I want. All what I need is to modify the configuration from the configuration data table 

I can change the connection string and connect to another SQL Server instance, I can query the same data base via a SQL Command, I can do query every thing that I want, as a part of my given permissions, always with the same package. I don't need to developer another package with the same structure to perform that. 

Let's play with this package a little. First we lunch the SQL Server management studio  and we try this query

USE tempdb

SELECT * FROM [SSIS Configurations]

GO

This above query will give us the following result 

So know, let's perform a couple of updates

UPDATE [SSIS Configurations]
SET ConfiguredValue = NULL
WHERE PackagePath = '\Package\Data Flow Task.Properties[[ADO NET Source].[TableOrViewName]]'

GO

DECLARE @query NVARCHAR(MAX) =N'SELECT Person.Contact.FirstName + SPACE(2) +  Person.Contact.LastName AS [Full name] FROM Person.Contact'



UPDATE [SSIS Configurations]
SET ConfiguredValue = @query
       
WHERE PackagePath = '\Package\Data Flow Task.Properties[[ADO NET Source].[SqlCommand]]'

GO

The above T SQL statements will change the query against the data base, hence, the execution behavior will change as the query is completely different. I used a SQL command to display the full name from the contact table.
Before lunching the package I changed the Access Mode of the ADO NET source task to SQL Command otherwise the validation fails. Then I lunch the execution

Same task but different content, that's it.