| Author |
Topic |
|
rushdib
Yak Posting Veteran
93 Posts |
 Posted - 2004-04-21 : 09:20:55
|
| Hi,On the designer, I have a DTS Package 1 inside DTS Package 2 along with couple of Execute SQL Task. So the work flow inside Package 2 is like this DTS Package1 -> SQL Task1 -> SQL Task 2I have made the work flow to move on only "on success". But it doesn't happen that way. The package completes even if DTS Package 1 fails. What seems to be am I doing wrong here?Thank you,Rushdi |
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2004-04-21 : 09:56:33
|
| Does dts package 1 have to run inside dts package 2?If not then you can run the packages from a sql server agent job.Here you can set the job to quit reporting failure when dts package 1 fails.Duane. |
 |
|
|
rushdib
Yak Posting Veteran
93 Posts |
Posted - 2004-04-21 : 10:32:32
|
| I want the Package 1 to be completed successfully to run the other SQL Tasks inside Package 2. I have also modified the work flow to run a different task "on failure" option of Package 1, but some how it seems to always follow the "on success" path. Rushdi |
|
|
|
weasel
Starting Member
8 Posts |
Posted - 2004-04-26 : 19:53:00
|
| I am having the exact same problem. Someone on a sun-belt mailing list suggested that I set the "Fail package on first error" option on for the sub-package, but that didn't help either.I have posted a screen shot to help illustrate the oddness of this:http://www.rsvpusa.com/rsvp/temp/dtserror.jpgI found this on Google, but it appears that the site is down at the moment, so I don't know if there is a solution posted there.[dBforums - DTS - On Failure send mail does not work]http://dbforums.com/t973293.html |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-04-26 : 19:56:12
|
| Inside of the step test for error and RAISERROR if one occurs. This will cause a failure to be "recognized". Remember just because something "doesn't happen" doesn't mean that SQL Server sees it as an error. Let us know if this works.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
weasel
Starting Member
8 Posts |
Posted - 2004-04-26 : 19:59:47
|
| I'm a bit new to DTS, can you give a simple example of how to do that? I have a ActiveX Script Task in eash of the sub-packages already that I can easily add this.Did you look at the screenshot? The "Execute package" results window seems to see it as an Error, or is that different in some way? |
|
|
|
weasel
Starting Member
8 Posts |
Posted - 2004-04-26 : 20:38:32
|
| Everything I can find refers to setting the "fail on first error" at the package level or setting the "Fail package on step error" in the workflow. Neither, of these nor a combination of them is working for me... |
|
|
|
weasel
Starting Member
8 Posts |
Posted - 2004-04-26 : 21:23:39
|
| I have tried using an Execute SQL Task in the sub-package to RAISEERROR(1,1,2) but this doesn't help either. Apparently its the ActiveX Script Task (in the sub-package) that is not reporting the error properly. I can excute the step manually and run package from the IDE, and I get a message saying an error occured in that step, but it won't follow the failed path. |
|
|
|
weasel
Starting Member
8 Posts |
Posted - 2004-04-26 : 21:48:43
|
| Ok, so I eliminated the ActiveX Script Task from the sub-package to see if the failed Transform Data Task alone would trigger the failure in the parent package, and I still get the same results. All possible "fail on first error" and "fail package on step error" options are turned on.I am at a complete loss, and i'm going to go home before I pull my hair out. Maybe a well rested mind will improve my luck in the morning. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-04-26 : 23:26:45
|
| Double-click on the success step and see what you have for the predecessor. Make sure there is only one and it's the correct one.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
weasel
Starting Member
8 Posts |
Posted - 2004-04-27 : 10:46:36
|
yes, the child package has a linear path. Here is an over view what the entire thing looks like.Child Package:ActiveX Script Task|| SuccessVConnection 1 (SQL SERVER)|| Transform DataVConnection 2 (Text File (Destination))Parent Package:Execute Package Task (Child Package)| || Success | FailureV VEmail (Success) Email (Failure) I have changed an expected path, to force the child to fail for testing this.If I execute the ActiveX Step manually in the child it fails immeadeatly stating the VBScript error that was encountered. I figured this VBScript error was not picked up by the SQL server as a step or package error, so I added "On Error Resume Next" and then added a error checking myself that would allow me to return "DTSTaskExecResult_Failure" so the SQL server would get the proper task failed notification. Upon manually testing the child ActiveX step I get the "The task reported failure on execution" DTS error as expected. If I run the entire Child package manually, the first step shows a failure and the other step is not executed, as expected.When I go to the Parent package and manually execute the Execute Package (Child) step, it reports success. If I run the entire parent package manually, it shows the Execute Package (Child) step failing, and then follows the success path [anyway], and marks the failed path at Not Run. |
|
|
|
weasel
Starting Member
8 Posts |
Posted - 2004-04-28 : 17:15:25
|
| Yea, I finally got it! It appears to have been a PEBKAC error. The "Fail on first error" option need to be on for the child package and off for the parent. In addition the "Fail package on step error" for the steps in the child need to be off. If they are both on then you get the mysterious allways succeds even though it fails situation. In addition to this, you must go into the parent package and tell the Execute Package Task to use the most recent version of the child package each time you change the child, since SQL Server apparently keeps versions around for each time you save the package.Is there is a way to have the Execute Package Step automatically use the most upto date version automatically? |
|
|
|
gauss9
Starting Member
1 Post |
Posted - 2004-05-24 : 15:30:58
|
| execute the dts package from a vbscript then you can specify to always use the latest version. |
|
|
|
weasel
Starting Member
8 Posts |
Posted - 2004-05-24 : 16:52:20
|
| What I ended up doing was once I had it working properly I just killed all previous versions... |
|
|
|
|
DTS Package does not stop