Enhancements to the Flat File Connection Manager

You can extract and load data from a flat text file using Integration Services. In this version of Integration Services the Flat File Connection Manager now supports the ability to parse files with embedded qualifiers. If you have upgraded a SQL Server 2000 DTS package to a newer version of Integration Services and used flat files, you may understand what a benefit this new feature is, as it has caused a lot of development headaches in the past.

Embedded qualifiers surround text fields, as you can see from the following sample row mixing text and data:

'Michael', 'Rourke', 37, 'Bristol', 104

However, we may have a row containing a name that includes an apostrophe, such as:

'Michael', 'O'Rourke', 37, 'Bristol', 104

In this case the apostrophe is usually doubled up as follows:

'Michael', 'O''Rourke', 37, 'Bristol', 104

Previously, SSIS could not handle this scenario and the file would fail to parse. In SSIS 2012, this problem has been fixed and you...