global variable in dts - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

global variable in dts

Author

Topic

krn25
Starting Member

2 Posts

Posted - 2006-06-23 : 06:55:58

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

krn25

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2006-07-05 : 13:37:48

I had issues passing variables and execute packages using the "Execute Package Task", so I began using the code below to do the same for me. On my scenario, I only have 2 levels of DTS, 1 that pulls the values from reference tables and a second one that useds the variables as parameters and creates a set of reports. On this case, the master package uses variables (AllianceName and ReportName) from a table and keeps moving to the next record, passing each record from a table (SP) as a paramater to the next package.

You might be able to modify the code to suit your needs.

Hope this helps.

'**********************************************************************
' Visual Basic ActiveX Script
'**********************************************************************

Function Main()
Dim objAllianceName
Dim objReportName
Dim objChildPackage

'Create local ADO Recordset
Set objAllianceName = DTSGlobalVariables("AllianceName").Value
Set objReportName = DTSGlobalVariables("ReportName").Value

'Create child package and load from SQL Server
Set objChildPackage = CreateObject("DTS.Package")
objChildPackage.LoadFromSQLServer "SERVER", "USERNAME", "PA$$W0RD", DTSSQLStgFlag_Default,"","","","2ndLevelDTSPackageName"

'Loop through recordset
Do While not objAllianceName.eof
Do While not objReportName.eof
' Msgbox objAllianceName.Fields("AllianceName").Value

'Pass global variable to child package and execute
objChildPackage.GlobalVariables.Item("AllianceName") = objAllianceName.Fields("AllianceName").Value
objChildPackage.GlobalVariables.Item("ReportName") = objReportName.Fields("ReportName").Value

objChildPackage.Execute

'Move to next record

objReportName.MoveNext
objAllianceName.MoveNext
Loop
Loop

'Destroy object
objChildPackage.UnInitialize
Set objChildPackage = Nothing
Set objAllianceName = Nothing
Set objReportName= Nothing

'Return Success
Main = DTSTaskExecResult_Success

End Function

---

Thanks!
Igor.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources