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.
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
No comments:
Post a Comment