DTS question

• Chris Lee-152270

  SSCommitted

  Points: 1809

  More actions

  April 30, 2007 at 5:22 pm

  #92347

  Hi,

  I could not find a place MS SQL 2000 DTS folder and placed my question here.

  I am trying to use DTS to import some CSV (or Excell) file into MS SQL 2000. Building dynamic function inside an VB ActiveX Script, it should have someting like:

  "Function Main......DTSDestination(""Col1"") = DTSSource(""" & ColTitle1 & """) ....DTSDestination(""Col2"") = DTSSource(""" & ColTitle2 & """) ....End Function"

  DTSSource is a list of returned value from a stored procedure.  The returned list values should match column name from CSV file. Is there a way I could check whether DTSSource is valid or not? I tried IsEmpty(DTSSource(ColTitle1)) and few other ways; but no luck.

  Thanks.

  CHris

• David Burrows

  SSC Guru

  Points: 65142

  More actions

  May 1, 2007 at 4:04 am

  #703495

  Not sure how you get the values but presuming ColTitle1 contains the column name then you could do something like this

  On Error Resume Next

  DTSDestination("Col1") = DTSSource(ColTitle1)

  If Err.Number <> 0 Then

    'Error Do Something

  End if

  DTSDestination("Col2") = DTSSource(ColTitle2)

  If Err.Number <> 0 Then

    'Error Do Something

  End if

  I think Err.Number = 5 if the column is not found

  Far away is close at hand in the images of elsewhere.
  Anon.

• Chris Lee-152270

  SSCommitted

  Points: 1809

  More actions

  May 1, 2007 at 9:33 am

  #703573

  David,

  Thanks for the helping hand.

  I got ColTitle1 from a stored procedure. One of column name in CSV should match the name of ColTitle1. If anyway I could something like this:

  IF Err(DTSSource(ColTitle1)) <> 1 Then

     set pkg = DTSGlobalVariables.Parent

     set stpbegin = pkg.Steps("DTSStep_DTSActiveScriptTask_3")

  End If

  Chris