Scenario:
We are loading some data from flat file source to SQL server Table. There is possibility that the type of source data does not match with destination column data type. In those cases we want to ignore or redirect those records from destination and load the correct data.
Solution:
OLE DB Destination does provide the facility to redirect rows in case they can not be inserted due to truncation error, constraint issue etc. Use the below data in Flat File source.
ID,Name,Address
1,Aamir,XY Address
2,Raza,XX Address
test,Robert,344 Address
3,Williams, 123 streeet Address
4,Test,test address
5,Test name, test address 1
6,Test Name 2, Test Address 2
If you notice that instead of having number in ID ,I have put test. That means we can not insert this record in table with below definition as ID is int type.
CREATE TABLE [dbo].[Test]( [ID] [int] NOT NULL, [Name] [varchar](100) NULL, [Address] [varchar](100) NULL )
I have create an SSIS Package in SSIS 2012 and SSIS 2008. The Data Flow in both versions has Flat File Source and OLE DB Destination. I was just checking the behavior of both are same or not without implement redirection records. Both are create with default value. I did not make any change to any property.
Fig 1: Data Flow Task with Flat File Source and OLE DB destination in SSIS 2008
Fig 2: Data Flow Task with Flat File Source and OLE DB Destination in SSIS 2012
Let's run the SSIS the Package that we have created in SSIS 2008.
Fig 3: SSIS 2008 Data Flow Task without Redirecting Rows from OLE DB Destination
As we can see that it read 7 rows and then failed. Let's check the destination table and see if it loaded any records or all failed to load.
Fig 4: Table Loaded by SSIS 2008 without Redirecting Rows
We can see that the two top records before the error are loaded successfully to the test table. Let's truncate destination table and try to load data to this table by using SSIS package that we created in SSIS 2012.
Fig 5: Table Loaded by SSIS 2012 without Redirecting Rows from OLE DB Destination
Let's check if any rows are loaded in table.
Fig 6: Record Count after Execution of SSIS 2012 Package
As we see that that there was no record loaded into table when we used SSIS 2012 Package.
Note:
When you design SSIS Package in 2008 or SSIS 2012, keep this behavior in mind so can take care of it properly.
Configure Redirect Rows From OLE DB Destination
Now let's configure the OLE DB Destination to redirect any rows those can not be inserted due to any reason.
Double Click on OLE DB Destination and then configure as shown in below fig.
Fig 7: Configure OLE DB Destination for Redirect Rows
After configuring OLE DB Destination, You can write the redirect rows to any destination. For Test Purpose I have redirected them to Multicast Transformation.
Fig 8: Redirect Rows from OLE DB Destination in SSIS Package
As we can see that total 7 records are read from source and 6 are loaded in destination table and one record is redirected as it can be not loaded into table because ID is int type and data coming in is varchar.
One More Test Case
Let's make some changes in our destination table and consider that the Name column is unique. I dropped the table and created by using below DDL
CREATE TABLE [dbo].[Test]( [ID] [int] NOT NULL, [Name] [varchar](100) NULL, [Address] [varchar](100) NULL, CONSTRAINT [C_Name] UNIQUE(Name))
Also I made changes in Sources data. You can see the changes in Red, I have duplicate Name now in two records.
ID,Name,Address
1,Aamir,XY Address
2,Raza,XX Address
test,Robert,344 Address
3,Williams, 123 streeet Address
4,Test,test address
5,Test name, test address 1
6,Raza, Test Address 2
Now Let's run the same SSIS Package and see what happen?
Fig 9: Redirect Rows from OLE DB Destination with Unique Constraint Violation
We can see that all the rows are redirected. so what we learnt from this
If the row data can not be inserted due to truncation or overflow then It will be redirect only that row. But if It is Constraint Violation such as Unique, Primary key etc. then it will redirect entire batch.
To handle this type of situation , we have to decrease the batch size to 1. This can make the load slow. It is better to validate data before loading into destination and redirect from there instead of redirecting from Destination.
Fig 10: Set Maximum Insert Commit Size to 1 in OLE DB Destination for Redirecting Rows
Let's run our SSIS Package now and see if all working as expected. It should only redirect two rows , one with bad data and other due to Unique Constraint.
Fig 11: SSIS Package Redirecting Rows from Destination Transformation
While you develop your SSIS Package, Keep in mind if you are redirecting your rows from destination just because of overflow or due to constraints. For both scenarios the behavior is different.
I feel SSIS is the best and most useful component of solving extreme complex operations and finding out solutions to some very easy and complex operations.
ReplyDeleteSSIS Postgresql Read
I am really thankful to you Deadpool And Wolverine Ryan Reynolds Embroidered Shirt
ReplyDeleteEmbark Descendants The Rise Of Red 2024 Malia Baker Blue Jacket
ReplyDeleteThese Trench leather coats are a stylish and versatile outerwear option that combines the classic design of a trench coat with the durability and luxury of leather.
ReplyDelete