Pass Global Var between Execute Package tasks

Question

Pass Global Var between Execute Package tasks

Sherrie Pierson

SSC Rookie

Points: 42
More actions
July 13, 2006 at 3:48 pm

#69133

Hello... I have a DTS Package that calls several Execute Package tasks. Each sub-package issues an SQL statement and they all require the same global variable value. Each package has been set up with the same global variable "active_term" but with a blank value.
I have set the inner global variable to "active_term" in all sub-packages. However when I execute the package the value isn't being sent, resulting in empty files.
I am using DTSRUNUI to generate a command file, which I edit to add the value for active_term. I even included a Dynamic Properties task as the first task in the main package with no luck.
How can I set this up so that a user can update the active_term value when executing the package? Thanks in advance,
S. Pierson

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

Hi Sherrie,

Global variable names are case-sensitive so make sure you've spelled it exactly the same in the Execute Package task as it's spelled in the sub packages.

Greg

Erik Kutzler SSCarpal Tunnel Points: 4287 More actions · Answer 2

Use the Outer Package global variable and make sure the global variable name you're using matches the global variable in the package you're executing exactly.

Sherrie Pierson SSC Rookie Points: 42 More actions · Answer 3

Thanks for your suggestions. I should mention that everything worked splendidly when I had a default value stored in each DTS package, but I found that when I submitted a different value for the global variable in the DTSRUN command file, the default value was being used. So I removed the default value from the global variables in all packages and now I'm getting empty files.

So I am sure the global variables are set up correctly in the sub-packages, and the Outer Package global variable is set as well.

Has anyone out there ever passed global variables between Execute Package tasks successfully, overriding the default value? Here's the command file, if that helps:

DTSRun /S "(local)" /N "Students" /V "{89E57000-B5D3-4D31-85B9-4F5FFC730756}" /L "C:\Temp\LOG_Students.txt" /A "active_term":"8"="200610" /W "-1" /E

Robert Davis One Orange Chip Points: 28027 More actions · Answer 4

I've had a lot of luck with setting global variables at runtime using an ActiveX script task. Here's a sample of one where I set the variable based on a record I look up in a logging table:

Function Main()

' DB objects and variables

Dim oCon

Dim sSQL

Dim sDSN : sDSN = "Driver={SQL Server};Server=MyServer; Database=MyDb;Trusted_Connection=YES"

Dim oRS

sSQL = "Select Top 1 DateAdd(hh, -1, StartTime) As PullDate From PullLog with(nolock) Where PullTypeID = 1 And PullCount > 0 And PullCount = PullCountIDB Order By StartTime Desc"

Set oCon = CreateObject("ADODB.Connection")

oCon.Open sDSN

Set oRS = oCon.Execute(sSQL)

If Not oRS.EOF And Not oRS.BOF Then

DTSGlobalVariables( "PullDate" ).Value = oRS("PullDate")

End If

oRS.Close

Set oRS = nothing

oCon.Close

Set oCon = nothing

Main = DTSTaskExecResult_Success

End Function

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Erik Kutzler SSCarpal Tunnel Points: 4287 More actions · Answer 5

The DTSRun command looks a bit off to me. Try:

DTSRun /S "(local)" /N "Students" /V "{89E57000-B5D3-4D31-85B9-4F5FFC730756}" /L "C:\Temp\LOG_Students.txt" /A "active_term:8=200610" /W "-1" /E