Flat file connection manager doesn't work for rows containing "Embedded Qualifiers"
I came across an interesting issue related to Flat file connection manager which doesn't work for rows containing "Embedded Qualifiers". I have worked with two different partners and both were having the same issue so thought of sharing my experience with you all.
Scenario and Issue:
Consider that we have a source CSV file which contains a row with Embedded Qualifiers like row number 4 - {"002042","OBEE Blue ("ABC" prin)","", } in the below example, with text qualifiers for the Flat File Connection Manager set to '"' and irrespective of property AlwaysCheckForRowDelimiter been set to True (or) False, the package fails during execution.
Source CSV File content:
CODE,NAME_OF,DEFINITION
"002090","Grey",""
"002091","Grey, Red",""
"002092","White/Teal",""
"002042","OBEE Blue ("ABC" prin)",""
"002093","GPf Grey",""
"002094","BMWand Blue",""
Error:
[Flat File Source [2]] Error: The column delimiter for column "<ColumnName>" was not found.
[Flat File Source [2]] Error: An error occurred while processing file "<Filepath>" on data row 5.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Flat File Source returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
This same CSV file works flawlessly in SQL Server 2008 R2 Business Intelligence Development Studio (BIDS) environment but fails with the above error when using SQL Server Data Tools (SSDT) for SQL Server 2012 and above.
You can find the related or similar issues described in below URL.
https://connect.microsoft.com/SQLServer/feedback/details/560592/flat-file-connection-manager-not-handling-text-delimiters-in-csv-files https://connect.microsoft.com/SQLServer/feedback/details/282396/ssis-flat-file-parser-does-not-read-column-delimiters-embedded-in-text-data
You can find the description in detail with both working and non-working scenario below
Working Scenario: BIDS/ SQL Server 2008-2008R2
1. Create a Package and select Data Flow task.
2. Create a Flat File Connection Manager with data as similar mentioned above.
3. Create a OLE DB destination as below.
4. At the end Data Flow task will look like as below.
5. When you Execute the package, it will get executed successfully.
6. We’ll get the desired result in SQL Destination table.
Non-Working Scenario: SSDT/ SQL Server 2012 or above
1. Create a Package and select Data Flow task
2. Create a Flat File Connection Manager
3. Create an OLEDB destination as below.
4. At the end Data Flow task will look like as below.
5. When you Execute the package, it will fail with error [see screenshot]
So, this behavior is not a bug. This is by design behavior and we should follow the below changes in the flat file to fix it. I have used sample row for the demo purpose
Solution:
To use a specific character as text qualifier, e.g. “as text qualifier, we should use “” to represent the real “
So "OBEE Blue ("ABC" prin)" should be modified to "OBEE Blue (“"ABC”" prin)"
Starting from SQL Server 2012, the Flat file connection manager has been greatly improved to accommodate Embedded Qualifiers. Following articles talk about the Flat file connection manager improvements in detail.
https://www.microsoftpressstore.com/articles/article.aspx?p=2201315&seqNum=5 https://blogs.msdn.microsoft.com/mattm/2011/07/17/flat-file-source-changes-in-denali/
Hope this helps you as well.
Author: Vikas Kumar – Support Engineer, SQL Server BI Developer team, Microsoft
Reviewer: Chaitra Hegde – Support Engineer, SQL Server BI Developer team, Microsoft
Comments
- Anonymous
June 06, 2019
We are upgrading SSIS packages to 2017 from 2008r2, and stuck with this issue. We do not have control of source system sending in their feed files with text qualifiers - is there any alternate tip/trick to get thru this?