January 31, 2012 at 12:36 pm
I've created an SSIS package that loops through all .csv files in a directory and moves the data to a SQL Server table. The package runs flawlessly when executed with the package execution tool, but not when set as a schedule job.
The package says that it ran successfully when scheduled as a job, but the data does not appear in the tables and the .csv files are still there (they're supposed to be deleted when the package finishes moving their data). Like i said, though, all of this works perfectly when I run it outside of a SQL Agent job.
I have the job set up to run as a proxy account which has appropriate access to the files on the file share as well as appropriate permissions on the tables in the destination database. Even if I set the job to run with the same permission as the person who created it (me), it does nothing as a job (still says "succeeded" though), but works correctly otherwise.
Any ideas? Thanks in advance for any suggestions.
January 31, 2012 at 1:04 pm
That is weird, my only suggestion would be to add some logging to the SSIS package so you can tell where it is getting stuck. Maybe you have it so the error doesn't make it to the stack so the job looks like it succeeded.
You can add logging within BIDS under SSIS > Logging and then checking the boxes that you want to collect info on.
January 31, 2012 at 1:42 pm
How is the package constructed? Do you use standard SSIS components, or do you also use a script task?
Your best chance right now is to add logging, as jpomfret7 suggested.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 31, 2012 at 1:58 pm
The package is all SSIS components, with one script task. The header and the data are divided from the (oddly structured) .csv's into two temp tables, and then the script task combines them into one row in the destination table (an INSERT statement). This process loops for each file in the directory.
I'll try adding logging to the package. Maybe that'll tell me what's broken.
Thanks.
January 31, 2012 at 2:40 pm
daniel.nix (1/31/2012)
The package is all SSIS components, with one script task. The header and the data are divided from the (oddly structured) .csv's into two temp tables, and then the script task combines them into one row in the destination table (an INSERT statement). This process loops for each file in the directory.I'll try adding logging to the package. Maybe that'll tell me what's broken.
Thanks.
In the script task, you need to declare the result of this task yourself. E.g.: DTS.TaskResult = Success (something like that)
Did you program the script task so that it can fail? In other words, DTS.TaskResult = Failure is present somewhere in the code.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 31, 2012 at 2:42 pm
Okay after looking at logging, it seems that the forEach file enumerator is showing up with no matches, even though there are matching files in the directory. This at least explains why the job "succeeds", but without the desired results. The enumerator is set to match filenames of '*.csv', and it does so when run outside of a job. Something about it running as a job prevents the enumerator from finding any matches. I'm stumped on this.
January 31, 2012 at 2:50 pm
Koen Verbeeck (1/31/2012)
daniel.nix (1/31/2012)
The package is all SSIS components, with one script task. The header and the data are divided from the (oddly structured) .csv's into two temp tables, and then the script task combines them into one row in the destination table (an INSERT statement). This process loops for each file in the directory.I'll try adding logging to the package. Maybe that'll tell me what's broken.
Thanks.
In the script task, you need to declare the result of this task yourself. E.g.: DTS.TaskResult = Success (something like that)
Did you program the script task so that it can fail? In other words, DTS.TaskResult = Failure is present somewhere in the code.
Sorry, I got that wrong. It's not a script task, but an Execute SQL task that performs that part.
January 31, 2012 at 2:51 pm
daniel.nix (1/31/2012)
Okay after looking at logging, it seems that the forEach file enumerator is showing up with no matches, even though there are matching files in the directory. This at least explains why the job "succeeds", but without the desired results. The enumerator is set to match filenames of '*.csv', and it does so when run outside of a job. Something about it running as a job prevents the enumerator from finding any matches. I'm stumped on this.
You're sure you deployed the latest version to the server?
The SQL Agent account has permissions to view all the files in the folder?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 31, 2012 at 3:10 pm
I figured it out. The server that the files exist on has been mapped to a network drive letter, and I used that letter to specify the path of the directory to loop within. This works fine when executing the package outside of a job, but for some reason, scheduling it as a job requires that the path be specified explicitly.
So instead of putting T:\Quality\, I needed to use \\FileServer01\Docs\Quality\ as the directory to look in for the .csv files. The package executes smoothly now. Thanks for all your help.
January 31, 2012 at 3:27 pm
I believe this is because the drive was mapped to your user which is not seen by the SQL Server Agent. Good catch though!
Jared
CE - Microsoft
January 31, 2012 at 11:03 pm
daniel.nix (1/31/2012)
I figured it out. The server that the files exist on has been mapped to a network drive letter, and I used that letter to specify the path of the directory to loop within. This works fine when executing the package outside of a job, but for some reason, scheduling it as a job requires that the path be specified explicitly.So instead of putting T:\Quality\, I needed to use \\FileServer01\Docs\Quality\ as the directory to look in for the .csv files. The package executes smoothly now. Thanks for all your help.
Aaah, mapped drives and UNC paths, a classic one 😀
Jared is probably correct in his explanation.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply