Re: Pass variables from parent DTS to child DTS package...
From: Andreas Terzis (cubeware_at_pacbell.net)
Date: 05/13/04
- Next message: Ken Cooper: "Re: Changing DTS Package Password"
- Previous message: travis_5579_at_hotmail.com: "DTS - Oracle Driver 8i Error"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 13 May 2004 01:28:10 -0700
Pass variables from parent DTS to child DTS package...
From: gr8patra
Date Posted: 4/14/2004 12:17:00 AM
Hi there,
I have a parent DTS that calls a child DTS through ActiveX scripts. Both
of them have the same global variables defined in them. I need to call
the child DTS from the parent and pass it a different date (one of the
variables) to run it for. I don't seem to get it work. Here is the code
that I use in the ActiveX scripts. Please let me know if you have any
ideas or if you need me to provide you with more information.
Thanks,
Andreas
************************************************************************
***************
' ActiveX in Parent DTS
Option Explicit
Function Main
Dim OriginalDate
Dim TargetDate
Dim InputFile
Dim OutputFolder
Dim TotalMonths
Dim CurrDate
Dim counter
Dim fso
Dim pkg
Dim conTextFile
Dim stpEnterLoop
Dim stpFinished
Dim oPkg, oStep
Dim sServer, sUID, sPwd, iSecurity, sPkgPwd, sPkgName, sErrMsg
Set oPkg = CreateObject("DTS.Package")
sServer = "(local)"
sUID = ""
sPwd = ""
iSecurity = DTSSQLStgFlag_UseTrustedConnection
sPkgPwd = ""
sPkgName = "StaticPoolJUNKCHILD"
' Load Child Package
oPkg.LoadFromSQLServer sServer, sUID, sPWD, iSecurity , sPkgPWD, "",
"", sPkgName
Set pkg = DTSGlobalVariables.Parent
Set fso = CreateObject("Scripting.FileSystemObject")
OriginalDate = DTSGlobalVariables("OriginalDate").Value
TargetDate = DTSGlobalVariables("TargetDate").Value
OutputFolder = DTSGlobalVariables("OutputFolder").Value
InputFile = DTSGlobalVariables("InputFile").Value
TotalMonths = DateDiff("m", OriginalDate, TargetDate)
counter = 0
While counter < TotalMonths
' Change OriginalDate in child package to the one calculated in
parent package
oPkg.GlobalVariables("OriginalDate").Value =
DTSGlobalVariables("OriginalDate").Value
' Execute the child package
oPkg.Execute
CurrDate = DateAdd("m", counter, DateAdd("d", 1, OriginalDate))
- 1
DTSGlobalVariables("OriginalDate").Value = CurrDate
msgbox "Parent DTS - counter: " & counter & ", CurrentDate: " & CurrDate
counter = counter + 1
Wend
Main = DTSTaskExecResult_Success
End Function
************************************************************************
**************
' ActiveX in Child DTS
Dim oFSO
Dim sDate
Dim oDate
Dim tDate
Dim fName
Dim fTemp
Dim fDir
Dim fSourceFile
Dim fDestFile
Function Main()
fTemp = DTSGlobalVariables("InputFile").value
fName = Left(fTemp, Len(fTemp) - 4)
sDate = DTSGlobalVariables("OriginalDate").value
oDate = Month(sDate) & "-" & Day(sDate) & "-" & Year(sDate)
sDate = DTSGlobalVariables("TargetDate").value
tDate = Month(sDate) & "-" & Day(sDate) & "-" & Year(sDate)
fDir = DTSGlobalVariables("OutputFolder").value & "\"
fSourceFile = fDir & fName & ".xls"
fDestFile = fDir & "Archive\" & fName & " " & oDate & " to " & tDate
& ".xls"
msgbox "Child DTS - InputFile: " & fTemp & ", OriginalDate: " &
DTSGlobalVariables("OriginalDate").value & ", DestFile: " & fDestFile
DTSGlobalVariables("OutputFile").value = fDestFile
Main = DTSTaskExecResult_Success
End Function
Re: Pass variables from parent DTS to child DTS package...
From: Darren Green
Date Posted: 4/14/2004 1:26:00 AM
The code works for me with suitable variables specified. You don't
appear to
be passing through TargetDate which is used in both packages. Same issue
with OutputFolder.
Try manually setting the child [package values by hand and executing it.
If
your values are wrong and the package errors then there will be no
information reported in the parent, so it may appear fine, but not do
anything, when the child is actually failing. You can check the Step
execution result for each step in the child, in the parent script to get
some idea of failure.
-- Darren Green http://www.sqldts.com "gr8patra" <gr8patra@pacbell.net> wrote in message news:7289CFDA-708D-4200-8C2F-72CFE39F9AEE@microsoft.com... > Hi there, > I have a parent DTS that calls a child DTS through ActiveX scripts. Both of them have the same global variables defined in them. I need to call the child DTS from the parent and pass it a different date (one of the variables) to run it for. I don't seem to get it work. Here is the code that I use in the ActiveX scripts. Please let me know if you have any ideas or if you need me to provide you with more information. > Thanks, > Andreas > > ************************************************************************ **** *********** > > ' ActiveX in Parent DTS > Option Explicit > > Function Main > Dim OriginalDate > Dim TargetDate > Dim InputFile > Dim OutputFolder > Dim TotalMonths > Dim CurrDate > Dim counter > > Dim fso > Dim pkg > Dim conTextFile > Dim stpEnterLoop > Dim stpFinished > > Dim oPkg, oStep > Dim sServer, sUID, sPwd, iSecurity, sPkgPwd, sPkgName, sErrMsg > > Set oPkg = CreateObject("DTS.Package") > > sServer = "(local)" > sUID = "" > sPwd = "" > iSecurity = DTSSQLStgFlag_UseTrustedConnection > sPkgPwd = "" > sPkgName = "StaticPoolJUNKCHILD" > > ' Load Child Package > oPkg.LoadFromSQLServer sServer, sUID, sPWD, iSecurity , sPkgPWD, "", "", sPkgName > > Set pkg = DTSGlobalVariables.Parent > Set fso = CreateObject("Scripting.FileSystemObject") > > OriginalDate = DTSGlobalVariables("OriginalDate").Value > TargetDate = DTSGlobalVariables("TargetDate").Value > OutputFolder = DTSGlobalVariables("OutputFolder").Value > InputFile = DTSGlobalVariables("InputFile").Value > > TotalMonths = DateDiff("m", OriginalDate, TargetDate) > > counter = 0 > While counter < TotalMonths > > ' Change OriginalDate in child package to the one calculated in parent package > oPkg.GlobalVariables("OriginalDate").Value = DTSGlobalVariables("OriginalDate").Value > > ' Execute the child package > oPkg.Execute > > CurrDate = DateAdd("m", counter, DateAdd("d", 1, OriginalDate)) - 1 > DTSGlobalVariables("OriginalDate").Value = CurrDate > msgbox "Parent DTS - counter: " & counter & ", CurrentDate: " & CurrDate > counter = counter + 1 > > Wend > > Main = DTSTaskExecResult_Success > End Function > > ************************************************************************ **** ********** > > ' ActiveX in Child DTS > Dim oFSO > Dim sDate > Dim oDate > Dim tDate > Dim fName > Dim fTemp > Dim fDir > Dim fSourceFile > Dim fDestFile > > Function Main() > > fTemp = DTSGlobalVariables("InputFile").value > fName = Left(fTemp, Len(fTemp) - 4) > > sDate = DTSGlobalVariables("OriginalDate").value > oDate = Month(sDate) & "-" & Day(sDate) & "-" & Year(sDate) > sDate = DTSGlobalVariables("TargetDate").value > tDate = Month(sDate) & "-" & Day(sDate) & "-" & Year(sDate) > fDir = DTSGlobalVariables("OutputFolder").value & "\" > fSourceFile = fDir & fName & ".xls" > fDestFile = fDir & "Archive\" & fName & " " & oDate & " to " & tDate & ".xls" > > msgbox "Child DTS - InputFile: " & fTemp & ", OriginalDate: " & DTSGlobalVariables("OriginalDate").value & ", DestFile: " & fDestFile > DTSGlobalVariables("OutputFile").value = fDestFile > Main = DTSTaskExecResult_Success > End Function > Re: Pass variables from parent DTS to child DTS package... From: Andreas Terzis Date Posted: 4/14/2004 2:02:00 PM Darren, Thanks for the response. I only need OriginalDate changed by the parent package. The child package runs fine by itself without errors. The parent package runs fine as well, but the problem is that the new OriginalDate specified in the parent package doesn't get passed to the child package. If you specify different values for the OriginalDate in the two packages, you'll see that the one for the parent package doesn't get passed to the child package and the child package uses its own. You could create two different text files on your local drive that have the structure below and use those in the DTS packages tested, if you have the time. The DTS packages can have just a Dynamic Properties Task and an ActiveX script. Any input would be greatly appreciated. Thanks again, Andreas *********************************************** The file below is called: "SP Dates.ini" and is used in one of the DTS packages. The OriginalDate can be changed and saved under the same name in a different subdirectory and used in the other DTS package. ----------------------- [Static Pool] OriginalDate="12-31-2003" TargetDate="02-29-2004" OutputFolder="\\oomc.root\user\Andreas.Terzis\Docs\Computing\Software\Re ports\Output\Option One\Static Pool" InputFile="Static Pool Results.xls" *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! Re: Pass variables from parent DTS to child DTS package... From: Darren Green Date Posted: 4/16/2004 6:48:00 AM I created two packages and added ActiveX Script tasks using your code last time. The variable was passed correctly between the two packages. In your description below I do not understand why I should need two text files. Surely the point is to only have one consumed by the parent which passes the values into the child . If the child also reads values why bother passing anything through? Make sure your global variable names are exactly the same, both in the package properties and code, as they are case sensitive. -- Darren Green http://www.sqldts.com "Andreas Terzis" <gr8patra@pacbell.net> wrote in message news:%23dEDU3kIEHA.3376@TK2MSFTNGP09.phx.gbl... > Darren, > Thanks for the response. I only need OriginalDate changed by the parent > package. The child package runs fine by itself without errors. The > parent package runs fine as well, but the problem is that the new > OriginalDate specified in the parent package doesn't get passed to the > child package. If you specify different values for the OriginalDate in > the two packages, you'll see that the one for the parent package doesn't > get passed to the child package and the child package uses its own. You > could create two different text files on your local drive that have the > structure below and use those in the DTS packages tested, if you have > the time. The DTS packages can have just a Dynamic Properties Task and > an ActiveX script. Any input would be greatly appreciated. > Thanks again, > Andreas > > *********************************************** > > The file below is called: "SP Dates.ini" and is used in one of the DTS > packages. The OriginalDate can be changed and saved under the same name > in a different subdirectory and used in the other DTS package. > > ----------------------- > > [Static Pool] > OriginalDate="12-31-2003" > TargetDate="02-29-2004" > OutputFolder="\\oomc.root\user\Andreas.Terzis\Docs\Computing\Software\Re > ports\Output\Option One\Static Pool" > InputFile="Static Pool Results.xls" > > > > *** Sent via Developersdex http://www.developersdex.com *** > Don't just participate in USENET...get rewarded for it! Re: Pass variables from parent DTS to child DTS package... From: Andreas Terzis Date Posted: 4/17/2004 1:35:00 AM Darren, Thanks for the response. The child package is executed by itself sometimes by a job and runs let's say for the last month. Sometimes though, I need to have a job run which calls the parent package, which in turn calls the child package. The child package has all the logic for my report and the parent package only changes the OriginalDate and then executes the child package in a loop. Let's say that the child package uses an OriginalDate of '01-31-04' (and a TargetDate of '02-29-04'). When it is executed by itself (not called by the parent package), it gets data between those two dates. But I need to have the child package generate 13 reports sometimes, for trending purposes. The TargetDate always remains the same but the OriginalDate should be '01-31-03' in the first execution, '02-28-03' in the second and so on, up to '01-31-04'. That's why I need those OriginalDate instances change every time in the parent package. I guess I can eliminate the parent package text input file, but I am not sure if the user would want the historical data generated for 13 months or 25 or 6. Does that make sense? I am glad you got your example to work. I still cannot get mine to work. Is there any way you could e-mail me the two packages (if they still work after my latest explanation)? Basically, the OriginalDate in the child package should change each time the package is called by the parent package. That should be printed every time by the "msgbox" command (msgbox "ChildDTS - ..."). I would appreciate your help. Thanks, Andreas *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! Re: Pass variables from parent DTS to child DTS package... From: Darren Green Date Posted: 4/19/2004 3:52:00 PM In message <OJyBTQEJEHA.3104@TK2MSFTNGP10.phx.gbl>, Andreas Terzis <gr8patra@pacbell.net> writes >Darren, >Thanks for the response. The child package is executed by itself >sometimes by a job and runs let's say for the last month. Sometimes >though, I need to have a job run which calls the parent package, which >in turn calls the child package. The child package has all the logic >for my report and the parent package only changes the OriginalDate and >then executes the child package in a loop. Let's say that the child >package uses an OriginalDate of '01-31-04' (and a TargetDate of >'02-29-04'). When it is executed by itself (not called by the parent >package), it gets data between those two dates. But I need to have the >child package generate 13 reports sometimes, for trending purposes. The >TargetDate always remains the same but the OriginalDate should be >'01-31-03' in the first execution, '02-28-03' in the second and so on, >up to '01-31-04'. That's why I need those OriginalDate instances change >every time in the parent package. I guess I can eliminate the parent >package text input file, but I am not sure if the user would want the >historical data generated for 13 months or 25 or 6. Does that make >sense? I am glad you got your example to work. I still cannot get mine >to work. Is there any way you could e-mail me the two packages (if they >still work after my latest explanation)? Basically, the OriginalDate in >the child package should change each time the package is called by the >parent package. That should be printed every time by the "msgbox" >command (msgbox "ChildDTS - ..."). I would appreciate your help. >Thanks, >Andreas > Andreas, If the child package is normally executed by itself from the job, what sets the OriginalDate for that run? Is there some logic in the child overwriting the variables supplied by the parent? (I'll send the packages tomorrow as I'm on another machine now) -- Darren Green (SQL Server MVP) DTS - http://www.sqldts.com PASS - the definitive, global community for SQL Server professionals http://www.sqlpass.org Re: Pass variables from parent DTS to child DTS package... From: Andreas Terzis Date Posted: 4/20/2004 1:19:00 AM Darren, The child package was created first and it took its variables from the text file that I supplied in an earlier thread of the posting. Then, since my users wanted to execute the child package 13 times I started creating the parent package and I thought of using the same text file with the first OriginalDate of the executions and of course the TargetDate along with the other variables. Then I would execute the parent package (which is nothing close to completion unfortunately), which would start with let's say: '01-31-2003' as the OriginalDate and '02-29-2004' as the TargetDate. It would pass those variables to the child package and the child package would execute all the logic (stored procs, ActiveX scripts, exports to Excel and E-mailing of that). After it's done, since there is a loop in the parent package, the OriginalDate would change to '02-28-2003' but the TargetDate would stay the same. Then the child package would execute for the second time, and so on, until the execution of the child package with '01-31-2004' as the OriginalDate (and of course the same TargetDate of '02-29-2004'). In other words as I have explained at another thread, I only want the OriginalDate to change. If you run the code in the parent package, you'll see that it loops through those dates. My problem is that the parent package doesn't pass the OriginalDate variable to the child package. That was supposed to be done by the statement: oPkg.GlobalVariables("OriginalDate").Value = DTSGlobalVariables("OriginalDate").Value Am I missing something? I would really appreciate your help; I am way too late with this project and my managers are giving me a hard time. Any input would be great. Thanks again, Andreas ------------------------------------------------------------ Hi there, I implemented what you were describing but didn't work. I finally created two variables in the child package, one called 'NewOriginalDate' and one called 'UseNewOriginalDate'. If I ran the parent package first, I would change the 'UseNewOriginalDate' variable of the child package to 1. Then the child package would be executed and it have a decision in an ActiveX script where if the 'UseNewOriginalDate' variable was 1 (instead of 0 as I set it to in the child package under the Global Variables), which it is then the 'OriginalDate' of the child package would be the 'NewOriginalDate' from the parent package instead of its own 'OriginalDate' which would be set in a dynamic properties task. If the child package would be executed by itself, then the decision would evaluate to 0 and its own 'OriginalDate' would be read from a text file. The problem that I am facing with this now is that the correct variables get passed from parent to child but once this is done in the ActiveX script task (right after the dynamic properties task), then the child package reports a success (I get the message: "Successfully completed..."), and the package ends. None of the steps after the ActiveX script are executed!!! If the child package is executed by itself, all the steps after the ActiveX script are executed. Any ideas why the child package exits without failure and it reports success mistakenly? Whoever likes, I can e-mail some screenshots, if all of the above doesn't make sense. Thanks, Andreas Terzis *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!
- Next message: Ken Cooper: "Re: Changing DTS Package Password"
- Previous message: travis_5579_at_hotmail.com: "DTS - Oracle Driver 8i Error"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
