Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2003
    Posts
    5

    Unanswered: IF construct in DTS

    Hi All,

    I am new to DTS. I have to pick data from a excel sheet and put it in SQL Server table. If the column value is N/A in the excel then I should load NULL in the target table. Else the value. This is being the case I wrote this construct and it seems to load only NULLs into the target and the values are not getting loaded.

    If Trim(UCase(DTSSource("ABC transport"))) = "N/A" Then DTSDestination("ABC_transport") = NULL Else DTSDestination("ABC_transport") = DTSSource("ABC transport")
    Main = DTSTransformStat_OK

    Thanks in advance.

    Regards,
    Sathish

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    try the code below, use the line breaks I have...

    If Trim(UCase(DTSSource("ABC transport"))) = "N/A" Then
    DTSDestination("ABC_transport") = NULL
    Else
    DTSDestination("ABC_transport") = DTSSource("ABC transport")
    End If
    Main = DTSTransformStat_OK

  3. #3
    Join Date
    Nov 2003
    Posts
    5

    If Construct...

    Hi,

    Thanks for the reply.

    I tried it, still it loads only the NULL and not the values.

    Regards,
    Sathish

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Well the syntax is correct.

    I'd check the source data. Is excel displaying N/A when there is something else in the cell? I know it will sometimes show rounded figures when the cell hold the full data.

    Can you post a sample of the excel spreadsheet?

  5. #5
    Join Date
    Nov 2003
    Posts
    5
    Originally posted by rokslide
    Well the syntax is correct.

    I'd check the source data. Is excel displaying N/A when there is something else in the cell? I know it will sometimes show rounded figures when the cell hold the full data.

    Can you post a sample of the excel spreadsheet?
    Attached Files Attached Files

  6. #6
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Okie,.... well there are 1000 non N/A records, 5159 N/A records and 1 null,.... how many non N/A records are you getting loaded in your db table?? 6159 or 5159?

  7. #7
    Join Date
    Nov 2003
    Posts
    5
    Hi,

    That was pretty fast. I am getting loaded with 6159 NULLs.

    Regards,
    Sathish

  8. #8
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    using the dts wizard to import the spreadsheet to a temp ABC transport table and using the following code....

    '************************************************* *********************
    ' Visual Basic Transformation Script
    ' Copy each source column to the
    ' destination column
    '************************************************* ***********************

    Function Main()
    DTSDestination("Renewal Month") = DTSSource("Renewal Month")
    If Trim(UCase(DTSSource("ABC transport"))) = "N/A" Then
    DTSDestination("ABC transport") = NULL
    Else
    DTSDestination("ABC transport") = DTSSource("ABC transport")
    End If
    Main = DTSTransformStat_OK
    End Function


    I get it with values where they are meant to be....

    Wanna try this as a test to try and locate your problem?

  9. #9
    Join Date
    Nov 2003
    Posts
    5
    Not much luck. Still its not working for me. Let me try for some time to see whether I can get it.

    Thank you very much for the help.

    Regards,
    Sathish

  10. #10
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    No worries. Let me know if you need more help, sorry I couldn't solve your problem.

  11. #11
    Join Date
    Feb 2002
    Posts
    2,232
    The problem is with the excel driver - the excel driver attempts to guess at the datatype based on the first X number of entries. Save the excel file as a csv file and do the same dts script using the csv file and it will work.

  12. #12
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    By if he is using VBScript to do the transformation then it would be using it's typing (eg. everything is a string) to do the comparison....

    Also, wouldn't that also mean that my test transformation wouldn't work where it did work?

Posting Permissions

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