July 3, 2016 at 3:14 pm
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.
July 7, 2016 at 8:04 pm
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
July 7, 2016 at 8:15 pm
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
July 8, 2016 at 6:17 am
I'll Give that a shot, thank you both for the information
July 8, 2016 at 12:02 pm
Hope you get it working
July 8, 2016 at 2:08 pm
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