[This is just for sharing information for those common asked questions collected from forums. If you have any better way or feedback, please directly reply in this thread so that community users can benefit from your answers.] Question: I want to load data incrementally in SSIS. It looks like the Lookup transformation component can help. How can I do this? Answer: The Lookup transformation component will help for this scenario. You can refer to the following steps: 1. Create a test table and add some test data. CREATE TABLE dbo.SourceTable (ProductID int NOT NULL ,Name varchar(10) NULL) Go CREATE TABLE dbo.DesTable (ProductID int NOT NULL ,Name varchar(10) NULL) Go Insert into SourceTable(ProductID,Name) values(1,'a') Insert into SourceTable(ProductID,Name) values(2,'e') Insert into SourceTable(ProductID,Name) values(3,'c') Insert into DesTable(ProductID,Name) values(1,'a') Insert into DesTable(ProductID,Name) values(2,'b') 2. Drag a Data Flow task onto the control panel, and double-click the “Data Flow Task”. 3. From the toolbox, drag an OLE DB Source and connect to the table “SourceTable”. 4. Drag a Lookup transformation on to the data flow panel, and create a connection from the OLE DB Source to it. Double click the Lookup component to open the Property editor, choose “Ignore failures” for “specify how to handle rows with no matching entries”. In the Connection tab, choose the connection manager and select the table “DesTable”. Then click the Columns tab, map the columns “ProductID” between the SourceTable and the DesTable, and check all the checkboxes in the destination. 5. Modify the output alias like this:ProductID->De_ProductID and Name->De_Name. Click the “Error Output” tab, and select the “Ignore failure item” for error column. 6. Drag a Conditional Split, and connect the Lookup to it with matched output. Edit the Conditional Split, and add the following two outputs. NewRow: ISNULL(De_ProductID) Changes: (ISNULL(De_ProductID)==False) && (Name != De_Name) Note: For those records not found in the destination table, the column value of De_ProductID in the output table is NULL, which also means that these records in the source table are newly added. Else if the Name values in the source table do not match corresponding values in the destination table, it means these records in the source table are changed. If you have multiple columns, you may consider adding a timestamp column to save the update time automatically. 7. Now you can incrementally load data by inserting the new rows into your target table and update changed records also from the two outputs from the Conditional Split component. Drag a SQL Server Destination for inserting new data and an OLE DB Command for updating records to the panel. a. Connect the Conditional Split to the SQL Server Destination with “NewRow” output. Then connect the Conditional Split to the OLE DB Command with “Changes” output. b. Edit the SQL Server Destination, connect it to the table “DesTable”. c. Double click the OLE DB Command, set the Connection Manager column to your destination connection manager. d. Switch to the "Component Properties" tab, and input the following T-SQL clause to the SQLCommand field: UPDATE dbo.DesTable SET Name = ? WHERE ProductID = ? e. Click the Column Mappings tab, and you can find two columns automatically generated in the Available Destination Columns, Param_0 and Param_1. According to the parameters sequence showed in the T-SQL, you can connect Name to Param_0 and ProductID to Param_1. 8. Save and execute the package. Please remember to mark the replies as answers if they help and unmark them if they provide no help

I would be if it had screens....tyro's like me can understand easily. Anyways Good Work...

Need more explanation... Seems like there is some wrong in Point 6. we have to connect it to not matched output. I am a newbie...if i am wrong please explain. Refer http://sqlblog.com/blogs/andy_leonard/archive/2007/07/09/ssis-design-pattern-incremental-loads.aspx for screen and explanation.

No - in step 4 you configured the Lookup to ignore failures. Since you did so, there will be no rows sent to the "no match" output. Talk to me now on

gr8 work