CODEWITHSUNDEEP
c#.netsql-serverssisetl
Dan Champagne
Dan Champagne

Reputation: 920

SSIS SqlServer 2016 DTS ComponentMetaDataCollection always empty

I'm working on creating a .NET program to mass-update a bunch of SSIS packages. I have most of what I need (updating connection strings, changing version, etc.) and the last part I want to do is to be able to programmatically refresh the column metadata. However, I cannot seem to load existing data flow tasks.

var app = new Microsoft.SqlServer.Dts.Runtime.Application();
var package = app.LoadPackage(path, null);
var tasks = package.Executables.Cast<Microsoft.SqlServer.Dts.Runtime.TaskHost>().ToList();
        
foreach (var item in tasks) {
    var pipeline = item.InnerObject as Microsoft.SqlServer.Dts.Pipeline.Wrapper.Sql2016.MainPipe;

    if (pipeline == null) // skip sqltask, sendmailtask, etc
        continue;

    var metadata = pipeline.ComponentMetaDataCollection; // always an empty collection
}

The particaular package I'm loading has multiple tasks, but when I filter to the one that is a data flow task, the ComponentMetaDataCollection is always empty, but I would expect two items (an OLE DB Source and OLE DB Destination).

<DTS:Executable
  DTS:refId="Package\WORK"
  DTS:CreationName="Microsoft.Pipeline"
  DTS:Description="Data Flow Task"
  DTS:DTSID="{342AAB75-DE96-47E9-A253-B00B86A56F05}"
  DTS:ExecutableType="Microsoft.Pipeline"
  DTS:LocaleID="-1"
  DTS:ObjectName="WORK"
  DTS:TaskContact="Performs high-performance data extraction, transformation and loading;Microsoft Corporation; Microsoft SQL Server; (C) 2007 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1">
  <DTS:Variables />
  <DTS:ObjectData>
    <pipeline
      version="1">
      <components>
        <component
          refId="Package\WORK\OLE DB Destination"
          componentClassID="Microsoft.OLEDBDestination"
          contactInfo="OLE DB Destination;Microsoft Corporation; Microsoft SQL Server; (C) Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;4"
          description="OLE DB Destination"
          name="OLE DB Destination"
          usesDispositions="true"
          version="4">
... edited for brevity

I've tried dozens of methods over the last two days to try and get the existing ones so that I can refresh the metadata. I know the SqlServer.DTS library is meant more for creating packages rather than modifying existing ones, but is there a way to accomplish this? Ultimately I'm looking to call methods like:

//foreach (IDTSComponentMetaData100 component in pipeline.ComponentMetaDataCollection) {

//    var wrapper = component.Instantiate();
//    wrapper.ProvideComponentProperties();
//    wrapper.AcquireConnections(null);
//    wrapper.ReinitializeMetaData();
//    wrapper.ReleaseConnections();
//}

so I can save myself months' worth of manually updating these (there are something like 3,000 packages in total we need to upgrade). Any help would be greatly appreciated!

Upvotes: 2

Views: 548

Answers (1)

Hadi
Hadi

Reputation: 37313

I know the SqlServer.DTS library is meant more for creating packages rather than modifying existing ones

Totally wrong!! Automating SSIS packages does not mean only creation!

I think you should use Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe instead of Microsoft.SqlServer.Dts.Pipeline.Wrapper.Sql2016.MainPipe.

Try using the following code:

using DtsWrapper = Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using DtsRuntime = Microsoft.SqlServer.Dts.Runtime;

...

DtsRuntime.TaskHost TH = (DtsRuntime.TaskHost)tsk;

//Data Flow Task components
if (TH.InnerObject.ToString() == "System.__ComObject"){

    DtsWrapper.MainPipe m = (DtsWrapper.MainPipe)TH.InnerObject;
    
    DtsWrapper.IDTSComponentMetaDataCollection100 mdc = m.ComponentMetaDataCollection;
    
    foreach (DtsWrapper.IDTSComponentMetaData100 md in mdc)
    {
       //write your logic here
    }
}

You can get more information about looping over existing package components in the following answer:

Upvotes: 1

Related Questions