1

curly one for SSIS / Windows / Powershell people.

I have an issue where I have two Windows 2012 servers and I'm trying to remotely execute an SSIS package. Server "D3" is the SQL server database with SSIS installed, and I am able to execute my package successfully in the SSIS GUI, and also via the DTExec utility using the following Powershell script (normally would be cmd, but must be powershell due to using the invoke-command further down):

The Powershell script ExecCommandImportSEMA.ps1 looks like this:

$command = @'
cmd.exe /C 'DTEXEC.exe /File "E:\Contact Management File Archive\SSIS Project\Integration Services Project1\Integration Services Project1\SEMA_Response_File_Load.dtsx" /De "(password here)"'
'@
Invoke-Expression -Command:$command

This is OK when I execute this command from the D3 database server.

However, I am actually needing to execute this call from D1 (other Windows 2012 server). I use the call Invoke-Command in order to get the DTExec to run from the other server. I do this using this bat script (must be a bat for upstream compatibility).

powershell invoke-command -ComputerName D3 -FilePath 'E:\ETLJobs\ExecCommandImportSEMA.ps1'
PAUSE

This fails with the error below in the ForEach loop component (first process to look for the files), which picks up files from the same D3 server on which is lives (and yes, the files DO exist there):

Warning: 2016-11-09 17:28:39.06
   Code: 0x8001C004
   Source: Foreach Loop Container Loop through each file
   Description: The For Each File enumerator is empty. The For Each File enumer
ator did not find any files that matched the file pattern, or the specified dir
ectory was empty.
End Warning

To Recap:

  • WORKS: execute powershell script locally on D3 to Invoke-Expression, which then executes DTExec utility on D3.
  • FAILS: execute bat script on D1 which uses Invoke-Command to execute the same powershell script (now must be on D1) to Invoke-Expression, which then executes DTExec utility on D3.

Complex. Basically I can run the package fine locally, but using the bat script and Invoke-Command then causes the SSIS package error to come up on the D1 cmd prompt.

From comments

I tried a local drive folder "E:\" on D3 with and executed it from D1 and it actually WORKED (also works when executing locally on D3) - I copied the files there manually and looks to have removed some of the strange users out of the Security settings. Pointing to permissions on the network share drive folder or files, although both my user and the general Users groups both have the same permissions on the share drive files as on the local files. It is strange though that I can execute the SSIS package when I'm on D3 just fine to the same files on the network drive, just not from D1.

Recap: CAN execute the job on D3 from D1 when the files are on the local drive "E:\". CAN'T execute the job on D3 from D1 when the fi

billinkc
  • 59,250
  • 9
  • 102
  • 159
marcus
  • 11
  • 2
  • To restate: Run package on D3 and it picks up files fine. Start package on D3 remotely from D1 and it can't find the files/path. Typically, this is a permissions or pathing. What account ran it successfully on D3? Is that the same account as D1? Where are the source files located - is it a local or remote folder. If remote, are you using mapped drives or UNC? – billinkc Nov 09 '16 at 16:18
  • Correct - package runs fine via GUI and Powershell script on D3, but not on D1. Both executing using my own account. I did try running the D1 bat script as administrator to kick off the package too, no avail. Was using mapped network drives to store the source files, but also tried a local drive folder "E:\" on D3 with no luck. – marcus Nov 09 '16 at 22:32
  • I tried a local drive folder "E:\" on D3 with and executed it from D1 and it actually WORKED (also works when executing locally on D3) - I copied the files there manually and looks to have removed some of the strange users out of the Security settings. Pointing to permissions on the network share drive folder or files, although both my user and the general Users groups both have the same permissions on the share drive files as on the local files. It is strange though that I can execute the SSIS package when I'm on D3 just fine to the same files on the network drive, just not from D1. – marcus Nov 10 '16 at 02:54
  • Recap: CAN execute the job on D3 from D1 when the files are on the local drive "E:\". CAN'T execute the job on D3 from D1 when the files are on the network drive. CAN execute the job on D3 FROM D3 when the files are on the network drive. – marcus Nov 10 '16 at 03:20
  • Ohhh, that smells like a [double hop issue](https://blogs.technet.microsoft.com/askds/2008/06/13/understanding-kerberos-double-hop/) Here's a [PS article](https://blogs.technet.microsoft.com/ashleymcglone/2016/08/30/powershell-remoting-kerberos-double-hop-solved-securely/) – billinkc Nov 10 '16 at 04:00
  • Yes - that would make sense, great stuff! Will check back when I can test out the invoke-command using some Kerberos credentials. – marcus Nov 11 '16 at 05:34

0 Answers0