1

I am working on Win2K12R2, SSDT 2016, and SQL Server 2014.

I am have a bunch of data to import and I'm trying to make several calls in serial to SSIS packages to get everything to load correctly. I'm trying to do this from PowerShell.

Here's a look at my PowerShell script:

add-type -AssemblyName "Microsoft.SqlServer.ManagedDTS, Version=11.0.0.0,      Culture=neutral, PublicKeyToken=89845dcd8080cc91"

$pkgname = '\SSISDB\Projects\Test.Data.Import\DataImport'
$server = "."

$dtsapp = New-Object ("Microsoft.SqlServer.Dts.Runtime.Application")
$exists = $dtsapp.ExistsOnDtsServer($pkgname,".");
$pkg = $dtsapp.LoadFromDtsServer($pkgname,$server,$null)

When I run 64-bit PowerShell I cannot even create the Application object. 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."

So I switch to running the PowerShell ISE (x86) version. The object now creates correctly but when I try to run this code I get the following error:

Exception calling "ExistsOnDtsServer" with "2" argument(s): "Connecting to the Integration Services service on the computer "(null)" failed with the following error: "The specified service does not exist as an installed service.".

I know this isn't true because I can see the packages on the server and have the service running. Also, I can invoke the package with dtexec.exe. I'm unsure whether I need to specifically target Sql Server 2014 or what. Any suggestions?

Corez
  • 314
  • 3
  • 18
  • Would it not be easier to just use the command line tool dtexec? – Dave Sexton Apr 04 '16 at 11:16
  • That's what I ended up doing. I used the ' /Par "$ServerOption::SYNCHRONIZED(Boolean)";True' switch to run the jobs synchronously. I would still like to figure out how to launch the job from PowerShell. – Corez Apr 04 '16 at 20:35
  • There is an answer to this question @: https://stackoverflow.com/a/75289585/1548275 – Jari Turkia Aug 02 '23 at 09:33

0 Answers0