Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
Now, drag “fuzzy lookup” transformation task below your “Ado Net Source” and connect extended green arrow from “Ado Net Source” to your fuzzy lookup. Double click on “Fuzzy Lookup” task to configure it.
Click on “Columns” tab to configure which column to check with reference from source table and select “firstName” and “lastName” column and connect it so that our fuzzy lookup task will compare these two fields from source to reference table.
Once you configure “columns”, you have to click on “Advanced” tab, you can set “Similarity Threshold” which will give you how much identical both fields are…. If it is 1 than it is perfect match, if it is 0 than no match or data not present in reference table so more near to 1, good match it is. We are not going to take any decision like if it is greater than .50 then do this otherwise do that so it would be ok if you don’t change “Similarity Threshold”.
Now, drag “SQL Server Destination” task so that this matched and unmatched data could fall in SQL Server table, though we have not created any SQL Server table for this so far. Connect green extended arrow from “Fuzzy Lookup” transformation task to “SQL Server Destination” task. Before we configure “SQL Server Destination” we would like to do one more thing. Double click on GREEN arrow between Fuzzy Lookup task and SQL Server destination task.
Now, double click on “SQL Server Destination” task to configure it. Give details of your SQL Server and database into “Connection Manager” name. since we don’t have destination table already created for our data, we are going to click on “New” button besides “Use a table or view” property which will create one destination table in our SQL Server.
Now you are ready to run your package, hit F5 to run it, when it crosses Fuzzy Lookup Task, it will show you data in grid, check it and click on “Green Arrow” above the grid in same dialog box so that data falls into our SQL Server table.
you can later on check the same data into SQL Server by executing TSQL Query, while generating new table in SQL Server, if you didn’t have rename the table, it would be by default [SQL Server Destination]. So you can execute something like
Once you are done with configuration of “Ado Net Source”, drag “Conditional Split Transformation” task from tool box to your work area and drag green arrow coming from “Ado Net Source” to your “Conditional Split Transformation” to connect “Conditional Split Transformation” with “Ado Net Source”.
Now, we are all set to split data into three different table from our “SampleReceive” table. Have three “Ado Net Destination” on the screen and rename it to “VOA”, “Semi-Voa”, “Pesticide” and connect all three “Ado Net Destination” from “Conditional Split Transformation” task. When you will try to connect that green arrow from “Conditional Split Transformation” to “VOA” Ado Net destination, it will ask you for select condition, you have to select “Voa” condition which we have already defined in “Conditional Split Transformation” task. Look at screen capture below for more information.
Once you connect it (all three destinations), You have to configure all three “Ado Net Destination” . I will show you how to do it for first one, for remaining two; you can follow the same procedure.
Once you will configure “VOA” table, configure all remaining two “Ado Net Destination” for respected table. Finally your diagram will looks like below image.
Now, you are ready to run, hit F5 to run your package and check whether you have really received data in all three blank table or not by running following TSQL commands in your SQL Server database from SSMS.