Problem: Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "NAME" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)
Solution: It can be awfully difficult to import data from simple text files (CSV) into an SQL Server Table through a Database Import Wizard. Challenges include the format of data in the text file, not all data was made the same, there could be an extra delimiter in the rows, column properties could be different. However, finding and fixing the errors in a dataset is another challenge on its own.
- The easiest way to import data into an SQL Server Table is by using an Access Database as an ETL, basically, follow the instruction below:
1. Create a Database in Microsoft Access
2. Create an External Datasource (a link to SQL Server Database through an ODBC, could be remote or local)
- In Access Database: "External Data" => "New Data Source (click on a small arrow pointing down)" => "From Database "=>"From SQL Server" => "Link to the data source by creating a linked table"
[NB] You will have to create a connection to the database if you are connecting to the remote server by using an ODBC connector (Basically the ODBC acts as your connection string).
3. After you create a linked table in Access Database, it's time to connect a text file as a data source as well. Follow the instruction as creating a linked table from a Database.
4. After the Database (Destination Source) has been linked as well as the Text file as a Data source as been linked then create an Append Query to Select data from the Text file and Insert into a Table in the Database you linked earlier.