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
Andrea

************************************************** ************************************

' ActiveX in Parent DT
Option Explici

Function Mai
Dim OriginalDat
Dim TargetDat
Dim InputFil
Dim OutputFolde
Dim TotalMonth
Dim CurrDat
Dim counte

Dim fs
Dim pk
Dim conTextFile
Dim stpEnterLoo
Dim stpFinishe

Dim oPkg, oSte
Dim sServer, sUID, sPwd, iSecurity, sPkgPwd, sPkgName, sErrMs

Set oPkg = CreateObject("DTS.Package"

sServer = "(local)
sUID = "
sPwd = "
iSecurity = DTSSQLStgFlag_UseTrustedConnectio
sPkgPwd = "
sPkgName = "StaticPoolJUNKCHILD

' Load Child Packag
oPkg.LoadFromSQLServer sServer, sUID, sPWD, iSecurity , sPkgPWD, "", "", sPkgNam

Set pkg = DTSGlobalVariables.Paren
Set fso = CreateObject("Scripting.FileSystemObject"

OriginalDate = DTSGlobalVariables("OriginalDate").Valu
TargetDate = DTSGlobalVariables("TargetDate").Valu
OutputFolder = DTSGlobalVariables("OutputFolder").Valu
InputFile = DTSGlobalVariables("InputFile").Valu

TotalMonths = DateDiff("m", OriginalDate, TargetDate

counter =
While counter < TotalMonth

' Change OriginalDate in child package to the one calculated in parent packag
oPkg.GlobalVariables("OriginalDate").Value = DTSGlobalVariables("OriginalDate").Valu

' Execute the child packag
oPkg.Execut

CurrDate = DateAdd("m", counter, DateAdd("d", 1, OriginalDate)) -
DTSGlobalVariables("OriginalDate").Value = CurrDat
msgbox "Parent DTS - counter: " & counter & ", CurrentDate: " & CurrDat
counter = counter +

Wen

Main = DTSTaskExecResult_Succes
End Functio

************************************************** ***********************************

' ActiveX in Child DT
Dim oFS
Dim sDat
Dim oDat
Dim tDat
Dim fNam
Dim fTem
Dim fDi
Dim fSourceFil
Dim fDestFil

Function Main(

fTemp = DTSGlobalVariables("InputFile").valu
fName = Left(fTemp, Len(fTemp) - 4

sDate = DTSGlobalVariables("OriginalDate").valu
oDate = Month(sDate) & "-" & Day(sDate) & "-" & Year(sDate
sDate = DTSGlobalVariables("TargetDate").valu
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: " & fDestFil
DTSGlobalVariables("OutputFile").value = fDestFil
Main = DTSTaskExecResult_Succes
End Functio

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 (AT) pacbell (DOT) net> wrote

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.
***********
**********

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!

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 (AT) pacbell (DOT) net> wrote

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!

In message <OJyBTQEJEHA.3104 (AT) TK2MSFTNGP10 (DOT) phx.gbl>, Andreas Terzis
<gr8patra (AT) pacbell (DOT) net> writes
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

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



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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 (AT) pacbell (DOT) net> wrote

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.
****
***********
sPkgName
****
**********
".xls"



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 (AT) pacbell (DOT) net> wrote




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 (AT) TK2MSFTNGP10 (DOT) phx.gbl>, Andreas Terzis
<gr8patra (AT) pacbell (DOT) net> writes
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!