SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Import/Export (DTS) and Replication (2000)
 global variable in dts
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

krn25
Starting Member

2 Posts

Posted - 06/23/2006 :  06:55:58  Show Profile  Reply with Quote
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

USA
407 Posts

Posted - 07/05/2006 :  13:37:48  Show Profile  Click to see igorblackbelt's MSN Messenger address  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000