I asked this question a couple of weeks ago:

"Pressing" the mapping tabs in the Data Flow Task without opening the solution

Now I can load the package object in PowerShell with this code:

Add-Type -Path 'C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.SqlServer.ManagedDTS\v4.0_15.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.ManagedDTS.dll';

$pkg = "C:\test.dtsx";

$app = New-Object -TypeName Microsoft.SqlServer.Dts.Runtime.Application 

$app | Get-Member

$Package = $app.LoadPackage($pkg, $null, $FALSE)

$Package | Get-Member

$Package.Executables

$DataFlowTask = $Package.Executables | Where-Object {$_.('Description') -eq ("Data Flow Task")}; 

My goal is to refresh the metadata (mappings) in the Data Flow task in both the destination and source.

I would like to call these methods on the object, which I think should do the trick (see this Link)

instance.AcquireConnections(null);
instance.ReinitializeMetaData();
instance.ReleaseConnections();

But I cannot find the Data Flow Object in the package object created above. What is the "address" on the Data Flow Task in the package object?

CodePudding user response：

I am not sure how this can be done using PowerShell, but I will give the C# solution.

//loop over control flow tasks
foreach (DtsRuntime.Executable tsk in pkg.Executables)
{
    DtsRuntime.TaskHost TH = (DtsRuntime.TaskHost)tsk
    //Getting the data flow task object
    if (TH.InnerObject.ToString() == "System.__ComObject")
    {
        try
        {
            DtsWrapper.MainPipe m = (DtsWrapper.MainPipe)TH.InnerObject;
            DtsWrapper.IDTSComponentMetaDataCollection100 mdc = m.ComponentMetaDataCollection;
            //loop over data flow task components
            foreach (DtsWrapper.IDTSComponentMetaData100 md in mdc)
            {
                //get the OLE DB Destination component                
                if(md.ComponentClassID == app.PipelineComponentInfos["OLE DB Destination"].CreationName)
                {
                 //add your code here
                }             
            }
        }
        catch
        {
        }
    }
}

This code was taken and improved from the following answer: Reverse engineering SSIS package using C#

I don't have much experience in PowerShell, but I will try to provide some helpful links:

• Get-SsisMetadata.ps1
• How to create SSIS package with PowerShell? (This link contain a method that execute a C# script from PowerShell - it may help)