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.