DTS Package

[sabloomer]

I am having trouble setting up a DTS package. I am VERY new at this so please try to put up with my stupid questions. I have a large text file I want to import into a tempory table, groom, and then load into the primary table. To do all of this I broke the process up into 7 different DTS packages and then scheduled them as different steps in one job. Here is my problem...

This process tests to see if a file has been updated and then imports the file if it has not already been imported today. I have a DTS package that tests if the file has been updated. I have had NO luck setting a DTS package to see if the job has already run today. My plan was to write to a log file when the process was done, or use the sysjobhistory table. Then I should be able to look at that table and tell if it has run today. For the life of me I can not figure out how to do that. The best thing I could think of was to use a VB Script in a DTS package to open a recordset and test for EOF and BOF, but I have spent 2 days trying to open a recordset in VB Script in a DTS package. I need to do it in a DTS package because I need it to return a Success or Failure so it can be a step in my scheduled job.

Thanks so much,

sabloomer

 

[SQLSister]

How about using the execute SQL task. Make the last step of your DTS being to run an execute SQL statement that inserts a record intot a ble withthe current date and time indicating the job was run.

Then the step after checking the existance of the file to import would check the data inthe job history table and see if it is earlier than todays date.


You know you can put all the steps in one DTS package that consists of execute package steps with the success and failutre workflow designed, right?

 

[sabloomer]

I can execute the SQL task to insert the record. I can not figure out how to read that table and return a Success/Failure. Once I have tested all of the packages, and I know that they work, I will try to combine them into one DTS Package.

Thanks,

Scott

 

[sabloomer]

SQLSister,

If I was using VBA in Access I would do something like this...

Dim rst As Recordset, sql As String
sql = "SELECT TranDate FROM tbl_SQL_log WHERE (((TranDate )=date()))"
Set rst = New Recordset
rst.Open sql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If rst.EOF = true and rst.BOF = true then
'No Records here
Else
'Yes I have Records
End If

 

[tb]

You could use your code (with minor changes) in an ActiveX Script Task and then flagging a global variable within the DTS package (Package Properties)

Example of what I mean . . .
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()
dim conn
dim dFromDate
dim dToDate

'Get values from global variables in DTS package - values are assigned by VB app
dFromDate = DTSGlobalVariables("FromDate&quot.value
dToDate = DTSGlobalVariables("ToDate&quot.value

set conn = CreateObject("ADODB.Connection&quot
conn.provider="sqloledb"
conn.CommandTimeout = 0
conn.ConnectionTimeout = 240

'Connect to server and execute stored proc in company
conn.open "IP Address", "Login", "Password"
conn.DefaultDatabase = "Database"
conn.execute("EXECUTE Stored Proc '" & dFromDate & "','" & dToDate & "'&quot
Main = DTSTaskExecResult_Success
End Function

Hope this makes sense . . .


"All I ask is the chance to prove that money can't make me happy." - Spike Milligan