SSIS flat file source sql destination error

0

I have a csv file that has several fields, all of which are showing as:

Datatype - string [DT_STR]
OutputColumnWidth - 50

The mapping to my output SQL table is for fields that are all varchar(50).

I'm on SQL Server 2012 and I'm using the SQL Server Native Client 11.0.

I'm getting the following truncation error:

Messages
Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "Description" 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)

Error 0xc020902a: Data Flow Task 1: The "Source - myfilename_CSV.Outputs[Flat File Source Output].Columns[Description]" failed because truncation occurred, and the truncation row disposition on "Source - myfilename_CSV.Outputs[Flat File Source Output].Columns[Description]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
 (SQL Server Import and Export Wizard)

How do I resolve this?

What I have tried is to set that column to in my source:

Datatype - string [DT_STR]
OutputColumnWidth - 200

And on my destination sql table I set it to varchar(200).

After I do that several other columns trigger the same error and I change the width on the source and destination as described above.

The package still fails. Why isn't this working?

I've tried on SQL server 2012 and 2015. I've used both the import wizard and and actual ssis package.

sql-server
ssis
ssis-2012
asked on Stack Overflow Aug 2, 2018 by mo_maat

1 Answer

0

As you are importing a flat file, there is no metadata to tell SSIS what data types are held within the file as you would get if you were importing from a database.

As such, you need to manually set your data types to match the data you are looking to import and build your table accordingly.

If, for example, your flat file contains:

  • Col1 - String - Max length in file of 246 characters
  • Col2 - Date - In format yyyy-mm-dd
  • Col3 - String - Max length in file of 62 characters

You would need to set your connection manager to import as follows:

  • Col1 - string [DT_STR] - Length at least 246
  • Col2 - Date [DT_DATE]
  • Col3 - string [DT_STR] - Length at least 62

You can't just take guesses at your data types or rely on SSIS to get them correct. Actually understand your data and import it using the appropriate formats.

answered on Stack Overflow Aug 2, 2018 by iamdave

User contributions licensed under CC BY-SA 3.0