satish: 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

satish