Loading SSIS package and it's connection managers in a WPF application

Question

I created an integration service project with three connection managers (which are defined in project level), now I have an SSIS package (.dtsx file) and I want to use it in my app. So I use Microsoft.SqlServer.Dts namespace as below:

string pkgLocation;
Package pkg;
Microsoft.SqlServer.Dts.Runtime.Application app;
DTSExecResult pkgResults;
pkgLocation = @"D:\My Job\Tadbirgaran\Integration Services Project\MyPackage.dtsx";
app = new Microsoft.SqlServer.Dts.Runtime.Application();
pkg = app.LoadPackage(pkgLocation, null);
pkgResults = pkg.Execute();

but this code does not load project connection managers obviously, so is there any way that I can load connection manager files programmatically and add them to the package connection property? or should I define connections in my package in the integration service project?

Answer 1

You can use configurations to control the connection properties programmatically. Here is an example.

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Tasks.BulkInsertTask;

namespace configuration_API
{
    class Program
    {    
        static void Main(string[] args)
        {
        // Create a package and set two properties.
            Package pkg = new Package();
            pkg.EnableConfigurations = true;
            pkg.ExportConfigurationFile(@"C:\conf.xml");

            // Create a variable object and add it to the 
            // package Variables collection.
            Variable varPkg = pkg.Variables.Add("var", false, "", 100);
            varPkg.Value = 1;
            string packagePathToVariable = varPkg.GetPackagePath();

            // Create a configuration object and add it to the 
           // package configuration collection.
            Configuration config = pkg.Configurations.Add();

           // Set properties on the configuration object.
            config.ConfigurationString = "conf.xml";
            config.Description = "My configuration description";
            config.ConfigurationType = DTSConfigurationType.ConfigFile;
            config.PackagePath = packagePathToVariable;

            // Save the package and its configuration.
            Application app = new Application();
            app.SaveToXml(@"c:\pkg.xml", pkg, null);

            // Reload the package.
            Package p1 = app.LoadPackage(@"c:\pkg.xml", null);
            // Review the configuration information.
            Configurations configs_After = pkg.Configurations;
            foreach(Configuration confAfter in configs_After)
            {
                Console.WriteLine("ConfigurationString is {0}", confAfter.ConfigurationString);
                Console.WriteLine("ConfigurationType is {0}", confAfter.ConfigurationType);
                Console.WriteLine("CreationName is {0}", confAfter.CreationName);
                Console.WriteLine("Description is {0}", confAfter.Description);
                Console.WriteLine("Assigned ID is {0}", confAfter.ID);
                Console.WriteLine("Name is {0}", confAfter.Name);
            }
        }
    }
}

Answer 2

I ended up loading the project instead of package and setting connections from project to package as below:

        private void SSISLoadData()
        {
            Project ssisProject = null;
            DTSExecResult pkgResults;
            try
            {
                ssisProject = Project.OpenProject(@"D:\My Job\TDB\Integration Services Project\bin\Development\Integration Services Project.ispac");
                Package pkg = ssisProject.PackageItems[0].LoadPackage(null);
                for (int i = 0; i < ssisProject.ConnectionManagerItems.Count; i++)
                    pkg.Connections.Join(ssisProject.ConnectionManagerItems[i].ConnectionManager);
                pkg.Connections[0].ConnectionString = dataFolderPath + "\\*.csv";
                pkg.Connections[1].ConnectionString = string.Format("Data Source =.; Initial Catalog = TDB; Provider = SQLNCLI11.1; Integrated Security = SSPI; Initial File Name = {0};", dbPath);
                pkg.Connections[2].ConnectionString = string.Format("Data Source =.; Initial Catalog = TDBRawData; Provider = SQLNCLI11.1; Integrated Security = SSPI; Initial File Name = {0}\\TDBRawData.mdf;", Environment.CurrentDirectory);
                pkgResults = pkg.Execute();
                MessageBox.Show(pkgResults.ToString());
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                if (ssisProject != null)
                    ssisProject.Dispose();
            }
        }

I don't know if there is a better solution for this problem, but it works.