0

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.