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

• Microsoft Visual Studio 2008
• SQL Server 2005 (or later editions)

Article
We begin by launching Microsoft Visual Studio and creating 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 the 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 create an instance of application:
Application app = new Application();
The next step is to create an SSIS package object:
Package Pkg_Template;
Declare and initialize 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.