SSIS Increment Load using Conditional Split Transformation
We have followed some steps:
Step 1: Select Data Flow Task onto Control Flow Tab.
Step 2: Drag and Drop the OLEDB Source
Step 3: Configure the OLEDB Source Connection
Step 4: Drag and drop a Lookup Transformation from the toolbox
Step 5: Add the text “Dest” to each column's Output Alias. These rows are being appended to the data flow pipeline.
Step 6: Next we need to modify our Lookup transformation behavior. By default, the Lookup operates as an INNER JOIN – but we need a LEFT (OUTER) JOIN. Click the “”Configure Error Output” button to open the “Configure Error Output” screen.
On the “Lookup Output” row, change the Error column from “Fail component” to “Ignore failure”. This tells the Lookup transformation “If you don't find an INNER JOIN match in the destination table for the Source table's CompanyId value, don't fail. “ - which also effectively tells the Lookup “Dont act like an INNER JOIN, behave like a LEFT JOIN”:
Step 7: Click OK to complete the Lookup tranformation configuration
Step 8: From the toolbox, drag and drop a Conditional Split Transformation onto the Data Flow canvas. Connect the Lookup to the Conditional Split as shown. Right click the Conditional Split and click Edit to open the Conditional Split Editor:
Step 9: Next, drag Dest_CompanyId from the columns frolder and drop it onto the “ISNULL(O_CompanyId)” text in the Condition column. “New Rows” should now be defined by the condtion “ISNULL (O_CompanyId)”. This defines the WHERE clause for new rows – setting it to “WHERE O_CompanyId is NULL,
Type “Changed Rows” into a second Output Name column. Add the expression “CompanyId ! = O_CompanyId || CompanyName != O_CompanyName” to the condition column for the Changed Rows output.
Step 10: Add OLEDB Destination and OLEDB Command transformation.
UPDATE [DimCompany_dest]
SET [CompanyName] = ?
,[Location] = ?
WHERE [CompanyId] = ?
In our reference screen shots:
Do you want more information click this link
http://sqlblog.com/blogs/andy_leonard/archive/2007/07/09/ssis-design-pattern-incremental-loads.aspx
We have followed some steps:
Step 1: Select Data Flow Task onto Control Flow Tab.
Step 2: Drag and Drop the OLEDB Source
Step 3: Configure the OLEDB Source Connection
Step 4: Drag and drop a Lookup Transformation from the toolbox
Step 5: Add the text “Dest” to each column's Output Alias. These rows are being appended to the data flow pipeline.
Step 6: Next we need to modify our Lookup transformation behavior. By default, the Lookup operates as an INNER JOIN – but we need a LEFT (OUTER) JOIN. Click the “”Configure Error Output” button to open the “Configure Error Output” screen.
On the “Lookup Output” row, change the Error column from “Fail component” to “Ignore failure”. This tells the Lookup transformation “If you don't find an INNER JOIN match in the destination table for the Source table's CompanyId value, don't fail. “ - which also effectively tells the Lookup “Dont act like an INNER JOIN, behave like a LEFT JOIN”:
Step 7: Click OK to complete the Lookup tranformation configuration
Step 8: From the toolbox, drag and drop a Conditional Split Transformation onto the Data Flow canvas. Connect the Lookup to the Conditional Split as shown. Right click the Conditional Split and click Edit to open the Conditional Split Editor:
Step 9: Next, drag Dest_CompanyId from the columns frolder and drop it onto the “ISNULL(O_CompanyId)” text in the Condition column. “New Rows” should now be defined by the condtion “ISNULL (O_CompanyId)”. This defines the WHERE clause for new rows – setting it to “WHERE O_CompanyId is NULL,
Type “Changed Rows” into a second Output Name column. Add the expression “CompanyId ! = O_CompanyId || CompanyName != O_CompanyName” to the condition column for the Changed Rows output.
Step 10: Add OLEDB Destination and OLEDB Command transformation.
UPDATE [DimCompany_dest]
SET [CompanyName] = ?
,[Location] = ?
WHERE [CompanyId] = ?
In our reference screen shots:
Do you want more information click this link
http://sqlblog.com/blogs/andy_leonard/archive/2007/07/09/ssis-design-pattern-incremental-loads.aspx
