SSIS API: How does one know what Interface to cast

Like this post, I am also trying to extract SQL from an SSIS package. I thought I would try the same code that was posted. It sounded like the code worked for him but was incomplete because it did not handle all of the possible scenarios. Here's the code to call the proc

var taskHost = (Microsoft.SqlServer.Dts.Runtime.TaskHost)_Package.Executables[0];
var innerObject = taskHost.InnerObject;

List<TaskHost> listOfTaskHosts = new List<TaskHost>();
listOfTaskHosts.Add(taskHost);

string sql = ExtractQueriesFromTasks(listOfTaskHosts);

From the post, here's the proc:

public static string ExtractQueriesFromTasks(List<TaskHost> Tasks)
{
    string src_query = "";
    foreach (TaskHost executable in Tasks)
    {
        DtsContainer Seq_container = (DtsContainer)executable;

        if (executable.InnerObject is TaskHost)
        {
            TaskHost th = (TaskHost)executable.InnerObject;
            string n = th.InnerObject.GetType().Name;
        }


        if (executable.InnerObject.GetType().Name == "__ComObject")
        {
                Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSPipeline100 sqlTask1 = (Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSPipeline100)executable.InnerObject;
        }
    }
    return src_query;
}

I get this

{System.InvalidCastException: Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSPipeline100'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{89CEBA86-EC51-4C62-A2D3-E9AA4FC28900}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

I tried this to get a list of interfaces, but an empty array was returned

        if (executable.InnerObject.GetType().Name == "__ComObject")
        {
            Type[] types = (executable.InnerObject.GetType()).GetInterfaces();

            foreach (Type currentType in types)
            {
                if (typeof(Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSPipeline100).IsAssignableFrom(executable.InnerObject.GetType()))
                {
                    Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSPipeline100 sqlTask1 = (Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSPipeline100)executable.InnerObject;
                }
            }

I guess my issue is knowing what Interface to cast these COM objects to. How does one know?

With this lack of typing, I am also confused about which libraries I may need for the task. It looks like there are COM versions as well as managed wrappers. My hope was that the managed wrappers would give me strongly typed objects instead of __COMObject, but maybe not. I just starting playing around and I'm not sure where to begin. If someone had comments about which libraries I may need to reference for the task of pulling out SQLs from SSIS packages, I'd appreciate it. I may also need to extract general info as to what source files are transferred to which dest tables.

Microsoft.SqlServer.DTSPipelineWrap.dll
Microsoft.SQLServer.DTSRuntimeWrap.dll
Microsoft.SQLServer.ManagedDTS.dll
Microsoft.SqlServer.PipelineHost.dll
Microsoft.SqlServer.ScriptTask.dll

回答1:

This is how I pulled the SQL from an Execute SQl task:

                foreach (Executable executable in _Package.Executables)
                {
                    TaskHost taskHost = executable as TaskHost;
                    if (taskHost != null)
                    {
                        string taskHostName = taskHost.Name;
                        System.Diagnostics.Debug.WriteLine("SSIS Task=" + taskHostName);

                        IDTSExecuteSQL iDTSExecuteSQL;

                        try
                        {
                            iDTSExecuteSQL = (IDTSExecuteSQL)taskHost.InnerObject as IDTSExecuteSQL;

                            if (iDTSExecuteSQL != null)
                            {

Now if I can just figure out how to extract sqls from within a Data Task:

 MainPipe pipeline = taskHost.InnerObject as MainPipe;
                                    if (pipeline != null)
                                    {
                                        foreach (IDTSComponentMetaData100 componentMetadata in pipeline.ComponentMetaDataCollection)
                                        {
                                            try
                                            {???

Now what??

回答2:

Guessing you don't have a path to the DTS objects.

Try this. Double check that you have system path varibles: c:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\;c:\Program Files\Microsoft SQL Server\100\Tools\Binn\;c:\Program Files\Microsoft SQL Server\100\DTS\Binn\;C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\;C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\

"100" in the path is sql server 2008. "110" is sql server 2012. They need to appear prior to the "110" versions in your path.

回答3:

This can be done with custom log events - see this answer to a similar question:

custom log events for ssis