August 19, 2002 at 4:54 pm
Below is a piece of code that is part of a DTS(2) that executes another DTS(1) and then loops through the steps of DTS(1) and writes the results of each step to a table as a log using a stored procedure. The problem that I'm seeing is that the step that is shown with "iCount" does not match the order of the steps within DTS(1) which have workflow precedences. For example, in DTS(1), the first step is "Delete Records" with a SQLTask. Step two is "Build SQL" with an ActiveX Script which creates the SQL statement in step three. Step three is "Run SQL" with a DataPump.
The order of "iCount" shows up with 1 associated with step 3, iCount=2 matches up with step 1 and iCount=3 matches with step 2
Any ideas as to why the order of "iCount" does not match the sequence that the steps are ordered in within the DTS?
Thanks!!!
PS -- I'm using SQL Server 7
Function Main()
Dim objPkg
Dim objLOGpkg
Dim objLOGtask
Dim myLogMsg
Dim iCount
'Initialize LOG package
Set objLOGpkg = CreateObject("DTS.Package")
objLOGpkg.LoadFromSQLServer "servername", "user", "password", DTSSQLStgFlag_UseTrustedConnection,,,, _
"BKE_DTS_Process_Log"
Set objLOGtask = objLOGpkg.Tasks("DTSTask_DTSExecuteSQLTask_1").CustomTask
'--------------------------------------------
'Create and Execute the package
Set objPkg = CreateObject("DTS.Package")
objPkg.LoadFromSQLServer "servername", "user", "password", DTSSQLStgFlag_UseTrustedConnection,,,, _
"BKE_DTS_Create_New_Table"
objPkg.Execute
'Check For Errors
For iCount = 1 To objPkg.Steps.Count
If objPkg.Steps(iCount).ExecutionResult = 0 Then
myLogMsg = "Successful"
Else
myLogMsg = "Failed"
End If
objLOGtask.SQLStatement = "exec sp_myProcessTime_w_Parameter " & _
"'BKE_DTS_Create_New_Table','" & _
objPkg.Steps(iCount).Description & " (" & objPkg.Steps(iCount).Name & ") ','" & _
myLogMsg & "'"
objLOGpkg.Execute
Next
'Clean-up objects
Set objLOGtask = Nothing
Set objLOGpkg = Nothing
Set objPkg = Nothing
Main = DTSTaskExecResult_Success
End Function
August 19, 2002 at 5:01 pm
I could be wrong, but my guess is that the index number of each step is assigned at the time it is created...not based on the order it is executed in the workflow...
hth,
Michael
Michael Weiss
Michael Weiss
August 20, 2002 at 7:52 am
Is there a way to get what the index of each step is to test this, or better yet, a way to get the order of execution of each step of the DTS?
August 20, 2002 at 4:30 pm
Michael's right. The index is the order in which the step is created, not executed.
You could include another column in your log;
objPkg.Steps(iCount).StartTime to find the exection order.
Just curious, why not use DTS's built in logging features?
John
August 22, 2002 at 7:58 am
I have added start-time and finish-time to the code and it works beautifully. (Below is the code I used)
Concerning the built-in logging function of DTS, how do I get it to work? Does the DTS have to be saved in the MetaData area?
Thanks!!!
Sample Code
'Check For Errors
For iCount = 1 To objPkg.Steps.Count
If objPkg.Steps(iCount).ExecutionResult = 0 Then
my_Log_Msg = "Successful"
Else
my_Log_Msg = "Failed"
End If
my_LOG_task.SQLStatement = "exec sp_myProcessTime " & _
"'" & objPkg.Name & "', " & _
"'" & objPkg.Steps(iCount).Description & "', " & _
"'" & objPkg.Steps(iCount).Name & "', " & _
"'" & my_Log_Msg & "', " & _
"'" & objPkg.Steps(iCount).StartTime & "', " & _
"'" & objPkg.Steps(iCount).FinishTime & "'"
my_LOG_pkg.Execute
Next
August 22, 2002 at 9:56 am
Start by checking out Extended DTS Objects in bol. Sorry, I don't have time to look into this further right now. If you find a solution, please post.
Thanks,
John
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply