Programmatically load SSIS package configurations

Question

I am making a framework in SSIS to load files from configurable folders and match them to a logical job in the database. In this job a package name is configured and in SSIS I execute this package in runtime.

I want to programmatically load a package configuration for this package, depending on the job loaded. SSIS SQL Server package configuration is not an option, because that loads values to this package just once in runtime for the package itself, but I want to load a specific package configuration in runtime that has been stored with the job (job has one package, but has many package configurations)....

Schematically: folderA -> file A.1 -> job A -> load package configuration for job A -> execute package in job A.

Is that possible?

Answer 1

We do something simliar using parent and child packages to run a standard package for differnt clients with differnt configuration values. The parent packge uses and enviroment variable and our configuration table to pull the configuration values for that particular process. The child table is configured to accept variables for the configuration which are sent from the parent package inthe execute package task. This also allows us to do some custom steps for a particular client in the parent package if need be (which is about 100% of the time here). So of you get one file form one client that they just cannot provide in the format the standard child import uses you can do transformation steps to get teh file ready for the standard import and then run the standard. Or you can add steps after the standrd package to send an email to the client with exceptions that they need to fix in their data for instance if only one client requires that.

You create Variables in the parent package for each piece of configuration information you want to send, typically to other variables or connection strings for the conmnections in the child package. You then put in an Excute package task that uses a connection to the child package.

In the child package you then go to the SSIS menu and choose package configurations and Add. Then for the type of configuration, you choose Parent Package variable. You will create one Parent package variable for each configuration item you want to send to the Child package. Things we send are things like the client_id, the connection strings to a client specific database, variables for things that might vary by client, etc.

We also store all our configurations in a table in a meta database where we store information about imports. So we set up our parent pacakge to use an environment variable to tell it which database to connect to to get the configuration information Then the second confiuration is to the SSISConfiguration table that stores the configuration information. We populate that information by server (it will vary by server generally, connection strings are different for dev, qa and prod) through an insert script that we run before testing the package.

For further detail, look in Books Online for execute package task and it wil show you how to set up the packages to pass variables.

Answer 2

I found the solution now. It is only possible by using a script task that uses the SSIS object model to create a package in runtime based on the SQL Server Application class where you can load the package by filename. After loading the package from file, I can read the configuration from file by xml or by SQL Server and add it in runtime to the child package configuration list.

Two important notes:

1) Parent variables are not passed to child package automatically. Only when an execute package task is used the parent variables are passed to the child automatically. To get this working I search the variables in runtime and write the values in it, because I know the exact variables I want to pass to each child package.

2) When using SQL Server as a package configuration for a child package, you must also create a connection manager in runtime and add it to the connection manager collection of the package. when adding the package configuration to the child package, be sure that the name of that connection manager is part of the connection string.

Here is the code to prove it works:

//load the information of the job into these variables. Package is the File system deployed package on a share. Package configuration can be the package configuration in an xml file on a share, or a connection string when using SQL Server (this one is used here).
            string package = this.Dts.Variables["Package"].Value.ToString();
            string packageConfiguration = this.Dts.Variables["PackageConfiguration"].Value.ToString();


            //create a package from package factory, by file.
            Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
            Package packageToRun = app.LoadPackage(package, null);

            //------------------------------------------ CHILD PACKAGE VARIABLES PASSING
            packageToRun.EnableConfigurations = true;

            //add one extra package configuration for child package specific configuration
            Configuration config = packageToRun.Configurations.Add();
            config.Name = "MyConfig";
            config.ConfigurationType = DTSConfigurationType.SqlServer;
            config.ConfigurationString = packageConfiguration;

            //use the name 'MyConnectionManager' in your packageConfiguration
            ConnectionManager cm = packageToRun.Connections.Add("OleDb");
            cm.Name = "MyConnectionManager";
            //TODO: retrieve this from an environvariable to allow change in data source for DEV, QA, PROD, now temporarly fixed to this value
            cm.ConnectionString = "Data Source=.;Initial Catalog=YYYYYYYYYY;Provider=SQLNCLI10.1;Integrated Security=SSPI;";

            //For Parent-Child var passing, I used the technique to let all the parent variables being defined in the child packages. 
            //Other technique could be to allow the child package not define the parent variables, but then the child packages have to reference them from code

            //------------------------------------------  PARENT VARIABLES PASSING
            //Now check if these parent variables exist in child package and write the actual values in them
            try
            {
                Variables vars = null;
                VariableDispenser variableDispenser = packageToRun.VariableDispenser;

                if (
                    packageToRun.Variables.Contains("User::XXXXXXXXXXXX") &&
                    )
                {
                    packageToRun.VariableDispenser.LockForWrite("User::XXXXXXXXXXXX");

                    variableDispenser.GetVariables(ref vars);

                    packageToRun.Variables["User::XXXXXXXXXXXX"].Value = this.Dts.Variables["User::XXXXXXXXXXXX"].Value;

                    vars.Unlock();

                    packageToRun.Execute();

                    Dts.TaskResult = (int)ScriptResults.Success;
                }
                else
                {
                    this.Dts.Events.FireError(0, string.Empty, "Child package: " + package + " has no required master variables defined or unable to unlock.", string.Empty, 0);
                }
            }
            catch (Exception ex)
            {
                this.Dts.Events.FireError(0, string.Empty, ex.Message, string.Empty, 0);

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