DTS Bulk Insert Rows Inserted

• 93fd3s

  Say Hey Kid

  Points: 683

  More actions

  November 13, 2003 at 11:07 am

  #106820

  When running a package with a Bulk Insert Task, the number of rows are reported back to the "Executing Package" window. Is this property exposed in any way to ActiveX/VbScript?

  I have a DTS Pkg. where I'd like to have the number of rows inserted by the Bulk Insert task added to the message text of a subsequent Send Mail task. I know I could query the table where the data was inserted but since the Bulk Insert task already knows that number I'd rather not hit the DB again.

  Any suggestions?

  Thanks...

• philcart

  SSC-Forever

  Points: 47794

  More actions

  November 13, 2003 at 3:20 pm

  #481978

  The following script returns the RowsComplete for a Datapump task and build a message string.

  You'll need to put the name of you step in the line that reads,

  Set oStp = oPkg.Steps("DTSStep_DTSDataPumpTask_1")

  And I'm not to sure, but I think the property name is different as well. You can check this via disconnected edit. If it's different change the line,

  iActCnt = oTsk.RowsComplete

  
  
  Option Explicit
  
  
  
  Function Main()
  
  On Error Resume Next
  
  
  
  Dim oPkg ' DTS Package object
  
  Dim oStp ' DTS step object
  
  Dim oTsk ' DTS Task object
  
  
  
  Dim sPkgName ' DTS Package name
  
  Dim sStpName ' DTS Step name
  
  Dim sTskName ' DTS Task name
  
  Dim iActCnt ' Actual record count from datapump task
  
  
  
  ' open DTS package object so we can get the current package details
  
  Set oPkg = DTSGlobalVariables.Parent
  
  sPkgName = oPkg.Name
  
  
  
  ' create object referring to appropriate datapump task
  
  Set oStp = oPkg.Steps("DTSStep_DTSDataPumpTask_1")
  
  sStpName = oStp.Name
  
  sTskName = oStp.TaskName
  
  Set oTsk = oPkg.Tasks(oStp.TaskName).CustomTask
  
  ' get actual record count from datapump task
  
  iActCnt = oTsk.RowsComplete
  
  
  
  ' build message
  
  sMsg = "DTS Package: " & sPkgName & " Step: " & sStpName & " Task: " & sTaskName & vbCrLf
  
  sMsg = sMsg & "Number of records processed was " & iActCnt
  
  
  
  ' save the resulting error message to a local global variable
  
  DTSGlobalVariables("gvsMsg").Value = sMsg
  
  
  
  Set oTsk = Nothing
  
  Set oStp = Nothing
  
  Set oPkg = Nothing
  
  
  
  ' signal task success
  
  Main = DTSTaskExecResult_Success
  
  
  
  End Function
  
  

  Hope this helps

  Phill Carter

  --------------------

  Colt 45 - the original point and click interface

  --------------------
  Colt 45 - the original point and click interface