In this article, we will discuss use of lookup transformation, benefits of having different types of panels and management of data in source and destination tables to achieve results incrementally.
Loading your data from data sources in a proper incremental manner can be a hurdle. We can this problem by using lookup transformation component which is useful in loading your data in an increment order. This component performs lookups by joining data in reference data set with the data in input columns. Lookup transformation can also be used to fetch additional information in table whose values are in common columns. Lookup transformation component gets one input and it can return one of its three different type of output that are error outputs, matched outputs, no match outputs.
Using following steps, we can make use of lookup transformation to load our data in an incremental order.
Source and Destination tables
First, we need to create a test table and populate it with some data.
--Source Table CREATE TABLE user.table1 (EmpName varchar(30) NULL, EmpID int NOT NULL) Go --Destination Table CREATE TABLE user.table2 (EmpName varchar(30 NULL, EmpID int NOT NULL) Go Insert into table1(EmpName,EmpID) values ('Jack',10) Insert into table1(EmpName,EmpID) values ('John',20) Insert into table1(EmpName,EmpID) values ('Harris',30) Insert into table2(EmpName,EmpID) values ('Jack',10) Insert into table2(EmpName,EmpID) values ('Gibbs',20)
Data Flow Task
Now we need to drag to control panel the data flow task and then double click on this task. Using the toolbox, a source should be dragged from object linking and embedding database (OLE DB) and connected to the source table which is “table1”. Next, we need to populate data flow panel using lookup transformation.
Another connection is needed to be created from OLE DB source to the data flow panel. Next, we need to open “Property Editor” which can be opened by clicking on lookup component. Now there can be rows in which entries are not matched, for managing and handling this issue we need to choose “ignore failure” option right next to it.
Next step is to select destination table ‘table2’ from the connection tab. After selecting table click on the column tab and map the column EmpID between the source table ‘table1’ and the destination table ‘table2’. Make sure all the checkboxes are checked in destination.
Modification of Alias
Now we need to modify the output alias which can be done like this
EmpName => De_EmpName
EmpID => De_EmpID
For handling column error problem just click on error output and select ‘ignore failure’.
After we have modified the alias, the conditional split needs to be dragged and connected to the lookup with matched outputs. Now just edit the conditional split by adding following two lines.
Changes: (EmpName!=De_EmpName)&&(Isnull(De_EmpID)==FALSE) NewRow: (Isnull(De_EmpID)
Column values of De_EmpID in output table is NULL for the records which cannot be found in table2 (Destination table) which means our table1 (Source table) is recently populated with the records. Now if EmpName value in source tables doesn’t match up to the corresponding values in the table2 (Destination table) then it simply means that source table records have been changed. If you got several columns then it is better to automatically save time update by adding column related to the timestamp.
Finally we can load our data incrementally by adding new records in our target/destination table. Also the changed and updated record will be loaded incrementally thanks to variety of outputs of conditional split. To update new record to panel in future, we just need to drag destination of SQL Server to insert new data.
In the end just execute to check and then save the package.
Recent Data Recovery
Incase if your database is crashed and you want to recover your recent data, you can use some specialized tool to recover SQL Server data for you effectively.
Upton Mark is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and word recovery software products. For more information visit www.datanumen.com