Import SSIS packages with Powershell

This post does not say, but if the Directory is there it will over write correct..

I am trying to put together a test environment so I can test

OK not Connecting

Here is the error
Exception calling "CreateFolderOnDtsServer" with "3" argument(s): "Connecting to the Integration Services service on the computer "-08" failed with t
he following error: "The RPC server is unavailable.
".

This error occurs when the computer host name does not exist, or cannot be reached.
"
At \\pcna-02\mydocs\ltorres\My Documents\WindowsPowerShell\Modules\SSIS\SSIS.psm1:571 char:35
+     { $app.CreateFolderOnDtsServer <<<< ($path, $value, $serverName) }
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

Code below

import-module SSIS
new-isitem 'msdb' 'Test' $env:PCSQ-08

Select allOpen in new window

I know I can connect I just did with SSMS..
Any idea?

Do I have to run this command locally?

Seems like it is that what the $env:computername does

I did it from another machine..will that not work?

try this:
new-isitem '\msdb' 'Test' 'PCSQ-08'

error..

Path \msdb\Test already exists!
At \\pcna-02\mydocs\ltorres\My Documents\WindowsPowerShell\Modules\SSIS\SSIS.psm1:573 char:12
+     { throw <<<<  "Path $testPath already exists!" }
    + CategoryInfo          : OperationStopped: (Path \msdb\Test already exists!:String) [], RuntimeException
    + FullyQualifiedErrorId : Path \msdb\Test already exists!

Not sure I follow here  sorry..

The packages I want to import are sitting here

\\pcna-02\mydocs\ltorres\My Documents\SSIS_Packages\PCSQ-02

Where so I specify this

try:

copy-isitemfiletosql -path "\\pcna-02\mydocs\ltorres\My Documents\SSIS_Packages\PCSQ-02\*" -destination '\msdb\Test' -destinationServer 'PCSQ-08'

Select allOpen in new window

Yes, I tried

copy-isitemfiletosql -path "\\pcna-02\mydocs\ltorres\My Documents\SSIS_Packages\PCSQ-02_test\*" -destination "msdb\Test" -destinationServer 'PCSQ-08' -connectionInfo @{SSISCONFIG=".\SQLEXPRESS"}

Select allOpen in new window

got this error
copy-isitemfiletosql -path "\\pcna-02\mydocs\ltorres\My Documents\SSIS_Packages\PCSQ-02_test\*" -destination "msdb\Test" -destinationServer 'PCSQ-08' -connectionInfo @{SSISCONFIG=".\SQLEXPRESS"}

The term 'copy-isitemfiletosql' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a p
ath was included, verify that the path is correct and try again.
At line:3 char:21
+ copy-isitemfiletosql <<<<  -path "\\pcna-02\mydocs\ltorres\My Documents\SSIS_Packages\PCSQ-02_test\*" -destination "msdb\Test" -destinationServer 'PCSQ-08' -c
onnectionInfo @{SSISCONFIG=".\SQLEXPRESS"}
    + CategoryInfo          : ObjectNotFound: (copy-isitemfiletosql:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

Select allOpen in new window

add this before the command:

import-module SSIS

Select allOpen in new window

Looks like there is an error copying here

I now get this error
Exception calling "SaveToDtsServer" with "4" argument(s): "Unable to find the specified file."
At \\pcna-02\mydocs\ltorres\My Documents\WindowsPowerShell\Modules\SSIS\SSIS.psm1:773 char:31
+         { $app.SaveToDtsServer <<<< ($package, $null, $path, $serverName) }
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

Looks like the SaveToDtsServer when this command is called


{ $app.SaveToDtsServer($package, $null, $path, $serverName) }

are you working with sql 2005 or 2008?

check his script here:
http://poshcode.org/1769

I am working with 2008 the server its trying to import to is 2008

Nah Same Error


PS C:\Users\ltorres> & 'C:\Users\ltorres\Downloads\SQLPSX SSIS Demo.ps1'
Exception calling "LoadPackage" with "2" argument(s): "Failed to open package file "\\pcna-02\mydocs\ltorres\My Documents\SSIS_Packages\PCSQ-02_test\TEMPPKG" due to error
 0x80070
005 "Access is denied.".  This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either pr
oviding
an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.
"
At \\pcna-02\mydocs\ltorres\My Documents\WindowsPowerShell\Modules\SSIS\SSIS.psm1:725 char:27
+         { $app.LoadPackage <<<< ($path, $null) | add-Member -memberType noteProperty -name DisplayName -value $name -passthru }
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

Exception calling "LoadPackage" with "2" argument(s): "Failed to open package file "\\pcna-02\mydocs\ltorres\My Documents\SSIS_Packages\PCSQ-02_test\TESTING" due to error
 0x80070
005 "Access is denied.".  This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either pr
oviding
an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.
"
At \\pcna-02\mydocs\ltorres\My Documents\WindowsPowerShell\Modules\SSIS\SSIS.psm1:725 char:27
+         { $app.LoadPackage <<<< ($path, $null) | add-Member -memberType noteProperty -name DisplayName -value $name -passthru }
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

Exception calling "LoadPackage" with "2" argument(s): "Failed to open package file "\\pcna-02\mydocs\ltorres\My Documents\SSIS_Packages\PCSQ-02_test\PRODUCTION" due to er
ror 0x80
070005 "Access is denied.".  This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either
 providi
ng an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.
"
At \\pcna-02\mydocs\ltorres\My Documents\WindowsPowerShell\Modules\SSIS\SSIS.psm1:725 char:27
+         { $app.LoadPackage <<<< ($path, $null) | add-Member -memberType noteProperty -name DisplayName -value $name -passthru }
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

Exception calling "SaveToDtsServer" with "4" argument(s): "Unable to find the specified file."
At \\pcna-02\mydocs\ltorres\My Documents\WindowsPowerShell\Modules\SSIS\SSIS.psm1:773 char:31
+         { $app.SaveToDtsServer <<<< ($package, $null, $path, $serverName) }
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

Exception calling "SaveToDtsServer" with "4" argument(s): "Unable to find the specified file."
At \\pcna-02\mydocs\ltorres\My Documents\WindowsPowerShell\Modules\SSIS\SSIS.psm1:773 char:31
+         { $app.SaveToDtsServer <<<< ($package, $null, $path, $serverName) }
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

Exception calling "SaveToDtsServer" with "4" argument(s): "Unable to find the specified file."
At \\pcna-02\mydocs\ltorres\My Documents\WindowsPowerShell\Modules\SSIS\SSIS.psm1:773 char:31
+         { $app.SaveToDtsServer <<<< ($package, $null, $path, $serverName) }
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

Exception calling "SaveToDtsServer" with "4" argument(s): "Unable to find the specified file."
At \\pcna-02\mydocs\ltorres\My Documents\WindowsPowerShell\Modules\SSIS\SSIS.psm1:773 char:31
+         { $app.SaveToDtsServer <<<< ($package, $null, $path, $serverName) }
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

PS C:\Users\ltorres>

Does This error

Exception calling "SaveToDtsServer" with "4" argument(s):

Is this an issue with the 4 Arguments
or an issue with the 4th Argument

$app.SaveToDtsServer <<<< ($package, $null, $path, $serverName)

The 4th Argument is the $ServerName


This is really getting frustrating Thanks for your help!

In cased your wondering here is the code I ran. Do you see an issue?

#Edit SSIS.psm1 and Comment/Uncomment 2005 or 2008 version of SSIS assembly
#add-type -AssemblyName "Microsoft.SqlServer.ManagedDTS, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
add-type -AssemblyName "Microsoft.SqlServer.ManagedDTS, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

#Import the SSIS module
import-module SSIS

#Work with SSIS packages stored on the file system
$packages = dir "\\pcna-02\mydocs\ltorres\My Documents\SSIS_Packages\PCSQ-02_test\*" | select -ExpandProperty Fullname | foreach {get-ispackage -path $_ }
$packages | foreach {$package = $_; $_.Configurations | Select @{n='Package';e={$Package.DisplayName}}, Name,ConfigurationString}
$packages | foreach {$package = $_; $_.Connections | Select @{n='Package';e={$Package.DisplayName}}, Name,ConnectionString}

#Create a new folder on the SSIS server
#new-isitem "\msdb" "sqlpsx" "Z002"
#Copy SSIS packages from the file system to SQL Server and change the connection string for SSISCONFIG
copy-isitemfiletosql -path "\\pcna-02\mydocs\ltorres\My Documents\SSIS_Packages\PCSQ-02_test\*" -destination "msdb\Test" -destinationServer "PCSQ-08" -connectionInfo @{SSISCONFIG=".\SQLEXPRESS"}

#Work with SSIS packages on SQL Server
$packages = get-isitem -path '\sqlpsx' -topLevelFolder 'msdb' -serverName "PCSQ-08" | where {$_.Flags -eq 'Package'} | foreach {get-ispackage -path $_.literalPath -serverName $_.Servername}
$packages | foreach {$package = $_; $_.Configurations | Select @{n='Package';e={$Package.DisplayName}}, Name,ConfigurationString}
$packages | foreach {$package = $_; $_.Connections | Select @{n='Package';e={$Package.DisplayName}}, Name,ConnectionString}

Select allOpen in new window

Here is the error location if it helps

any ideas?

what is the error?

Exception calling "SaveToDtsServer" with "4" argument(s): "Unable to find the specified file."
At \\pcna-02\mydocs\ltorres\My Documents\WindowsPowerShell\Modules\SSIS\SSIS.psm1:773 char:31
+         { $app.SaveToDtsServer <<<< ($package, $null, $path, $serverName) }
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

Below is the runtime error I get for every package..
ErrorImp.JPG

Is This some sort of set up issue?

Updates?