Programmatically Load and Update Connection Managers of SSIS Package Template

By | June 23, 2012

Abstract

Recently, I have been contracting on a project that involved updating existing SSIS Packate Templates. Thus, this article will demonstrate loading and updating of SSIS Package Templates using a Script Task component.

Requirements

Article

We begin by launching Microsoft Visual Studio and create a new Integration Services Project which is located under Business Intelligence Projects category.

After you have assigned a project name, proceed to click and drag the Script Task in Control Flow pane of the package’s toolbox.

Right click the script task and click on “Edit”

Under the Script Task Editor change the “ScriptLanguage” to “Microsoft Visual C# 2008”.

In Project Explorer, ensure the following references are added:
Microsoft.SQLServer.DTSRuntimeWrap;

Back to the code window, ensure that the following namespaces are declared:
using Microsoft.SqlServer.Dts.Runtime;

After the above declarations, proceed to creating an instance of application:
Application app = new Application();

The next step is to create an SSIS package object:
Package Pkg_Template;

Declare and initialise local variables:

//this variable will be used to indicate where the template package source location
int load_pkg_id = 2;

//template package is saved in the File System
string load_pkg_file_name;

//template package is saved in the SSIS Package Store
string load_pkg_dtssrv_name;
string load_pkg_dtssrv_path;

//template package is saved in SQL Server
string load_pkg_srv_name;
string load_pkg_srv_path;

//the below variables are used to define the new connection string
string conn_srv_name = “localhost”;
string conn_db_name = “AdventureWorks2008R2”;
string conn_name = “OLE_SOURCE”;

The rest of the code is as follows:

if (load_pkg_id == 1)
{
load_pkg_file_name = @”C:\Program Files\Microsoft SQL Server\100\DTS\Packages\Pkg_Template.dtsx”;

Pkg_Template = app.LoadPackage(load_pkg_file_name, null);

foreach (ConnectionManager Pkg_Connections in Pkg_Template.Connections)
{
if (Pkg_Connections.Name.ToString() == conn_name)
{
Pkg_Template.Connections[conn_name].ConnectionString = string.Format(
“Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=SSPI;”, conn_srv_name, conn_db_name);
}

app.SaveToXml(load_pkg_file_name,Pkg_Template, null);
}

}
else if (load_pkg_id == 2)
{
load_pkg_srv_name = “localhost”;
load_pkg_srv_path = @”\\Sifiso\Pkg_Template”;

Pkg_Template = app.LoadFromSqlServer(load_pkg_srv_path, load_pkg_srv_name, null, null, null);

foreach (ConnectionManager Pkg_Connections in Pkg_Template.Connections)
{
if (Pkg_Connections.Name.ToString() == conn_name)
{
Pkg_Template.Connections[conn_name].ConnectionString = string.Format(
“Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=SSPI;”, conn_srv_name, conn_db_name);
}

app.SaveToSqlServerAs(Pkg_Template, null, “Sifiso/Pkg_Template”, load_pkg_srv_name, null, null);
}

}
else if (load_pkg_id == 3)
{
load_pkg_dtssrv_name = “localhost”;
load_pkg_dtssrv_path = @”File System\Pkg_Template”;

Pkg_Template = app.LoadFromDtsServer(load_pkg_dtssrv_path, load_pkg_dtssrv_name, null);

foreach (ConnectionManager Pkg_Connections in Pkg_Template.Connections)
{
if (Pkg_Connections.Name.ToString() == conn_name)
{
Pkg_Template.Connections[conn_name].ConnectionString = string.Format(
“Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=SSPI;”, conn_srv_name, conn_db_name);
}

app.SaveToDtsServer(Pkg_Template,null,load_pkg_dtssrv_path, load_pkg_dtssrv_name);
}
}

Conclusion

It’s that simple!

You can now execute your script task and the template package will be updated accordingly.

Cheers.

Sifiso.

Leave a Reply