我正在嘗試將此代碼從 C# 轉換為 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;
從此網頁在“添加數據流任務組件”部分下對此進行編碼:https ://www.sqlshack.com/biml-alternatives-building-ssis-packages-programmatically-using-manageddts/
組件是:
using System;
using DtsRuntime = Microsoft.SqlServer.Dts.Runtime;
using DtsWrapper = Microsoft.SqlServer.Dts.Pipeline.Wrapper;
我的問題從第一行開始,TaskHost thMainPipe = (TaskHost)e;
我收到類似“無法轉換......類型的值......”之類的錯誤。
這是正在運行的 PowerShell 代碼:
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);
為什麼我會收到演員表錯誤TaskHost thMainPipe = (TaskHost)e;
?
我得到錯誤的地方是:
# 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
錯誤信息是:
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
的類型$e
也是Microsoft.SqlServer.Dts.Runtime.TaskHost
. 所以不知道為什麼我需要$thMainPipe
。
另請參閱:此後續問題。
您的轉換操作數是COM 對象(如在錯誤消息中報告為對像類型所證明的System.__ComObject
那樣)可能是問題的根源,因為我不認為 PowerShell 可以將 COM 對象轉換為其他類型。
但是,鑑於 PowerShell 可以動態發現對像上的成員,在 C# 需要強制轉換的許多情況下,PowerShell 不需要(並且強制轉換到接口在 PowerShell 中是無操作的,除非指導方法重載解決方案)。同樣,沒有(嚴格)需要輸入 variables。[1]
因此,正如您已經確認的那樣,只需省略$thMainPipe.InnerObject
to type的演員表即可[Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe]
:
$dataFlowTask = $thMainPipe.InnerObject # No cast.
$component = $dataFlowTask.ComponentMetaDataCollection.New()
[1] 在 PowerShell 中鍵入變量意味著對它進行類型約束,這意味著您仍然可以自由分配任何類型的值,只要 PowerShell 可以自動將其轉換為目標類型(並且 PowerShell 的自動類型轉換是非常靈活);例如,[int] $num = '42'
工作正常。如果沒有類型約束,所分配的值的類型(可能是任何類型)有效地決定了它的類型,但您可以在以後隨意分配不同類型的對象;例如$var = '42'
,使$var
包含 a [string]
,而$var = 42
使其成為[int]
.
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句