Question from Beginner DTSX person about Transforms

  • I would like to change different ways to put in United States to one thing buy a fuzzy look up or something like that. I have the following values in the DB. U.S.A UnitedStates, UniteStates, U.S. US, USA. I would like to know how I would create a DTSX transformation that would take a list of items and change those values listed above to UNITED STATES. I'm assuming Fuzzy Lookup is not the correct way to go. Would like some help figureing this out.

  • You could use a Derived Column task in the data flow and write a nested Conditional IF statement. I do not have acceess to SSIS right now so posting a link which will help out:

    http://www.sqlchick.com/entries/2011/6/8/nested-conditional-operators-in-an-ssis-derived-column.html

  • You would :

    1. Add data flow

    Within the Data flow

    1. Add a Data Flow with the SQL statement pulling in the data from the database table

    2. Add a Derived Column Task

    3. Add a column with a formula similar to:

    FieldName == "U.S.A" || FieldName == " UnitedStates" || FieldName == "UniteStates" || FieldName == "U.S." || FieldName == "US" || FieldName == "USA." ? "UNITED STATES" : "UNITED STATES"

    4. Connect the Derived Column task to and Destination Task

  • I'll Give that a shot, thank you both for the information

  • Hope you get it working

  • Alternative and easier to maintain:

    Add a SQL table with two columns, one column for the value you want, and one column for the alternate value.

    Add a row for each of the alternate values.

    Example:

    UNITED STATESUS

    UNITED STATESU.S.

    UNITED STATESU.S.A.

    CanadaCAN

    Add a lookup in your data flow, and compare your data values with the alternate values, insert the wanted value into your flow.

    This way you can add additional countries and or alternates without changing your package.

Viewing 6 posts - 1 through 5 (of 5 total)

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