DTS Global Variables

[Sylence]

Problem:
I have a DTS package scheduled every morning to import data form a .csv into our SQL Server Database.

In the package I have multiple .csv sources processing data into one SQL Server destination.

Each .csv source has its respective Global Variable to record the date of the last import.

All transformation processes were custom coded using ActiveX script and the code is responsible for updating the Global Variable.

The Global Variable is not consistantly being updated when the package is triggered by the SQL Server Agent (scheduler).

Note:
The Global Variable will update if I manually run the package twice. The first run will move the data, but not update the Global Variable until the second run.

If you need any more info to help solve this problem, please request it.

Thanks!!!

 

[cbhnp]

How and where is the Global Variable being used to "record the date of the last import"?

 

[Sylence]

This is the code from the DTS package where the Global Variable is update everytime a new record is entered.
**********************************************************
' Copy each source column to the destination column
Function Main()
Dim curDate, loggerID

loggerID = "E3226-01"
curDate = CDate(DTSSource("Col002") & " " & DTSSource("Col003"))

If CDate(DTSGlobalVariables("E3226-01").Value) < curDate Then

DTSDestination("LoggerID") = loggerID
DTSDestination("DateTime") = curDate
DTSDestination("PVKW") = getKW(DTSSource("Col004"))
DTSDestination("DataNA") = 0
DTSGlobalVariables("E3226-01") = curDate
Main = DTSTransformStat_OK
Else

Main = DTSTransformStat_SkipRow

End if

End Function


Function getKW(inputKW)
If inputKW < 0 then
getKW = 0
Else
getKW = inputKW
End if
End Function

 

[cbhnp]

Maybe I'm misreading this but it doesn't look like you need a global variable in this script. All you're doing is comparing the datetime stored in Col002/Col003 of the current record with the previous record. So only the first record needs to be compared to a previous file import.

Global variables don't get saved from run to run unless the DTS is saved in between. The first step of my imports is to fill the global variable with the max(datetime) from the destination table with a SQLTask.

select LastImportDate = max(datetime) from table

LastImportDate would be an Output Parameter mapped to the global variable.