I am trying to execute an SSIS package from Powershell where th,e package has several configured environment references.
$null = [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices")
$null = [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.Sdk.Sfc")
$ConnectionString = "Data Source=" + $LocalSQLServer
$SSISConnection = New-Object System.Data.SqlClient.SqlConnection
$SSISConnection.ConnectionString = $ConnectionString
$SSISConnection.Credential = $LocalSQLCredential
$SSIS = New-Object Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices
$SSIS.Connection = New-Object Microsoft.SqlServer.Management.Sdk.Sfc.SqlStoreConnection $SSISConnection
$SSISCatalog = $SSIS.Catalogs["SSISDB"]
$SSISFolder = $SSISCatalog.Folders[$TargetFolderName]
$SSISProject = $SSISFolder.Projects[$ProjectName]
$SSISPackage = $SSISProject.Packages[$PackageName]
$SSISEnvironmentReference = $SSISProject.References | Where-Object {$_.Name -eq $EnvironmentName} #Get the environment reference
$SSISPackage.Execute("False", $SSISEnvironmentReference)
When I execute this script, I get an exception:
System.Management.Automation.MethodInvocationException: Exception calling "Execute" with "2" argument(s): "The type initializer for 'Microsoft.SqlServer.Management.IntegrationServices.PackageInfo' threw an exception."
---> System.TypeInitializationException: The type initializer for 'Microsoft.SqlServer.Management.IntegrationServices.PackageInfo' threw an exception.
---> System.TypeInitializationException: The type initializer for 'Microsoft.SqlServer.Diagnostics.STrace.STraceSource' threw an exception.
---> System.TypeLoadException: Could not load type 'Microsoft.SqlServer.Server.SqlContext' from assembly 'System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.
at Microsoft.SqlServer.Diagnostics.STrace.STraceSource..cctor()
--- End of inner exception stack trace ---
at Microsoft.SqlServer.Diagnostics.STrace.STraceSource.get_TraceSources()
at Microsoft.SqlServer.Diagnostics.STrace.TraceContext..ctor(String eventSourceName, String eventContext, String eventSubcontext)
at Microsoft.SqlServer.Diagnostics.STrace.TraceContext.GetTraceContext(String eventSourceName, String eventContext, String eventSubcontext)
at Microsoft.SqlServer.Diagnostics.STrace.TraceContext.GetTraceContext(String eventSourceName, String eventContext)
at Microsoft.SqlServer.Management.IntegrationServices.PackageInfo..cctor()
--- End of inner exception stack trace ---
at Microsoft.SqlServer.Management.IntegrationServices.PackageInfo.ScriptCreateExecution(Boolean use32RuntimeOn64, EnvironmentReference reference)
at Microsoft.SqlServer.Management.IntegrationServices.PackageInfo.Execute(Boolean use32RuntimeOn64, EnvironmentReference reference, Collection`1 setValueParameters, Collection`1 propetyOverrideParameters)
at CallSite.Target(Closure , CallSite , Object , String , Object )
--- End of inner exception stack trace ---
at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)
at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
Any clues on why the 2nd parameter is throwing an error? If I pass $null (as in https://learn.microsoft.com/en-us/sql/integration-services/ssis-quickstart-run-powershell?view=sql-server-ver16 and 90% of the examples I have seen) still get the same error.
If I Execute via dtexec
I have to send the Environment's RererenceId (a number) instead of the environment path or name.