July 24, 2007 at 4:20 pm
All, I need help converting some script logic from DTS to SSIS. Basically someone wrote a generic error handler that uses Set oPackage = DTSGlobalVariables.Parent, which doesn't exists anymore in SSIS. The error handler step loops through the package information and gather information from the preceding step and reports on it.
I will have to create a new error handler, but I'm not sure how to get information from the preceding step to to the reporting.
I'm including part of the code/logic for reference:
' DTS objects
Dim oPackage, oStep, i, j, k,oStep2, strTaskName, strTaskDescription, oDataPump2, strSource, strDestination, oSQLTask
Set oPackage = DTSGlobalVariables.Parent
'Find this step
For i = 1 To oPackage.Steps.Count
Set oStep = oPackage.Steps.Item(i)
If oStep.ExecutionStatus = DTSStepExecStat_InProgress And InStr(1, oPackage.Tasks(oStep.TaskName).Description, "error handler", 1) > 0 Then
'Confirm precedent status and result, parse names
For j = 1 To oStep.PrecedenceConstraints.Count
Set oStep2 = oPackage.Steps(oStep.PrecedenceConstraints.Item(j).StepName)
strTaskDescription = oPackage.Tasks(oStep2.TaskName).Description
strTaskName = oPackage.Tasks(oStep2.TaskName).Name
If InStr(1 , strTaskDescription , "SQLTask Check" , 1 ) > 0 Then
For k=1 To oPackage.Steps.Count
If InStr(1 , oPackage.Steps.Item(k).TaskName , strTaskName, 1 ) > 0 Then
Set oStep2 = oPackage.Steps(oPackage.Steps.Item(k).PrecedenceConstraints.Item(j).StepName)
strTaskDescription = oPackage.Tasks(oStep2.TaskName).Description
strTaskName = oPackage.Tasks(oStep2.TaskName).Name
End If
Next
End If
...
I'm not looking for anyone to write a solution for me, just to point me in the right direction.
Curtis Smith
SQL Server DBA
Well in worked in Theory ...
January 30, 2010 at 10:29 am
You can try DTS xChange to convert your DTS packages. DTS xChange not only converts DTS packages but also optionally adds best practices .. which includes Logging/Auditing SSIS way not the DTS way.
Check http://www.pragmaticworks.com/Products/Business-Intelligence/DTSxChange
March 8, 2011 at 1:01 pm
Disregard....
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply