Today a colleague was asking me how to access the tasks and components within a package using code. Accessing the collection of tasks in the package is fairly easy however accessing the collection of components in a data flow task isn't quite so simple. The reason is that the data flow pipeline is a COM component. Thankfully Microsoft provide a managed implementation of the COM interface called Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe in order that we can get inside the data-flow. You need to reference Microsoft.SqlServer.DTSPipelineWrap.dll in order to access it.
/*
* This code demonstrates how to iterate over a package and output
* the name of all the tasks. Additionally, if the task is a data flow
* it outputs the names of all the components in that data flow.
*
* Most of this code can be found in Books Online at http://msdn2.microsoft.com/en-us/library/ms135956.aspx
* however the code that shows how to:
* a) test if an executable is a data flow or not
* b) cast the COM pipeline interface as a managed object
* is a little more obscure!
*
* To use this code make sure you reference the following assemblies:
* Microsoft.SqlServer.DTSPipelineWrap
* Microsoft.SQLServer.DTSRuntimeWrap
* Microsoft.SQLServer.ManagedDTS
*
* -Jamie Thomson, 5th March 2007
*/
using System;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Pipeline;
namespace Conchango.Ssis
{
class Program
{
static void Main(string[] args)
{
if (args.Length == 1)
{
Console.WriteLine();
Application app = new Application();
try
{
Package p = app.LoadPackage(args[0], null);
Executables pExecs = p.Executables;
foreach (Executable pExec in pExecs)
{
TaskHost taskHost = (TaskHost)pExec;
Console.WriteLine("Executable name = " + taskHost.Name);
//Test if the task is a data flow
if (taskHost.InnerObject is Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe)
{
//Cast the Executable as a data flow
MainPipe pipe = (MainPipe)taskHost.InnerObject;
foreach (IDTSComponentMetaData90 comp in pipe.ComponentMetaDataCollection)
{
Console.WriteLine(" Component Name = " + comp.Name);
}
}
}
}
catch (Exception e)
{
Console.WriteLine(e.Message.ToString());
}
}
else
{
Console.WriteLine("Supply one and only one parameter");
}
Console.Read();
}
}
}
but the real trick here is understanding how you can access the COM interface of the pipeline.