Translating C# to PowerShell

Question

I am trying to translate this code from C# to PowerShell

//Creating a new package
Application app = new Application();
Package p = new Package();

//Adding the connection manager
ConnectionManager DatabaseConnectionManager = p.Connections.Add("OLEDB");
DatabaseConnectionManager.ConnectionString = @"Data Source=.;Initial Catalog=tempdb;Integrated Security=SSPI;Provider=SQLNCLI11;Auto Translate=false;"; ;
DatabaseConnectionManager.Name = "ConnectionOLEDB";
DatabaseConnectionManager.Description = "SSIS Connection Manager for OLEDB";
    
//Adding the data flow task
Executable e = p.Executables.Add("STOCK:PipelineTask");
TaskHost thMainPipe = (TaskHost)e;
MainPipe dataFlowTask = (MainPipe)thMainPipe.InnerObject;
thMainPipe.Name = "Import Person";
    
IDTSComponentMetaData100 component = dataFlowTask.ComponentMetaDataCollection.New();
component.Name = "OLEDBSource";
component.ComponentClassID = app.PipelineComponentInfos["OLE DB Source"].CreationName;

Code this from this webpage under the section "Adding Data Flow Tasks components": https://www.sqlshack.com/biml-alternatives-building-ssis-packages-programmatically-using-manageddts/

The assemblies are:

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

See Reverse engineering SSIS package using C#

My problems start at the line with TaskHost thMainPipe = (TaskHost)e; and I get the errors like "Cannot convert ...value of type ...".

Here is the PowerShell code that is working:

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

$App = New-Object -TypeName Microsoft.SqlServer.Dts.Runtime.Application; 
$Package = New-Object -TypeName Microsoft.SqlServer.Dts.Runtime.Package;
$PackageFullPath = 'C:\test.dtsx';
# Empty package created. 

$ConnectionString = "Data Source=SomeInstance;Initial Catalog=SomeDB;Integrated Security=SSPI";
 
$DatabaseConnectionManager = $Package.Connections.Add("OLEDB");
$DatabaseConnectionManager.ConnectionString = $ConnectionString;
$DatabaseConnectionManager.Name = "SomeDB";
$DatabaseConnectionManager.Description = "Hello";

# Add a Data Flow Task
$e = $Package.Executables.Add("STOCK:PipelineTask");

  # $App.SaveToXml($PackageFullPath, $Package,$null);

Why am I getting a cast error for TaskHost thMainPipe = (TaskHost)e;?

Where I get the error is:

# Add a Data Flow Task
$e = $Package.Executables.Add("STOCK:PipelineTask"); # This works
# $e | Get-Member
$thMainPipe = [Microsoft.SqlServer.Dts.Runtime.TaskHost]$e # no error here
# This line triggers the error:
$dataFlowTask = [Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe]$thMainPipe.InnerObject 

The error message is:

Cannot convert the "System.__ComObject" value of type "System.__ComObject#{b3350f87-4de7-4cb4-a273-d980c9e0b8ad}" to type "Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe".
At line:1 char:1
+ $dataFlowTask = [Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe]$t ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : ConvertToFinalInvalidCastException

Also the type of $e is Microsoft.SqlServer.Dts.Runtime.TaskHost. So not sure why I need $thMainPipeat all.

Answer 1

^{See also: This follow-up question.}

That your cast's operand is a COM object (as evidenced by System.__ComObject being reported as the object type in the error message) may be the source of the problem, because I don't think PowerShell can cast COM objects to other types.

However, given that PowerShell can dynamically discover members on objects, in many cases where C# requires casts, PowerShell doesn't (and casts to interfaces are no-ops in PowerShell, except when guiding method overload resolution). Similarly, there's no (strict) need to type variables.^[1]

Thus, as you've confirmed, simply omitting the cast of $thMainPipe.InnerObject to type [Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe] worked:

$dataFlowTask = $thMainPipe.InnerObject # No cast.
$component = $dataFlowTask.ComponentMetaDataCollection.New()

---

^{[1] Typing a variable in PowerShell means to type-constrain it, which means that you're still free to assign a value of any type, as long as PowerShell can automatically convert it to the target type (and PowerShell's automatic type conversions are very flexible); e.g., [int] $num = '42' works fine. Without a type constraint, the type of the value being assigned, which may be of any type, effectively determines its type, but you're free to assign an object of a different type later; e.g. $var = '42' makes $var contain a [string], whereas $var = 42 makes it an [int].}