Post reply

convert ActiveX script logic from DTS to SSIS

  • July 24, 2007 at 4:20 pm

    #117788

    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

    #1111202

    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

    #1296102

    Disregard....

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply