Post reply

SSIS alter import data

  • September 1, 2009 at 8:23 am

    #134657

    Hello All,

    I am trying to find the correct way to alter data that comes in from a CSV and imports to SQL2K5.

    I have a field in the export that is a string value. Some examples of data in the column are REVENUE, EXPENSE, STATISTIC etc...

    When I import these fields to SQL2K5 I would prefer to enumerate the data as 0, 1, 2, 3 etc. What is the proper way to go about altering the data. I've briefly peeked at Script Transform and Derived Column.

    Thanks,

    J

  • September 1, 2009 at 8:36 am

    #1047574

    A Derived Column or a Script Component would work fine.

    You could also create a table in your database that stores these text values with a numeric id and use a Lookup.

  • September 1, 2009 at 9:10 am

    #1047607

    Erik Kutzler (9/1/2009)

    A Derived Column or a Script Component would work fine.

    You could also create a table in your database that stores these text values with a numeric id and use a Lookup.

    Option (2) is the way I would go - then you kill 2 birds with one stone - you have also built your lookup table for post-import pure-SQL queries.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply