sql-server-2008 - SSIS API : How does one know what Interface to cast a __COMObject to?

Question

赞this post ，我也在尝试从 SSIS 包中提取 SQL。我想我会尝试发布的相同代码。听起来代码对他有用，但不完整，因为它没有处理所有可能的情况。这是调用过程的代码

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);

从帖子中，这是过程:

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;
}

我明白了

{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)).

我试过这个来获取接口(interface)列表，但是返回了一个空数组

        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;
                }
            }

我想我的问题是知道将这些 COM 对象转换到哪个接口(interface)。怎么知道？

由于缺乏打字，我也对我可能需要哪些库来完成任务感到困惑。看起来有 COM 版本和托管包装器。我希望托管包装器会给我强类型对象而不是 __COMObject，但也许不会。我刚开始玩，我不知道从哪里开始。如果有人对我可能需要引用哪些库来完成从 SSIS 包中提取 SQL 的任务提出意见，我将不胜感激。我可能还需要提取有关将哪些源文件传输到哪些目标表的一般信息。

1. Microsoft.SqlServer.DTSPipelineWrap.dll
2. Microsoft.SQLServer.DTSRuntimeWrap.dll
3. Microsoft.SQLServer.ManagedDTS.dll
4. Microsoft.SqlServer.PipelineHost.dll
5. Microsoft.SqlServer.ScriptTask.dll

Answer 1

这就是我从执行 SQL 任务中提取 SQL 的方式:

                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)
                            {

现在，如果我能弄清楚如何从数据任务中提取 sql:

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

现在呢？？