Powershell can't access SQL Server 2012 Microsoft.SqlServer.DTSRuntimeWrap

2

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.

powershell
ssis
sql-server-2012
asked on Stack Overflow Apr 28, 2014 by Deddiekoel • edited Apr 28, 2014 by Deddiekoel

1 Answer

0

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
answered on Stack Overflow Apr 28, 2014 by user3565980

User contributions licensed under CC BY-SA 3.0