Portable SSIS Configs With a Single Script

Following from yesterday’s post, here’s how to achive SSIS configurations with a script instead of a SQL statement and a loop.

Database setup is exactly the same, and the two variables for environment and configuiration database connection string are set up the same, but the package needs just a script task.  The only property for the script which needs to be set is MaximumErrorCount which should be set to zero.

This script connects to the configuration database, selects all the variables that are valid for the current package and environment and applies them.  In the script code add another using for System.Data.SQLClient then add the following:

        public void Main()
        {
            // Create a variable dispenser and a variables collection
            VariableDispenser disp = this.Dts.VariableDispenser;
            Variables vars = default(Variables);

            String connString = null;
            String environmentName = null;
            String packageName = null;

            try
            {
                disp.LockForRead("User::strConfigDatabaseConnString");
                disp.LockForRead("User::strEnvironment");
                disp.LockForRead("System::PackageName");
                disp.GetVariables(ref vars);

                connString = vars["User::strConfigDatabaseConnString"].Value.ToString();
                environmentName = vars["User::strEnvironment"].Value.ToString();
                packageName = vars["System::PackageName"].Value.ToString();
                try
                {
                    // Connect to the configuration database
                    SqlConnection conn = new SqlConnection(connString);
                    conn.Open();

                    // Retrieve configuration values
                    String sql = String.Concat("select VarName, VarValue, VarType from ConfigVars where EnvironmentName = '",
                        @environmentName, "' and (PackageName = 'Sys' or PackageName = '", @packageName, "')");
                    SqlCommand command = new SqlCommand(sql, conn);

                    SqlDataReader reader = command.ExecuteReader();

                    // Loop through the reader
                    while (reader.Read())
                    {
                        String varName = reader["VarName"].ToString();
                        String varValue = reader["VarValue"].ToString();
                        String varType = reader["VarType"].ToString();

                        // Set the value for the variable
                        SetVariable(varName, varValue, varType);
                    }

                    // Tidy up
                    reader.Close();
                    reader.Dispose();
                    command.Dispose();
                    conn.Close();
                    conn.Dispose();

                }
                catch
                {
                    Dts.Events.FireWarning(0, "Set configuration variable values", "Unable to retrieve configuration values from database.", "", 0);
                }
            }
            catch
            {
                Dts.Events.FireWarning(0, "Set configuration variable values", "Unable to get configuration database connection string.", "", 0);
            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }

        public void SetVariable(string varName, string varValue, string varType)
        {
            VariableDispenser disp = this.Dts.VariableDispenser;

            Variables vars = default(Variables);
            try
            {
                // Lock the variable we are going to provide a value for
                disp.LockOneForWrite(varName, ref vars);

                // Convert the value to the right type for the variable and assign it
                if (varType.ToLower() == "string")
                {
                    vars[0].Value = varValue;
                }
                else if (varType.ToLower() == "int32")
                {
                    vars[0].Value = Convert.ToInt32(varValue);
                }
                else if (varType.ToLower() == "datetime")
                {
                    // Add more strings to this array depending on the format of dates you have used in the config database
                    String[] sFormats = { "yyyy-mm-dd" };
                    DateTime dt = DateTime.ParseExact(varValue, sFormats, null, System.Globalization.DateTimeStyles.None);
                    vars[0].Value = dt;
                }
                else if (varType.ToLower() == "boolean")
                {
                    vars[0].Value = Convert.ToBoolean(varValue);
                }

                Boolean bReFire = true;
                Dts.Events.FireProgress(String.Concat("Set ", varName, " value to '", varValue, "'."), 100, 0, 0, "Set configuration variable values", ref bReFire);

            }
            catch (DtsRuntimeException ex)
            {
                Dts.Events.FireWarning(0, "Set configuration variable values", ex.Message, "", 0);
            }
        }

Very simple and very flexible.

A Portable Alternative to Configurations in SSIS

How do you configure SSIS to work across various environments such as dev, test and production?  At the very least, you are going to have a different target server/database but there are possibly different sources, different directories used for RAW files, different logging systems, different operators to notify on error and any number of other changes to the system between environments,

Using Configurations is the normal way to set up your SSIS packages with the right values needed to make it behave correctly, but when you deploy your SSIS from development to test or live, you need to change those configurations.  There’s no easy mechanism for this.  Here’s a more flexible alternative which doesn’t use Integration Services’ built-in Configuration system, but lets you write your own mechanism.  It takes a bit of setting up but you only need to do it once – then you just reuse the template package.

The basic principle is that for every property which needs to be configured, you create a variable and use that variable as the property value in an expression.  For example, the connection manager for your source has an expression for ‘Connection String’.  So you create a variable named ‘@strSourceConnectionString’ and place that into the ConnectionString expression for the connection.  Then, the value for that variable is read from a database at runtime.  Crucially, you tune the SQL statement that retrieves variable values so that it retrieves the correct ones for your current environment.  This is what improves it over a standard Configuration.

The method:

Firstly, create a database table which will hold your variable values.  I would put this in the ETL management/audit database.  The columns you will need will be for the variable name, the variable value, the variable type, the environment and the package name (if you have variables with the same name but different values in each package).

These columns contain:

• The name of the variable to be configured – must be the same as the variable in SSIS.
• The value to be assigned to the variable.
• The data type of the variable in SSIS (because you are going to need to convert the string value to the right data type before it can be assigned).
• The environment for which this variable should be given this value.  You can put whatever you like in this column depending on how many environments you have and what they’re called.
• The name of the package for which this variable should be given this value.  This would need to match the name of the SSIS package exactly because it’s going to be searched for using the System::PackageName variable.  You can also use a value such as ‘SYS’ or ‘ALL’ to indicate the same value for the variable is used in all packages.

Examples might be:

You can see that strStringVariable has three entries – for the package VariableTest it has different values for Dev and Prod environments, and for the package VariableTest2 it has a different value.  The other variables apply to ‘Sys’ packages which I’m going to use to mean all packages.

With this structure in place, we can move to SSIS.  Your package will need to know which environment you are currently in and where to find all the configuration variables; for this I think the best way is to create two variables for Environment and ConfigConnectionString, and save this as an XML configuration.  When you deploy the SSIS to a different environment, just copy the .dtsconfig file to the same location (I create a new folder on the root of C:) on the new server.  Then you only ever have to change one config value in one XML file for the environment to retrieve all the right configurations.

Now create an ADO.NET connection manager and set its ConnectionString expression to the variable for the config database.

Create three string variables named strVarName, strVarValue and strVarType.  Also create the variables which you are going to add values to, remembering they need to be named exactly the same as the value in the VarName column in your config database.

The next thing to do is retrieve all the variable values from the config database and assign them to the variables in the package.  This could be done with a single script (here’s how) but here I am demonstrating grabbing the values then assigning them in a loop – it requires a bit less understanding of C# scripting in SSIS.

Add an Execute SQL Task, a ForEach Loop and a Script Task inside the loop.

The SQL task uses the config database connection, and its SQL statement is something like:

select VarName, VarValue,VarType from Configs
where EnvironmentName = @envName and (PackageName in (@pacName, ‘Sys’))

Map the @envName parameter to your Environment variable and the @pacName to the System::PackageName variable.  Set the ResultSet property to FullResultSet and place the result set into an object variable named objVariables.

Properties for the ForEach loop are:

• Enumerator: Foreach ADO Enumerator
• ADO object source vasriable: User::objVariables
• Variable Mappings: User::strVarName, User::strVarValue, User::strVarType

On the loop and the script, set the MaximumErrorCount property to zero.  This is because you will get errors if the variable you are trying to assign a value to doesn’t actually exist in the package – but you don’t want to crash the whole package because of this.

The basis of the script code you need is:

        public void Main()
        {

            // Create a variable dispenser and a variables collection
            VariableDispenser disp = this.Dts.VariableDispenser;
            Variables vars = default(Variables);
           
            string varName = null;
            string varValue = null;
            string varType = null;

            try
            {
                // Lock and assign variables
                disp.LockForRead(“User::strVarName”);
                disp.LockForRead(“User::strVarValue”);
                disp.LockForRead(“User::strVarType”);
                disp.GetVariables(ref vars);
                varName = String.Concat(“User::”, vars["User::strVarName"].Value.ToString());
                varValue = vars["User::strVarValue"].Value.ToString();
                varType = vars["User::strVarType"].Value.ToString();

                vars.Unlock();
            }
            catch
            {
                Dts.Events.FireWarning(0, “Set configuration variable values”, “Unable to lock variables.”, “”, 0);
            }

            // Call method which assigns values to SSIS variables
            SetVariable(varName, varValue, varType);

            // Finish
            Dts.TaskResult = (int)ScriptResults.Success;
        }

        public void SetVariable(string varName, string varValue, string varType)
        {
            VariableDispenser disp = this.Dts.VariableDispenser;

            Variables vars = default(Variables);
            try
            {
                // Lock the variable we are going to provide a value for
                disp.LockOneForWrite(varName, ref vars);

                // Convert the value to the right type for the variable and assign it
                if (varType.ToLower() == “string”)
                {
                    vars[0].Value = varValue;
                }
                else if (varType.ToLower() == “int32″)
                {
                    vars[0].Value = Convert.ToInt32(varValue);
                }
                else if (varType.ToLower() == “datetime”)
                {
                    // Add more strings to this array depending on the format of dates you have used in the config database
                    String[] sFormats = {“yyyy-mm-dd”};
                    DateTime dt = DateTime.ParseExact(varValue, sFormats, null, System.Globalization.DateTimeStyles.None);
                    vars[0].Value = dt;
                }

                Boolean bReFire = true;
                Dts.Events.FireProgress(String.Concat(“Set “, varName, ” value to ‘”, varValue, “‘.”), 100, 0, 0, “Set configuration variable values”, ref bReFire);

            }
            catch (DtsRuntimeException ex)
            {
                Dts.Events.FireWarning(0, “Set configuration variable values”, ex.Message, “”, 0);
            }

            // Unlock variables
            vars.Unlock();
        }
    }

Note that I have used a VariableDispenser where required, so you don’t need to add any variables to the ReadOnlyVariables or ReadWriteVariables.  This script only covers string, integer and datetime variables – if you’ve got other types you’ll need to add additional condition handlers to the if…elseif block to do the conversions required.

What happens when you run the package?  If you set the strEnvironmentName variable to ‘Dev’, you get this:

If you set the value to ‘Prod’ you get different values:

So, everything is completely portable between environments.

Let me know (Twitter: @DuncanSutcliffe) if this works for you or if it doesn’t.