Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2008
    Posts
    1

    Unanswered: dtsPumptask/transformation looping

    using MSSQL 2000
    hi all. I have been looking everywhere for this but can't seem to find the answer.
    I am trying to create DtsDestination("") and DTSSource("") columns on the fly. I am working on a DTS looping systems that will take a csv with any number of columns and insert it to it's related table. IE a.csv pumps into TBLA, then loop. b.csv inserts into TBLB etc. I have it all working now when I use the same number of columns but unfortunately I have csv files that range from 3 to 30 cols.

    this is my active X task that is currently working:
    '************************************************* ***
    ' Visual Basic Transformation Script
    '************************************************* ***

    ' Copy each source column to the destination column
    Function Main()
    X = DTSGlobalVariables("TC").Value

    Select Case X
    case 1
    'inserts the values to TBLA
    DTSDestination("ID") = DTSSource("Col001")
    DTSDestination("Balance") = DTSSource("Col002")
    DTSDestination("AccountNo") = DTSSource("Col003")
    DTSDestination("Name") = DTSSource("Col002")
    case 2
    'inserts the values to TBLB
    DTSDestination("ID") = DTSSource("Col001")
    DTSDestination("Balance") = DTSSource("Col002")
    DTSDestination("AccountNo") = DTSSource("Col003")
    DTSDestination("Name") = DTSSource("Col002")
    case 3
    'inserts the values to TBLC
    DTSDestination("ID") = nothing ' DTSSource("Col001")
    DTSDestination("Name") = nothing ' DTSSource("Col002")
    DTSDestination("AccountNo") = DTSSource("Col003")
    DTSDestination("Balance") = DTSSource("Col004")

    end select

    Main = DTSTransformStat_OK
    End Function

    What i need to do is be able to somehow create 4 more columns for CASE 3 and remove two columns from case 2.
    anyone have an idea on how to do this? Once again..the process is working right now until i try and just go with something like
    DTSDestination("SomeOtherCol") = DTSSource("Col005")

    cheers
    Last edited by macaroo; 01-04-08 at 19:14.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I suggest that you just don't do this.
    You may think you're saving yourself time but this will blow up in your face in little time

    Besides; you're attacking this the wrong way
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •