November 3, 2008 at 8:03 am
Hi Guys
I am new to this forum and would like some help. I hope this is the correct place to post my question.
Background Info.
I have a SSIS package that contains a "For Each File" loop. it is set up to look for *.txt. Inside the loop I process each File and then Archive that file.
Executing the package in the SSBI environment everything works perfectly. The package also executes perfectly using the command line dtexec /f "C:\XXXXX.dtsx".
The Question
For some reason though,
if I execute the package using TSQL xp_cmdShell 'dtexec /f "C:\XXXXX.dtsx"' I get a warning stating that the "For Each File enumerator is empty. For Each File enumerator did not find any files that match the file pattern, or the specified directory was empty. the overall outcome of the package however is SUCCESS.
When using xp_cmdShell the warning/error comes back within a second or so, but when I execute the package using the command line utility it seem to wait for the process to find the .txt files, about 10 or 15 seconds.
My ultimate goal is to shedule the SSIS Package but having the package in a schedled job Returns SUCCESS, but does not actually process the txt files. (I am thinking that if I manage to solve the empty Enumerator issue that the scheduler will work for me)
Any help in this will be appreciated.
Kind Regards
Chris
November 3, 2008 at 12:39 pm
When the package is being run from a job or from xp_cmdshell, it runs on the SQL Server. First, this server must be an SSIS Server (not just an MSSQL Server). Next, the path that your ForEach File enumerator must be available locally on that server in the context of the user that is executing the package. So, your SQL Agent Service account (if running a job) or the MSSQL Service account (if using xp_cmdshell).
If the path is a UNC path, the user must have access to the path. If the path is not UNC, the drive and path needs to be contextually correct (mapped drive letter can be different for different users). However, if the path is a local path on an actual drive, what you are doing should work, just make sure the path is on the server.
I would recommend you rdp to the server or log in at the terminal using the MSSQL Service account to see what it looks like for the xp_cmdshell command.
November 3, 2008 at 1:12 pm
Hi Michael
Thanks for your responce. I will give it a bash again in the morning.
July 15, 2011 at 11:26 am
I'm having some issues, did you able to resolve it? If yes, what had you done?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply