global variable - passing to nested dts packages

[kirstyn]

Hi All

I have a master DTS package that has a global variable of 'gvImportDate'.
The master DTS package executes other DTS packages (e.g. a DTS called ImportDetailTables). These packages contain a third level of DTS packages (e.g. ImportDetailTables contains ImportSkillDetails).

I want to be able to pass the global variable through the nested DTS packages so that the 3rd level package (e.g. ImportSkillDetails) can use the global variable in a Data Transformation Task
(select
*
from
oauser.hCmsAgent
where ( INTERVALSTART BETWEEN dateadd(d,-1,?) and ?))

I managed to pass the global variable into one test package (When i look at that packages properties the global variable type = Dispatch). In here the SQL parses correctly.

When i try and pass the global variable into my precreated DTS package (ImportSkillDetails) I cant do it. I can set up a new global variable (exactly the same spelling and cases) (e.g. Importdate, type = Date, value = 01/06/2006).
But when I put in the SQL into the Data Transformation Task i get the message 'syntax error or access violation).

The connections are exactly the same as in the test DTS.

Is what I want to do possible?
If so how do i set up the global variable in the precreated dts package so that it will access the value in the master package?
How do i get past the parsing error in the data transformation task to be able to set the parameter to the global variable?

thanks for your help
krn

 

[MDXer]

the key to passing global variables to a child package is 2 part.

First your global variables must be identical in both packages Name datatype and case. You can't pass sDate to sdate if it is sDate in the parent it must be sDate in the child.

Second and the place that is the hardest usually.
Int the execute package task properties there are 3 Tabs:
General
Inner Package Global Variables
Outer Package Global Variables

DO NOTHING on the Inner Package Global Variables Tab.
on the Outer Package tab specify any global variables you want passed on using the drop downs.

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

 

[kirstyn]

thats great - thank you

Any idea why an existing package has parsing problems but a new package doesnt?

What do i need to set up inside the child package? anything? or is it all done in the execute package task?

cheers
krn