Script task runs on separate thread?
-
I can't believe what I'm seeing... but I don't have another explanation for the behavior.
=====================
Control Flow
User::nextFile=something
User::workfile=c:\xxx\zzz.txt
Foreach file (Variable Mappings, Index 0 = User::nextFile)
Execute SQL task (breakpoint on 'start')
post User::nextFile into db table
File System task (breakpoint on 'PreExecute event')
copy User::nextFile to User::workfile
Script task (breakpoint on 'PreExecute event')
open User::workfile
do some checks... length validation, etc.
Another_0 task (breakpoint on 'PreExecute event')
Another_1 task (breakpoint on 'PreExecute event')
Another _2 task (breakpoint on 'PreExecute event')
=====================
The next breakpoint after I hit 'F5 - continue' when the Debugger is at 'Script task', is 'Execute SQL task' ?!?! It is not the breakpoint at 'Another_0 task', the next task in the sequence. Why is that? Is there a way to enforce the sequence of tasks? The Package will error out because User::nextFile cannot be copied onto User::workfile. User::workfile is opened by Script task doing some checks.
Ideas welcome...
ej
-
Do you have precedence constraints in your package?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Yes... the Constraints are completely vanilla, i.e. "Success".
-
Is it possible to post an screenshot of your control flow?
You can add attachments to a reply here in these forums.
If there is anything confidential in your package that can be seen with the naked eye, just black it out or something like that 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
My mistake, a couple of Constraints are not generic. They are causing the difference in execution path.
I add an Expression which must also evaluate to true. Here you can see that User::CompanyNumber must not be empty, meaning that the file was not empty, and it had at least 1 line of data with 1 field.
The first file evaluated is empty, so the Constraint fails.
I was failing to "Close()" the file in Script task, thus the conflict with 'File copy' on iteration #2.
Also, I was being confused because the debugger honors breakpoints in the Script task on just the first execution. So, to me, it looked like it did not execute the script. When I added a file that will pass all the checks, I could see the alternate path executing.
At this point, "Execute SQL task" throws and error because it returns an empty set (select id where custName='unknownCustomer'). Other folks are having that problem also. Hard to believe that task is not prepared to deal with an empty result set. Maybe I'm just too dumb...
Anyway, thanks for your response. It really helped me focus.
Eric Johnson
Chelsea, MI USA
-
eric.johnson 33562 (3/4/2011)
At this point, "Execute SQL task" throws and error because it returns an empty set (select id where custName='unknownCustomer'). Other folks are having that problem also. Hard to believe that task is not prepared to deal with an empty result set. Maybe I'm just too dumb...You're not dumb. SSIS can be quirky from time to time.
What is the error that you get?
eric.johnson 33562 (3/4/2011)
Anyway, thanks for your response. It really helped me focus.No problem, glad to help.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
The next problem was in Execute SQL task. The result set is normally 1-row;1-column. An integerId (Identity column). But it could also be nothing, i.e. no match. My solution is first to use an Execute SQL task to do "select count(*)...". That should always return at least a 0. When it is more than 0, I know I can go ahead and do the "select xId...". Not very elegant but it works. (Coalesce, CASE, etc. cannot fix this scenario.)
One other thing I discovered was that the "Expression Constraint Failed" paths from the various tasks (Script, Execute SQL) can not share a common 'go-to' task. Like "File System task: move file to 'failedFilesDir'. When they do, the looping of "foreach file..." gets goofy, or the debugger can't follow it, or something. If they each have their own, dedicated target-task, it works fine.
But you are right... SSIS seems a bit awkward, and unintuitive. I could have written C# code to do this stuff in a couple hours. This has taken me a couple days. I hope there will be a payoff somewhere not too far into the future.
Eric Johnson
Chelsea, MI USA
-
eric.johnson 33562 (3/4/2011)
One other thing I discovered was that the "Expression Constraint Failed" paths from the various tasks (Script, Execute SQL) can not share a common 'go-to' task. Like "File System task: move file to 'failedFilesDir'. When they do, the looping of "foreach file..." gets goofy, or the debugger can't follow it, or something. If they each have their own, dedicated target-task, it works fine.I'm not exactly following. I'm more of a graphical guy 🙂
I could not see the picture in your previous post, as I do not have access to your hard drive 😉
When you reply, there is an edit attachments button, which you can use to upload images to this site.
Back to the problem. With a "common go-to task", do you mean that multiple arrows converge into one single task? If yes, you can set the behaviour of those precedence constraints by double clicking on one of the precedence constraints. At the bottom of the editor, you can choose if they behave as AND (the final task is executed if all task are succesful/completed or as OR (at least one needs to complete). OR precedence constraints are dashed arrows.
eric.johnson 33562 (3/4/2011)
But you are right... SSIS seems a bit awkward, and unintuitive. I could have written C# code to do this stuff in a couple hours. This has taken me a couple days. I hope there will be a payoff somewhere not too far into the future.Once you're past the SSIS learning curve, developing ETL should be faster than manually writting C#, as SSIS is a complete framework with automatic logging and built-in event handling. It can do a lot, you just have to figure out how 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply