Hello,

I am trying to introduce myselfe to Global Variables in dts and I guess I'm
having a difficult time grasping how they work. The VB6 code follows and
beind it is the ActiveX script that is run in the dts package. When the dts
is run there is no data generated into a csv file which is because the
Global Variable (JobID) is null.. Can someone please explain to me what is
wrong by examining the following code?

I would really appreciate any help I can get!

Vic


' ******* VB6 Code ***
Dim oPackage As DTS.Package
Dim oPackage2 As New DTS.package2
Dim oTask As DTS.DataPumpTask

Dim lngErrNumber As Long

Dim strErrDescription As String
Dim bStatus As Boolean
Dim strPackageName As String
Dim i As Integer
Dim lpErrorCode As Long
Dim errSource As String
Dim errDescription As String
Dim strFileName As String

Set oPackage = New DTS.Package

oPackage2.GlobalVariables.Item("JobID").Value = 1
strPackageName = "dtsCoverLetter"

With oPackage
.LoadFromSQLServer gsServer, , ,
DTSSQLStgFlag_UseTrustedConnection, , , , strPackageName, 0
.LogFileName = App.path & "\Temp\dtsError.log"
.Execute
frmMain.StatusBar1.Panels("Status").Text = "Status: Creating
csv file with project data ..."
For i = 1 To .Steps.Count
If .Steps(i).ExecutionResult = DTSStepExecResult_Failure
Then
With .Steps(i)
.GetExecutionErrorInfo lpErrorCode, errSource,
errDescription
MsgBox "**DTS Error** " & lpErrorCode & Chr(10) &
Chr(10) _
& "Source: " & errSource & Chr(10) & Chr(10) _
& "Description: " & errDescription & Chr(10) &
Chr(10) _
& "File Name: " & strFileName & Chr(10) &
Chr(10) _
& "Action: File Skipped"
frmMain.StatusBar1.Panels("Status").Text =
"Status: Idle ..."
Exit Sub
End With
bStatus = False
End If
Next i
End With
Set oPackage = Nothing
' ** End of VB6 Code **


' ** ActiveX Script in DTS **

Dim oPkg, oDataPump, sSQLStatement

' Build new SQL Statement
sSQLStatement = "SELECT * FROM dbo.vuCoverLetter WHERE JobID = '" & _
DTSGlobalVariables("JobID").Value & "'"

' Get reference to the DataPump Task
Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask

' Assign SQL Statement to Source of DataPump
oDataPump.SourceSQLStatement = sSQLStatement

' Clean Up
Set oDataPump = Nothing
Set oPkg = Nothing

Main = DTSTaskExecResult_Success

' ** End of ActiveX Script

Never M ind ....

Why is it that 5 minutes after you post a question to the news group on a
problem you've worked hours on the solution becomes so obvious. It would be
nice if you could retrieve your stupid posting.

Thanks to anyone that may have taken a look!


Vic


"Vic Spainhower" <vicNoSpam (AT) perfected (DOT) com> wrote