In some cases, if you have Unicode data in the Memo field, those records will cause a problem in the package execution. Consider the following message
Dealing with the company from May 31 – BKW
In the above statement there is one Unicode character box that is created in the access system because of the enter key. As a test, I copy this line into our Microsoft Access database.
You can see the result of this copy step in the second record.
Save the Access table and close Access. Then run the Package application to import data.
You will have an error while transferring this kind of data. As a result, no record will be copied to a SQL Server database.
You can view the error message by clicking the Progress tab (Last One).
There you can see the Data Conversion Error.
We can avoid this situation. Suppose you are transferring millions of bytes of data and there are errors somewhere in the Access database. Then finding the error fields would be very difficult. The solution is to create an exception which allows us to transmit any error record to a temporary table while the other records are transmitted to the SQL Server database.
- Now click the data flow tab
- Click the Stop Debugging button from the tool bar
- Now find the Script Component in the Left side toolbox.
- Now drag the Script Component to the screen.
- Then the “Select Script Component Type” window will be displayed.
- Make sure “Transformation” Option button is selected.
- Click Ok
- Now you have a window like this.
- Now click the Data Conversion 1 box.
- You can see there is a red color line.
- Drag the red color line and connect to the Script Component box.
- When you successfully connect to the Script component then you will have the configuration Error Output window.
- We know that there is a problem in the Remarks column. So set the options like this.
- Now when the error occurs, the row will be redirected to the script section.
- Click Ok.
- Now your package looks like
Adding a Script >