Child package not working right 3

[Narizz28]

Good evening all,

I have a peculiar problem I've never encountered before. I am calling 17 packages from a parent package in order. I've made various changes over time to the second step child package. The most recent change is to take a complex SQL command and split it into 3 different steps with an On Completion workflow between them from one to the next.

The complete routine fails to update one table that's created in that second step package by the third SQL statement. After logging everything, I found that the child package only records a successful Step 1.

I am assuming that the parent package is still locked into an original version of the child package when it only had one step and did not include the new table's creation at that time. I assume that because a later child package that imports to the new table fails with an error that the table does not exist. Also, the table that gets dropped and recreated by the second child shows a create date of last month (when I was testing manually) and the others that are working properly show create dates of when the parent package ran.

Today, I deleted all old versions of the child package in question, and also deleted the step in the parent package and recreated it, but won't know until the parent runs tomorrow if that fixed it or not.

Does anyone have light they can shine on this?

 

[Narizz28]

Update:

The second child package still failed to do it's job fully. Only the first SQL step is being executed. I have the workflow set up from the first step to do an email on failure, or the second SQL step on success. The email task is set to go to the second SQL step on completion.

However, I am not getting an email that step 1 failed, but it doesn't seem to be obeying the On Success workflow either, as the logs only show SQL step 1 as being run and not SQL steps 2 and 3.

Now I'm plumb confused.

 

[JayKusch]

What manner are you calling these "child" packages?

Thanks

J. Kusch

 

[Narizz28]

From the parent, I step through the child packages via the Execute Package task. Each is followed by the next via an On Completion workflow indicator.

 

[Narizz28]

Here is a link to screen shots of the packages in question.

http://www.geocities.com/narizz28/dts.htm

 

[Narizz28]

Update:

I wanted the SQL steps seperated so that each section would email if it failed rather than the step as a whole. I reevaluated my needs and decided to just consolidate the steps into one and it is working just fine now that there is only one SQL step.

I'm still curious as to why it didn't work as I had it before.

 

[dtsScribe]

Right click the child package from within the parent. Choose Properties from Submenu. Note the "version id" on the General Tab of the window that pops up. This is the unique ID of the child package.

You have multiple versions of the child and it's pointing at the old one. You need to update it.

Update it by clicking on the "..." button, selecting the most recent child package. Note the unique ID has changed on the General tab.

Save parent package. You should be all set.

 

[Narizz28]

Yep, tried that and also deleting all old version. Problem still persisted.

I finally got around the problem by combining all SQL statements into the one step.

Thanks for your input.

 

[dtsScribe]

Others have had similar issues and have solutions; apparently some combinations of flags cause it not to work (although I havent personally found this):

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=34396

http://fuzzysoftware.com/newsgroups...p=microsoft.public.sqlserver.dts&messid=49006

 

[john76]

I don't know what your current status is with this, but I can tell from your screen shot why the 3-step wasn't working correctly.

The workflow constraints all operate on an "AND" condition, so that if a step has 2 constraints, both will need to be satisfied before the step will execute.

I can tell that the way you structured your package, what you expected to happen was:
Execute SQL Task: 1
If successful, go to Execute SQL Task 2
otherwise, go to Send Mail Task: fa... then go to Execute SQL Task 2

What happens though is:
Execute SQL Task: 1 (completes successfully)
Execute SQL Task: 2 receives "Execute SQL Task: 1 success" now waiting for "Send Mail Task: fa... complete" code, which it never receives because it will only complete if "Execute SQL Task: 1" fails, which it did not.

The same thing would happen if "SQL: 1" failed, only "SQL: 2" would be waiting for "SQL: 1" to succeed, which it never would.

So in short, "Execute SQL Task: 2" is waiting for "Execute SQL Task: 1" to succeed AND fail, which is obviously not going to happen.

It is a limitation of SQL Server 2000 DTS that can be worked around in ActiveX script tasks, (see

http://www.sqldts.com

for examples) but I would find another way to design your packages to account for the limitaion. You might, for example, replace the "On Success" condition on "SQL: 2" with an "On Completion" condition (since that seems to be your intention) and remove the "On Completion" condition linking the "Send Mail Task: fa..." to the "Execute SQL Task: 2" step. Then, no matter if "SQL: 1" succeeds or fails, "SQL: 2" will execute, but if "SQL: 1" does fail, it will notify you.

Similar logic can be applied forward through the package as well.

Hope this helps,
John

 

[MDXer]

Star to john as I just looked at the screen shot and came to the same conclusion.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[Narizz28]

That was a fantastic explaination. I now undestand what I did wrong. Thank you for clearing that one up.

 

[john76]

No problem, glad I could help.

[side note]

From what I've read about the Yukon (aka SQL Server 2005) version of DTS, it will include more advanced constraints that allow for the specification of "OR" constraints as well as the current "AND" constraints.

Cheers,
John

 

[dtsScribe]

My issue was actually a different one. I didnt look at the snapshot until after John's explanation and (correct) analysis.

Just for posterity, my issue was that the child package was erroring out but that error message wasn't being captured by the parent.

It was resolved by setting the 'fail on first error' logging property of the child package.

I was just surprised that i had to do this at all. You'd figure the parent would BY DEFAULT fail if the child threw up and abended.


b

 

[john76]

Wow, three stars on one tip! (So far... )

dtsScribe, that issue has bit me in the butt before as well.

Just glad I could help,
John