November 17, 2004 at 8:32 am
I've got a DTS package that checks a log table to see if further DTS processing is allowed. I want it to return a 'Y' or 'N' to denote this.
Parent DTS packages will then contain this child DTS, and I want to be able to access the value from the child DTS, i.e. the 'Y' or 'N'.
I've tried doing this with global/inner/outer variables, but I can't make the Parent DTS pick up the value from the child DTS.
How can I do this?
November 17, 2004 at 9:40 am
Michael,
You'll need to programatically execute the package vs using the Execute Package task OR have the child package store the result in a SQLServer table or some other persistence mechanism outside of the package. Doing the latter will allow the parent package to execute an ExecSQL or Dynamic Properties task to read the value back in.
THis latter approach should also play far nicer with the SQL 2000 to SQL Server 2005 upgrade.
Trey
Trey Johnson | Chief Business Intelligence Architect | Cizer Software (www.cizer.com)
Who? - Cizer - http://www.cizer.com/about.htm - Blog - http://www.sqlserverbi.com/
What? - Products enhancing Microsoft Business Intelligence - http://www.cizer.com/products.htm
Wow! - Empower your Developers.... NEW Drop In Reporting - http://www.cizer.com/cnr-drop-in-reporting.htm
How? - BI Training - http://www.cizer.com/training.htm - Cizer Solutions - http://www.cizer.com/solutions.htm
November 17, 2004 at 9:44 am
Thanks for the tip - I'll give it a go 🙂
November 18, 2004 at 7:08 am
This site has an example of passing Global Variables between packages - I use this method a good bit myself.
November 18, 2004 at 7:15 am
That's excellent - thanks very much 🙂
November 18, 2004 at 9:22 am
Thanks for the help so far. If my child DTS returns a 'Y' I want to continue processing in the parent DTS. If my child DTS returns an 'N' I want the parent DTS to exit: I don't want the parent DTS to fail, I just want it to stop.
The reason for this is because the parent DTS may fail further in the processing and at that point I DO want the DTS to exit with failure. It is at this point that I will get an email informing me of the error.
However, I can't get the parent DTS to just exit. Any ideas?
November 18, 2004 at 9:37 am
Here is a sample of how to do it (I hope it fits your needs).
FUNCTION MAIN() 'This would be the start of your function
DIM oPkg
DIM stpRecipient
Set oPkg = DTSGlobalVariables.Parent
'CREATE A STEP REFERENCE FOR EACH STEP (OR TASK REFERENCE FOR EACH TASK)
SET stpRecipient = oPkg.Steps("Recipient File")
'YOUR PROCESSING CODE GOES HERE
'If my child DTS returns a 'Y'
IF DTSGlobalVariable("variable name here"). Value = "Y" THEN
stpRecipient.DisableStep = FALSE 'Enable the step
ELSE 'Stop Processing with out failing
stpRecipient.DisableStep = TRUE 'Disable the step
SET oPkg = Nothing
SET stpRecipient = Nothing
Main = DTSTaskExecResult_Success
EXIT FUNCTION 'Don't need to do this if it's the last routine
END IF
SET oPkg = Nothing
SET stpRecipient = Nothing
Main = DTSTaskExecResult_Success
END Function
November 18, 2004 at 9:45 am
Thanks again for your help. The logic of the code matches what I'm trying to achieve, so hopefully I'll get the result I'm after.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply