Friday, April 24, 2015

SSIS Flat File Text Showing Up in Quotes Although Column is Set to Qualified

Short Description:  An SSIS package using a flat file datasource with columns set as TextQualified = true come through showing the text qualifier (values are in quotes or some other qualifier) in the destination (database, another flatfile, etc).

Problem:  This is just a case of a two part setting being in two oddly different places.  A column is set to TextQualified under Connection Manager Editor, Advanced, Column Name, TextQualified (true).  The Text Qualifier itself is set under The file connection's properties.  Text pulled from a flatfile will still show up in quotes (or surrounded by any other qualifier) even though TextQualified=true has been set.

Solution:  In addition to enabling the TextQualified setting at a column level.  The qualifier has to be specified at the connection properties level.

To set the Text Qualifier:

  • In SQL Server Data Tools.
  • Right click the Connection Manager and select Properties
  • In the Properties pane on the right hand side, under Misc
  • Enter your Text Qualifier (e.g. ") in the TextQualifier property.



Wednesday, April 22, 2015

SSIS Code Page Error Moving Flat File to OLE DB destination



Short Description:  When creating an SSIS dataflow moving data from a flat file directly to an OLE DB destination, the OLE DB destination throws a code page inconsistency error.

Problem:  An OLE DB destination that is fed by a Flat File Source which is in turn fed by a Flat File connection throws an error "Error 2 Validation error. Stage Sent Data: Stage Sent Data: The column "MyColumn" cannot be processed because more than one code page (65001 and 1252) are specified for it."  SQL server is by default Code Page 1252, and Unicode is 65001 for a text file. if you don't put the output columns through a Data Conversion transform before they get to the OLE DB destination, they will fail with this error.

Work Around:  I say work around because changing the Code page of the flat file connection does not actually result in the Code page staying saved, but it does result in the OLE DB destination accepting the input.

To Work Around:

  • Open the Flat File Connection, and change the Code Page from '65001 (UTF-8)' to '1252 (ANSI - Latin I)'
  • Click OK to save
  • Open the OLE DB Destination which is showing the error.
  • Close the OLE DB Destination which is showing the error.
  • The OLE DB Destination should now, now longer show the error.
  • For kicks, look at the Flat File Connection, and see that the Code Page is back to '65001 (UTF-8)'