0

For various reasons I'm trying to run an SSIS package remotely using Powershell.

I've enabled remote scripts on my server using the following command.

Set-PSSessionConfiguration -Name Microsoft.PowerShell -ShowSecurityDescriptorUI -Force

Next I'm trying to run the following script to remotely run DTExec on the server ServerName as the user domain\UserId.

$secpasswd = ConvertTo-SecureString "Password" -AsPlainText -Force
$mycreds = New-Object System.Management.Automation.PSCredential ("domain\UserId", $secpasswd)

invoke-command -ComputerName ServerName -Credential $mycreds -ScriptBlock { & `
    'C:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTEXEC.exe' `
    -FILE '\\UNC\SSIS_Package.dtsx' `
    -MAXCONCURRENT -1 -CHECKPOINTING OFF  -REPORTING EW }

I've confirmed that domain\UserId has read\execute access to \\UNC\SSIS_Package.dtsx but I am still getting the error:

Microsoft (R) SQL Server Execute Package Utility
Version 11.0.3349.0 for 64-bit
Copyright (C) Microsoft Corporation. All rights reserved.

Started:  4:40:34 PM
Error: 2015-04-22 16:40:34.24
   Code: 0xC0011007
   Source: {DAA42715-039F-4355-8121-6910272B5B07} 
   Description: Unable to load the package as XML because of package does not have a valid XML f
ormat. A specific XML parser error will be posted.
End Error
Error: 2015-04-22 16:40:34.24
   Code: 0xC0011002
   Source: {DAA42715-039F-4355-8121-6910272B5B07} 
   Description: Failed to open package file "\\UNC\SSIS_Package.dtsx"
due to error 0x80070005 "Access is denied.".  This occur
s when loading a package and the file cannot be opened or loaded correctly into the XML document
. This can be the result of specifying an incorrect file name when calling LoadPackage or the sp
ecified XML file has an incorrect format.
End Error
Could not load package "\\UNC\SSIS_Package.dtsx"
because of error 0xC0011002.
Description: Failed to open package file "\\UNC\SSIS_Package.dtsx"
due to error 0x80070005 "Access is denied.".  This occurs w
hen loading a package and the file cannot be opened or loaded correctly into the XML document. T
his can be the result of specifying an incorrect file name when calling LoadPackage or the speci
fied XML file has an incorrect format.
Source: {DAA42715-039F-4355-8121-6910272B5B07}
Started:  4:40:34 PM
Finished: 4:40:34 PM
Elapsed:  0.031 seconds

I don't know if it matters but the the package is from SQL Server 2008 and DTExec is 2012.

Edit: I've made some headway on this. It appears to be a double hop problem. I was able to fix this on one server by using CredSSP. I've run the following command on my client as an administrator.

Enable-WSManCredSSP -Role client -DelegateComputer * -Force

And this command on each of the servers I'm trying to use.

Enable-WSManCredSSP -Role server -Force

I'm using the following code to test the double hop. I'm removing the call to DTEXEC to remove that as an issue and just doing a DIR. I also have to add a CredSSP option to the invoke-command.

$secpasswd = ConvertTo-SecureString "Password" -AsPlainText -Force
$mycreds = New-Object System.Management.Automation.PSCredential ("domain\UserId", $secpasswd)

invoke-command -ComputerName ServerName -Credential $mycreds -ScriptBlock { & `
    'DIR' '\\UNC\SSIS_Package.dtsx' }
-Authentication CredSSP

Unfortunately this is only working on one of the 4 servers I've tried it on. They are all PowerShell 3. This is the error I'm getting currently.

Cannot find path '\\UNC\SSIS_Package.dtsx' because 
it does not exist.
    + CategoryInfo          : ObjectNotFound: (\\ServerName\...kage.dtsx:String) [Get-C 
   hildItem], ItemNotFoundException
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetChildItemCommand
    + PSComputerName        : CRDBLPSQLT02

Any suggestions where I can look next would be helpful.

  • I assume \\unc\ssis_package.dtsx is really \\unc\share\ssis_package.dtsx – billinkc Apr 22 '15 at 22:07
  • Yes. \\UNC is just me replacing the full path for short hand. – Kenneth Fisher Apr 22 '15 at 22:08
  • Always gotta go after the low hanging fruit on questions... What happens if you change that dtexec call to C:\Windows\System32\notepad.exe Just to ensure that yes, it's not some weird permission/pathing issue. After that, it's a toss up between "what's the package's protection level" versus "is there something weird about the package - custom/3rd party components, Script task, legacy ActiveX/DTS task involved" – billinkc Apr 22 '15 at 22:12
  • It ran without error but also without output. – Kenneth Fisher Apr 22 '15 at 22:15
  • Please confirm if the service account of Integration services also has permission to the UNC file location. Please check using services.msc and let us know. – GMaster9 May 3 '15 at 17:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.