Returning Recordcounts from Datapump to a Variable in DTS

[scoutant]

Does anyone know how to (easily) get the # of records transferred in a datapump task (similar to what is returned in the execution window). I would prefer to be able to return the value into a Global Variable. I can't run the DTS packages from VB code, however. Any help along these lines would be most appreciated.

 

[JohnDTampaBay]

You can use this ActiveX script as a place to start. Run this script after the data pump to populate the global variable. Your variable name and data pump task name will differ. Good luck!

Function Main()
  DIM oPkg
  SET oPkg = DTSGlobalVariables.Parent

  DTSGlobalVariables("@RowCount").Value = _
    oPkg.Tasks("DTSTask_DTSDataPumpTask_1").Properties("RowsComplete").Value

  SET oPkg = Nothing
  Main = DTSTaskExecResult_Success
End Function

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[scoutant]

You Rock, man. Thanks! This'll be much better than counting the lines in the textfile...

Scott

 

[scoutant]

BTW, just to give back a little...

If you are processing many textfiles from a single DTS package, the following modification to JohnDTampaBay's post works like a charm... You should only have to change the items in red

Function Main()
    Dim goPackage   
    Dim objFSO
    Dim objFolder
    Dim objDestFile
    Dim objTask
    Dim objConn
    Dim objFile
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.GetFolder("[COLOR=red]\\Server\share\Folder\[/color]")

    Set objDestFile = objFSO.OpenTextFile(objFolder.Path + "[COLOR=red]\RowCounts.log[/color]", 8, True)
    
    Set goPackage = DTSGlobalVariables.Parent
    
    For Each objTask In goPackage.Tasks
        If Left(objTask.Properties(1).Value, 23) = "DTSTask_DTSDataPumpTask" Then
            For Each objConn In goPackage.Connections
                If objConn.ID = objTask.Properties("DestinationConnectionID") Then Exit For
            Next
            
            Set objFile = objFSO.GetFile(objConn.DataSource)
            
            'Write "RowsComplete" values to the recordcount textfile
            Call objDestFile.WriteLine(objFile.Path & vbTab & _
                CStr(objTask.Properties("RowsComplete").Value) & vbTab & _
                objFile.DateLastModified)
            
            Set objFile = Nothing
            Set objConn = Nothing
        End If
    Next

    Set goPackage = Nothing
    'Cleanup objects
    objDestFile.Close
    Set objDestFile = Nothing
    Set objFolder = Nothing
    Set objFSO = Nothing

    Main = DTSTaskExecResult_Success
End Function