SSIS API: how do I know which interface to use to create __COMObject?

Question

Like this post , I am also trying to extract SQL from the SSIS package. I thought I would try the same code that was published. It sounded like the code worked for him, but was incomplete because it could not cope with all possible scenarios. Here is the code to call 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 is 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 it

 {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 assume that my problem is that I know which interface these COM objects should pass. How to find out?

With the 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. I hope managed wrappers give me strongly typed objects instead of __COMObject, but maybe not. I'm just starting to play, and I'm not sure where to start. If someone had comments about which libraries I might need to reference the task of pulling SQL packages from SSIS packages, I would appreciate it. I may also need to extract general information about which source files will be transferred to dest tables.

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

Answer 1

Here's how I pulled 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 a data task:

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

Now what ??

Answer 2

Assuming you have no way to DTS objects.

Try it. Double check if you have a system path: 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 should appear before versions of "110" in your path.

Answer 3

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

custom log events for ssis