Reading DTS and SSIS packages programmatically

Reading a DTS/SSIS programmatically helps a lot in doing impact analysis. Consider a scenario in which all the packages containing a particular table or stored procedure need to identified. Let me give another usage scenario for this. The sql native clinet provider SQLNCLI.1 is no longer supported in SQL server 2008 and it has to be changed to SQLNCLI10.1. If you are migrating to SQL Server 2008 and the provider name has to be changed across all packages, dynamically accessing the DTS/SSIS package using .NET assemblies is the best way to achieve this. Otherwise all the packages have to be opened manually for verifying and modifying the tasks. 

Let us see how we can do this using C#. Add the following references for accessing DTS/SSIS object model.

1. Microsoft.sqlserver.Pipelinewrap (Physical location--> C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.DTSPipelineWrap.dll)

2. Microsoft.sqlserver.ManagedDTS (Physical location--> C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll)

3. Microsoft.sqlserver.ScriptTask (Physical location--> C:\Program Files\Microsoft SQL Server\90\DTS\Tasks\Microsoft.SqlServer.ScriptTask.dll)

4. Microsoft.sqlserver.VSAHosting (Physical location--> C:\Program Files\Microsoft SQL Server\90\DTS\Binn\Microsoft.SqlServer.VSAHosting.dll)

5. Select "Microsoft DTSPackage Object Library" from the COM tab in the references (Physical Location --> C:\Program Files\Microsoft SQL Server\80\Tools\Binn\DtsPkg.dll)

6. Microsoft.sqlserver.Exec80PackageTask (Physical location--> C:\Program Files\Microsoft SQL Server\90\DTS\Tasks\Microsoft.SqlServer.Exec80PackageTask.dll)

Use the following namespaces:

using

Microsoft.SqlServer.Dts.Runtime;

using

Microsoft.SqlServer.Dts.Pipeline.Wrapper;

using

Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask;

using

Microsoft.SqlServer.Dts.Tasks.ScriptTask;

Now Load and access the DTS package that is physically stored on the file system:

string

PkgLocation = "E\\Test\\abcd.dts";

string

PkgPwd = "abc123";

DTS.

PackageClass dtsPkg = new DTS.PackageClass();

object pVarPersistStgOfHost = null;

dtsPkg.LoadFromStorageFile(PkgLocation, PkgPwd,

null, null, null, ref pVarPersistStgOfHost);

MessageBox.Show(dtsPkg.Name); //Get Package Name

MessageBox.Show(dtsPkg.Tasks.Count.ToString()); //Get number of tasks present in the package

for (int i = 1; i <= dtsPkg.Tasks.Count; i++)

{

MessageBox.Show(dtsPkg.Tasks.Item(i).Name); //Get Task Name

MessageBox.Show(dtsPkg.Tasks.Item(i).Description); //Get Task Description

for (int j = 1; j <= dtsPkg.Tasks.Item(i).Properties.Count; j++)

{

MessageBox.Show(dtsPkg.Tasks.Item(i).Properties.Item(j).Name); //Get property name

if (dtsPkg.Tasks.Item(i).Properties.Item(j).Value != null)

{

MessageBox.Show(dtsPkg.Tasks.Item(i).Properties.Item(j).Value.ToString()); //Get Property Value

}

}

}

Now Load and access the SSIS package that is physically stored on the file system:

string PkgLocation = "E\\Test\\abcd.dtsx";

string

PkgPwd = "abc123";

string strNewPackage = "Newabcd";

Microsoft.SqlServer.Dts.Runtime.

Application ssisApplication;

ssisApplication =

new Microsoft.SqlServer.Dts.Runtime.Application();

Package dtsPkg;

if (PkgPwd != null)

{

dtsApp.PackagePassword = PkgPwd;

}

PkgLocation =

@"" + PkgLocation;

dtsPkg = dtsApp.LoadPackage(PkgLocation, null);

foreach (Executable e1 in exes)

{

TaskHost T1 = (TaskHost)e1;

DtsProperties dp = T1.Properties;

foreach (DtsProperty p in dp)

{

MessageBox.Show(T1.Name.ToString()); //Get Task Name

MessageBox.Show(p.Name.ToString()); //Get Task's property Name

if (p.Get == true)

{

MessageBox.Show(p.GetValue(T1).ToString()); //get Task's property value

}

}

if (T1.InnerObject.ToString() == "Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask") //This block is specific to Script Task

{

ScriptTask ST = (ScriptTask)T1.InnerObject;

ScriptTaskCodeProvider scp = ST.CodeProvider;

string strMoniker = "dts://Scripts/" + ST.VsaProjectName + "/ScriptMain.vsaitem";

scp.GetSourceCode(strMoniker);

//Let us change the provider name

scp.PutSourceCode(strMoniker, scp.GetSourceCode(strMoniker).Replace("SQLNCLI.1", "SQLNCLI10.1"));

//Save the package in the default folder (C:\Program Files\Microsoft SQL Server\90\DTS\Packages) with a new name. strNewPackage variable holds the new name

dtsApp.SaveToDtsServer(dtsPkg,

null, @"File System\" + strNewPackage, ".");

}

if (T1.InnerObject.ToString() == "System.__ComObject") //This block is specific to the inner task (Ex:- DataFlow Task will have inner tasks for source and destination)

{

MainPipe m = (MainPipe)T1.InnerObject;

IDTSComponentMetaDataCollection90 mdc = m.ComponentMetaDataCollection;

foreach (IDTSComponentMetaData90 md in mdc)

{

foreach (IDTSCustomProperty90 cprop in md.CustomPropertyCollection)

{

MessageBox.Show(T1.Name.ToString());

MessageBox.Show(md.Name.ToString()); //Get Inner Task Name

MessageBox.Show(cprop.Name.ToString()); //Get Inner Task's property Name

MessageBox.Show(cprop.Value.ToString()); //Get Inner Task's property Value

}

}

}

}

Hope you find it interesting.

Comments

• Anonymous
  June 16, 2009
  PingBack from http://topalternativedating.info/story.php?id=9960

• Anonymous
  February 10, 2011
  The comment has been removed

• Anonymous
  May 19, 2011
  I dont have access to Imports Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask namespace which is equivalent to [using Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask;]  in c#. Any solution for this?

• Anonymous
  May 09, 2013
  Hi, I want to read SSIS 2005 packaged from Dot net and from that i want to malke the column mapping sheet. Can you please guide me ? Thanks in Advanced. Regards, Anshuman Saini