Tuesday, August 12, 2014

Exception calling “LoadPackage” in SQLPSX - SSIS 2012

Here's a copy of a Stack Overflow / DBA Overflow request I recently made:

I'm in a 2012-only world, where the only server and client software I have loaded on my development machine is SQL 2012. I cannot load other SQL Server versioned tools, and I am stuck (!) on PowerShell 2.0, as any scripts created have to be eventually deployable on servers which are very likely to only have PowerShell 2.0.

I am using a script inspired by examples I found online. Here's the code:

Import-Module SSIS clear; $ErrorActionPreference = "Stop" $path = "C:\Users\<<Username>>\Documents\Visual Studio 2012\Projects\ConfigurationsTest\ConfigurationsTest"; $packageFileObjects = Get-ChildItem -path $path -Filter "*.dtsx"; foreach($packageFileObject in $packageFileObjects) { $packageObject = Get-ISPackage -path $packageFileObject.FullName; foreach($variable in $packageObject.Variables) { Write-Output "$($packageObject.Name); } }

When using SSIS 2012 and SQLPSX, I get the following error.

Exception calling "LoadPackage" with "2" argument(s): "The package failed to load due to error 0xC0011008 "Error loa ding from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.". This occurs when CPackage::LoadFromXML fails. " At C:\Users\<<Username>>\Documents\WindowsPowerShell\Modules\SSIS\SSIS.psm1:727 char:19 + $app.LoadPackage <<<< ($path, $null) | add-Member -memberType noteProperty -name DisplayName -value $name -passthru } + CategoryInfo : NotSpecified: (:) [], ParentContainsErrorRecordException + FullyQualifiedErrorId : DotNetMethodException

I've verified that my $env:PSModulePath contains the folders where SQLPSX is installed. I've copied the SSIS.psm1 to a different location , also found in $env:PSModulePath , to see if the service running couldn't access my user directory. I've adjusted SQLServer.psm1 with references to 2012 versions of the files. (As found here:)

 Also, I've modified the header section of ssis.psm1 to reflect the version 11.0 assembly, like this:

if ( $Args[0] -eq 2005 ) { add-type -AssemblyName "Microsoft.SqlServer.ManagedDTS, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91" #add-type -Path "C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll" } else { add-type -AssemblyName "Microsoft.SqlServer.ManagedDTS, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91" #add-type -Path "C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll" }

I've read quite a bit online that talks about how great sqlpsx is, so I'm really looking forward to getting past this bug! I cannot figure out why this fails. All the syntax looks right, so I'm hoping maybe somebody else has conquered this error already, and can share their solution or workaround.

No comments:

Post a Comment