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.