1

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?

Hadi
  • 36,233
  • 13
  • 65
  • 124
xhr489
  • 1,957
  • 13
  • 39

1 Answers1

1

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:

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • Hi, I still cannot find the ReinitializeMetaData() method? I have ComponentMetaDataCollection for the Data Flow Task, but there is no such method for the objects after that... Any hints? – xhr489 Mar 09 '22 at 18:28
  • 1
    You should loop over the data flow task component and check if the component class is an OLE DB destination as I mention above. The method should be called where i placed the comment : `add your code here` – Hadi Mar 09 '22 at 18:31
  • Hi again, I cannot find Microsoft.SqlServer.DTSRuntimeWrap.dll. Is it nessasary for DtsWrapper.MainPipe? It is not in GAC nor in the SQL Server folder in Program files (SQL Server 2019). – xhr489 Mar 11 '22 at 20:31
  • 1
    @xhr489 make sure that the Integrations Services shared feature is installed with SQL Server 2019. – Hadi Mar 11 '22 at 20:59
  • Hi, `Microsoft.SqlServer.RuntimeWrapper.dll` is not to be found anywhere on any computer. I have even tried `Get-ChildItem "C:\Program Files\Microsoft SQL Server\" -Filter *RuntimeWrapper.dll -Recurse -Force` and no luck. See also this question [link](https://dba.stackexchange.com/questions/308877/finding-a-sql-server-assembly-where-is-100-folder-in-the-installation-folder) where I have asked the question on DBA website. – xhr489 Mar 18 '22 at 12:01
  • 1
    Try searching for `DTSRuntimeWrap.dll` – Hadi Mar 19 '22 at 18:33
  • Hi I have found it, but the 64 bit only covers until sql server 2017. I think I need a 64 bit for sql server 2019 for the PowerShell to work. – xhr489 Mar 19 '22 at 18:58