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 (4)
  1. Sunil Kumar says:

    TaskHost T1 = (TaskHost)e1;

    doesnt work gives invalid cast exception.

  2. Abhiraam says:

    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?

  3. Anshuman Saini says:

    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

Comments are closed.

Skip to main content