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.



No comments:

Post a Comment