Recently we faced a challenge in which we wanted to implement SSIS table-driven package configurations with a twist. We wanted to have a common or shared SSIS package configuration table and have the packages that make up our solution dynamically filter or utilize only specific package configuration rows from this table based on a package variable value. For example – let’s assume our package variable is the customer name with a sample value of: ‘ToscanosHardware.’ What we like to do is to have our SSIS packages look in the User::str_customer_name package variable for the customer-to-process, and then only utilize package configuration rows that apply to this specific customer. (there is a configuration filter column in the SSIS package configuration table) In other words, we’d like to implement table driven package configurations with row level filtering based on a package variable value.
Why can’t we do this with standard package configurations?
Given the current way in which table based package configurations work, we couldn’t find a way to implement row level filtering based on a package variable. Let’s assume we setup standard table based package configuration that use the following table:
CREATE TABLE [dbo].[SSIS Configurations]
( ConfigurationFilter NVARCHAR(255) NOT NULL,
ConfiguredValue NVARCHAR(255) NULL,
PackagePath NVARCHAR(255) NOT NULL,
ConfiguredValueType NVARCHAR(20) NOT NULL)
When, you create a package configuration entry in this table, you are asked to provide a ConfigurationFilter value. Here’s where we’d like very much to use an SSIS Expression and tell SSIS to filter based on what that expression evaluation to! Wouldn’t it be great to be able to provide a filter value that uses a package variable as part of an expression? This does not seem to work because what filter value we provide is actually literally interpreted.
What we’d like to do is capture in the provided screen snapshot:
<---- Here's what we tried!
Notice for the Configuration filter value we provide. We attempt to provide a SSIS expression here. However, the value we provide: User::customer_name + '_Stage_Connection' is not evaluated but is instead literally interpreted.
The Custom SSIS Solution
We ended up creating our own custom SSIS table-driven solution that was both straight-forward and easy to implmenent. Our custom SSIS solution requires several package variables along with SSIS tasks that reference a custom package configuration table that has the same column layout as the standard package configurations, but we appropriately named our table: dbo.ssis_custom_configurations. The SSIS tasks must be placed at the beginning of each package, so we placed them in the OnPreExecute Event Handler area.
In the sections that follow we will dissect each of these components in details so you can fully understand how to implement this solution.
1. Package Variables
There are several variables that are required to drive the custom solution. These are in the table below:
str_customer_name
This package variable provides the customer name we wish to processs and is actually
used to dynamically construct the ConnectString that points to the customer's Staging
Database. For example -- if we have a customer of 'ToscanosHardware' our staging
database will be ToscanosHardware_Stage.
str_configured_object_name
What is the name of the object are we going to focus on? We've pointed to both Connections
here and package variables. For example -- we have a generic Connection String we called
stage.oledb. Using our solution, this default catalog / database is changed leveraging the
str_customer_name.
str_configured_value
This package variable holds the new connection string value or packag variable value that
is obtained from the custom table.
str_config_filter
This package variable actually holds the value we are using as our row level filter. It is an
expression that combines the package name along with the customer name. Here's the
expression: @[System::PackageName]+"_"+ @[User::str_customer_cd] A sample
value could be 'extract_customer_data_ToscanosHardware'
rs_config
Hold the full result set (the rows returned from the package configuration table that whose
filter_name matches the str_config_filter package variable.
2. Required SSIS Tasks
The SSIS tasks below must be copied to the beginning of each package that you wish to participate in the custom package configurations solution. The tasks are below:
2.1 The GET CONFIG VALUES Execute SQL Task
The EXEC SQL Statement selects all rows from the custom package configuration table
using the str_config_filter in the where clause. Let’s assume we are currently working in a
package that extracts sales details data for the ‘ToscanosHardware’ customer. In this case,
our SSIS package name is ‘ext_sales_details’ so our str_config_filter is
‘ext_sales_details_ToscanosHardware’ The actual select statement used by the Execute
SQL Task is below followed by a screen snapshot of the actual task:
SELECT
configured_object_name,
property_name,
configured_value
FROM
ssis_configurations
WHERE
filter_name = ?
The select statement maps the User::str_config_filter package variable to the parameter.
Also, the User::rs_config package variable holds the full result set allowing a For Each SSIS
container to be used.
2.2 The For Each Container
The For Each Container uses the output of the Execute SQL Task which is a result set from
the custom package configuration table. The Collection Tab specifies the Users::rs_config
object source variable as seen below:
Finally, the columns returned are mapped to the following package variables as specified in
the Variable Mappings tab:
Finally, the columns returned are mapped to the following package variables as specified in
the Variable Mappings tab:
2.3 SCR - SETUP CONFIGS Script Task
The Script Task uses the package variables set in the For Each loop and actually changes the values of either the Connection String or the Package Variable Values. It is up to you to add the rows between the Select Case and the End Select. In the example below, these rows reference the connection strings and/or package variables you wish to change via this custom solution. In the example script below we are changing the ConnectionString values for both the Stage.OLEDB connection and the Stage.ADONET connection. Also, we are changing the values of the str_product_input_filename_pattern and the str_input_feed_directory package variables. These connection string rows and package variable rows must exist in the package configuration table.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
Public Sub Main()
Try
Select Case Dts.Variables("str_configured_object_name").Value.ToString
Case "Stage.OLEDB"
If Dts.Variables("str_property_name").Value.ToString = "ConnectionString" Then
Dts.Connections("Stage.OLEDB").ConnectionString =
Dts.Variables("str_configured_value").Value.ToString
End If
Case "Stage.ADONET"
If Dts.Variables("str_property_name").Value.ToString = "ConnectionString" Then
Dts.Connections("Stage.ADONET").ConnectionString =
Dts.Variables("str_configured_value").Value.ToString
End If
Case "str_product_input_filename_pattern"
If Dts.Variables("str_property_name").Value.ToString = "Value" Then
Dts.Variables("str_product_input_filename_pattern").Value =
Dts.Variables("str_configured_value").Value.ToString
End If
Case "str_input_feed_directory"
If Dts.Variables("str_property_name").Value.ToString = "Value" Then
Dts.Variables("str_input_feed_directory").Value =
Dts.Variables("str_configured_value").Value.ToString
End If
End Select
Dts.TaskResult = ScriptResults.Success
Catch ex As Exception
Debug.Print(ex.Message)
Dts.TaskResult = ScriptResults.Failure
End Try
End SubEnd Class
Conclusion
The custom SSIS solution worked like charm. We were able to actually pass the customer name as a parameter to the startup (master) package via a SQL Server Job. The master package that executed the children packages passed this customer name to the children via a Parent / Child package configuration. By doing this, we could have a generic SSIS solution that dynamically processed different customer data simply by having one job per customer.
We also embedded the required SSIS tasks in the OnPreExecute and OnPostExecute Event Handler events. This way we didn’t have to clutter up each package.
