SQL Server Integration Services - Error handling for truncation error

In this post ,I am going to explain about handling truncation error,while importing data from CSV file to table in database.

Step 1:

From the below link,you can download sample test data(CSV file)

Step 2: create new SSIS project

Step 3: drag & drop  Data Flow Task on to the  Control Flow tab as shown in below image.

Step 4: right click & edit  Data Flow Task,you will move into Data Flow tab.

Step 5: Under Connection Managers,right click to add 

1.)Connection Manager for OLE DB Destination ,pointing to your database.
2.) Connection Manager for Flat File Source ,pointing to your CSV file.

Step 6:  drag & drop Flat File Source & OLE DB Destination on to Data Flow tab ,
Right click and edit them ,such that they are mapped with their respective Connection Managers.

Click  New.. & create target table in OLE DB Destination Editor 
and then check mappings  as shown in below image.


Step 7 : Execute the package, you will get truncation error on Column Web,because data on CSV file exceeds the size of Web column in target table.

Step 8: right click on Flat File Source -> Show Advanced Editor

Goto the settings as shown below in image,and change ErrorRowDisposition & TruncationRowDisposition to RD_RedirectRow

which means ,we are redirecting truncated(error) rows ,we can capture the same using OLE DB Destination /Flat File Destination.

Step 9 :drag & drop another OLE DB Destination & create table for logging rows that were getting truncated.

While connecting  Flat File Source & OLE DB Destination(Error Log table),Configure Error Output as shown below:

Step 10 :
Query Error_log table to get the details of truncated rows as shown in below image

See Also:


No comments: