November 22, 2013 at 7:03 am
Hello all,
I have a fairly simple SSIS package that does three different database tasks, loading data from a CSV file, and writing to an error log in each data flow task. When they are all done, there are tasks to archive the CSV file and the three error logs, as such:
1. Create an archive directory named as YYYYMMDDHHmm. The directory path is a variable expression involving the CSV file path (another variable), and some manipulation of the value returned from GETDATE. This works correctly; the archive directory always gets created.
2. In parallel, copy the four files from their source location to the archive directory.
The problem I'm running into is that while the above works in BIDS, when run as a SQL Agent job only one or two (different runs of the job have different results) of the files get copied, and the job errors with "Could not find a part of the path xyz". This error would be correct if xyz were the correct value, but instead it is the value in the variable from BIDS, not from the configuration, and not the value of the directory created in step 1 (from what I can tell, the datetime component of the incorrect path is the datetime the package was deployed).
Why would one or two of the file copy steps have the correct variable value, and the other two or three be wrong?
Thanks in advance,
Tom
November 22, 2013 at 7:47 am
Which configuration do you use?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 22, 2013 at 8:11 am
Koen Verbeeck (11/22/2013)
Which configuration do you use?
If I'm interpreting your question correctly, an XML file on the server. No environment variables, no values from database.
November 22, 2013 at 1:31 pm
While I'm still curious as to why my original package fails, when I change the file copies to be sequential tasks instead of parallel, the package runs successfully to completion.
November 24, 2013 at 1:15 pm
Tom Bakerman (11/22/2013)
While I'm still curious as to why my original package fails, when I change the file copies to be sequential tasks instead of parallel, the package runs successfully to completion.
This seems weird.
It looks like the variable used in the expression is locked and other tasks can't access it.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 25, 2013 at 5:41 am
Koen Verbeeck (11/24/2013)
Tom Bakerman (11/22/2013)
While I'm still curious as to why my original package fails, when I change the file copies to be sequential tasks instead of parallel, the package runs successfully to completion.This seems weird.
It looks like the variable used in the expression is locked and other tasks can't access it.
How would something like that happen?
November 25, 2013 at 6:02 am
Tom Bakerman (11/25/2013)
Koen Verbeeck (11/24/2013)
Tom Bakerman (11/22/2013)
While I'm still curious as to why my original package fails, when I change the file copies to be sequential tasks instead of parallel, the package runs successfully to completion.This seems weird.
It looks like the variable used in the expression is locked and other tasks can't access it.
How would something like that happen?
I'm not saying it did happen, I'm saying it looks like that happens 🙂
It it is the case though, it would rather be a bug in the SSIS engine that leads to this issue when using too much parallellism.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply