I'm trying to write a powershell script to execute a dtsx package that is already on the server. Installing the package wasn't an issue but executing is proving rather frustrating. Everything I've found online states that I need to load the ManagedDTS assembly and start with the following command to instantiate the required base object:
$dtsapp = New-Object ("Microsoft.SqlServer.Dts.Runtime.Application")
But whenever I run this line I get the following error
New-Object : Exception calling ".ctor" with "0" argument(s): "Could not load file or assembly 'Microsoft.SqlServer.DTSRuntimeWrap, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified."
At C:\Users\admin\Documents\test-ssis-dtsx.ps1:4 char:11
+ $dtsapp = New-Object ("Microsoft.SqlServer.Dts.Runtime.Application")
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [New-Object], MethodInvocationException
+ FullyQualifiedErrorId : ConstructorInvokedThrowException,Microsoft.PowerShell.Commands.NewObjectCommand
I've loaded the ManagedDTS assembly using these two ways:
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ManagedDTS")
and
Add-Type -AssemblyName "Microsoft.SqlServer.ManagedDTS, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
Both commands execute without issue but instantiating the object still fails. The DtsRuntimeWrap.dll is present on the system, just not in the GAC_MSIL folder (where the ManagedDTS.dll is). I am hesitant to start copying dlls across because I want to run this script on remote servers and I don't want to have to add these kinds of configuration steps to each server if I don't have to.
The machine I'm running this on is a fresh install of Windows 2008 R2 with SQL Server 2012 SP1. I'm using powershell 3.0.
Name Value
---- -----
PSVersion 3.0
WSManStackVersion 3.0
SerializationVersion 1.1.0.1
CLRVersion 4.0.30319.18444
BuildVersion 6.2.9200.16481
PSCompatibleVersions {1.0, 2.0, 3.0}
PSRemotingProtocolVersion 2.2
Any idea what might cause this error?
Apparently the DTSRuntimeWrap is available in the GAC_32 folder. I tried copying it to the GAC_MSIL folder and running:
Add-Type -AssemblyName "Microsoft.SqlServer.DTSRuntimeWrap, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
This resulted in the following error:
Add-Type : Could not load file or assembly 'Microsoft.SqlServer.DTSRuntimeWrap.dll' or one of its dependencies. is not a valid Win32 application. (Exception from HRESULT: 0x800700C1)
At line:1 char:1
+ Add-Type -AssemblyName "Microsoft.SqlServer.DTSRuntimeWrap, Version=11.0.0.0, Cu ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Add-Type], BadImageFormatException
+ FullyQualifiedErrorId : System.BadImageFormatException,Microsoft.PowerShell.Commands.AddTypeCommand
Also tried running these commands on a fresh Windows 2012 R2 box (with SQL Server 2012 SP1). Here I got the exact same errors, so the issue seems to be SQL Server 2012 related.
We use the following to run SSIS commands from PowerShell:
SSISCommand is set to:
for 64-bit:
"C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /f "c:\path\package.dtsx" /Reporting V
for 32-bit:
"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /f c:\path\package.dtsx" /Reporting V
And run from PowerShell with:
Command.Run SSISCommand, 2, true
User contributions licensed under CC BY-SA 3.0