Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2002
    Location
    Norway
    Posts
    8

    Question Unanswered: DTS transformation question

    Hello,
    I am having problems with a quite simple DTS package. I guess the solution is quite simple, but I can't see it.

    E.q: I am importing a Excel sheet with 1-3 columns into a predefined table in SQL 7.0 (default values). The number of columns might however change from time to time. I have designed the DTS package transformation using VB script.

    Function Main()
    DTSDestination("A") = DTSSource("A")
    DTSDestination("B") = DTSSource("B")
    DTSDestination("C") = DTSSource("C")
    Main = DTSTransformStat_OK
    End Function

    but when I execute the package linked to a Excel file just containing column A and C I get an error claiming that the DTSDestination("B") is missing.

    I am looking for a method that will either ignore the error or ignore the command under execution.

    My real DTS package has several columns that might be missing.

    Thanks

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Post some examples of your data. How are you able to map to columns a and c skipping b in your destination ?

  3. #3
    Join Date
    Dec 2002
    Location
    Norway
    Posts
    8
    Originally posted by rnealejr
    Post some examples of your data. How are you able to map to columns a and c skipping b in your destination ?
    I was thinking of something like this

    Function Main()
    DTSDestination("A") = DTSSource("A")
    IF ( DTSSource("B") EXIST) Then
    DTSDestination("B") = DTSSource("B")
    ELSE
    DTSDestination("B") = "Mydefaultvalue"
    END IF
    DTSDestination("C") = DTSSource("C")
    Main = DTSTransformStat_OK
    End Function

    , but the logical expression ( DTSSource("B") EXIST) is not valid. I am looking for some command or expression that will handle this.

    I have seen postings where the expression is like:
    IF isNull( DTSSource("B")) Then .....
    ,but isNull is just checking for empty cells. In my case the column "B" is not available.

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    I don't think this is possible - I will check further - However, since you only have 3 possibilities you can create a path in dts to check the number of columns in the excel file and based on the answer (success or failure) have it execute the appropriate transformation task.

  5. #5
    Join Date
    Dec 2002
    Location
    Norway
    Posts
    8

    Smile

    Originally posted by rnealejr
    I don't think this is possible - I will check further - However, since you only have 3 possibilities you can create a path in dts to check the number of columns in the excel file and based on the answer (success or failure) have it execute the appropriate transformation task.
    Thanks rnealejr

    I just got feedback from the SQL Development Team. They say:


    The ActiveX Script task includes a list of functions that you can use in the
    DTS scripts.
    Instead of
    IF ( DTSSource("B") EXIST) Then
    DTSDestination("B") = DTSSource("B")
    ELSE
    ....

    Try
    IF not DTSSource("B") is nothing Then
    DTSDestination("B") = DTSSource("B")
    ELSE
    ....
    I have not been able to try this yet, but hopfully it will work. I will give you feedback if it does. Thanks again for your time.

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    Interesting - If you are successful with this, then DTSSource is an object variable. You might try Empty/vbNull/Null as well.

    Let me know if you are successful.

  7. #7
    Join Date
    Dec 2002
    Location
    Norway
    Posts
    8
    Originally posted by rnealejr
    Interesting - If you are successful with this, then DTSSource is an object variable. You might try Empty/vbNull/Null as well.

    Let me know if you are successful.
    Hi,

    It did not work.

    It seems that the Source columns are validated before the VBscript is executed.

    Lasse

Posting Permissions

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