Home Dashboard Directory Help
Search

SQL 2008 R2 Flat File Provider still broken by ManServ


Status: 

Closed
 as Fixed Help for as Fixed


91
1
Sign in
to vote
Type: Suggestion
ID: 281398
Opened: 6/5/2007 1:55:42 AM
Access Restriction: Public
Duplicates: 239415 282396 354768
3
Workaround(s)
view

Description

The flat file provider is not able to import double qualified text / embedded quotes. Also see: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=239415 (Flat file provider should support double qualified text import) and https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=267529 (Embedded quotes in Flat File Import fails)
Details
Sign in to post a comment.
Posted by Jon Miller2 on 8/10/2012 at 4:52 PM
OK, so, what is the correct syntax to import an RFC 4180 compliant CSV file? I think this is still broken. Pretty pathetic if you ask me.
Posted by Microsoft on 3/18/2011 at 12:29 PM
This issue has been resolved in the Denali release, and not the 2008 R2 service pack.
Sorry for the confusion.

Matt Masson
SQL Server Integration Services
Posted by chiyancheung on 12/21/2010 at 8:10 AM
I can't find any service packs for R2. And I have confirmed that R2 is not working. This may be file specific, but in that case, is there a workaround for me to "fix" the file?
Posted by Joseph Leathlean on 5/28/2010 at 5:41 AM
Well - they are again saying it is fixed in the next 'Major' release...

https://connect.microsoft.com/SQLServer/feedback/details/560592/flat-file-connection-manager-not-handling-text-delimiters-in-csv-files#tabs
Posted by Joseph Leathlean on 5/20/2010 at 11:50 PM
SQL 2008 R2 RTM also still does not handle this correctly - submitted a new bug: https://connect.microsoft.com/SQLServer/feedback/details/560592/flat-file-connection-manager-not-handling-text-delimiters-in-csv-files

Please go and vote on it...
Posted by ManServ on 4/4/2010 at 1:33 PM
What is the next "major" release and its schedule? Customers have to pay for the "R2" release after SQL Server 2008. So it is "major"? The internal version number on the other hand is just +0.5.
Posted by Microsoft on 3/25/2010 at 12:41 PM
This request has been fixed for the next major release of SQL Server. Specifically, embedded qualifiers are supported with this fix.

Posted by ManServ on 8/11/2009 at 11:55 AM
SQL Server 2008 R2 August CTP Flat File Parser still isn't capable of importing RFC 4180 compliant csv files. Also Unicode supplementary characters and line breaks aren't supported with UTF8 files. (Flat File Parser cannot import files with embedded text qualifiers http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=312164)
Posted by ManServ on 9/12/2008 at 5:51 AM
I have looked at the components. Here are the results for the Delimited File Reader.

Pros:
RFC 4180 compliant (e.g. multi line fields and embedded text qualifiers)
supports nvarchar(max)
can skip data/header lines
speed isn't very good but acceptable. about 300 rows/(second * GHz) with my test system/data

Cons:
Cannot be used in Import/Export Wizard
Incompatible with SQL Server 2005
Supports Unicode only below version 3.1. SQL Server 2008 supports unicode 5.0. Non UCS2/BMP (Basic Multilingual Plane) characters crash the component regardless of what UTF you use: "System.ArgumentException: The output char buffer is too small to contain the decoded characters, encoding 'Unicode (UTF-16)' fallback 'System.Text.DecoderReplacementFallback'."
The "IsUnicode" Custom property is misleading and means "IsUCS2LE". So especially don't use it with UTF8. Only set it to true with UCS2LE. It is also not needed because of the codepage property.

Altogether the component is very useful if you don't need full Unicode support. Maybe this will be fixed?

Not yet tested:
x64 compatibility

Hints:
Adjust the DefaultBufferMaxRows/DefaultBufferSize values for optimized performance. I used 1000/2048576 (Improving the Performance of the Data Flow: http://msdn.microsoft.com/en-us/library/ms141031.aspx)
Use a Foreach Loop Container and a Derived Column to process multiple files and log their names to the output
Be careful when using SQL Server and OLE DB destinations. By default they use bulk insert. This requires the bulk import priviledge and may causes troubles with tables that have constraints/triggers/defaults/computed columns. If you encounter the following error first try to set the destinations Timeout property to 0 (infinite) or use an OLE DB destination _WITHOUT_ the fast load option:
--
Error: 0xC0202009 at Data Flow Task, SQL Server Destination [22]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "The Bulk Insert operation of SQL Server Destination has timed out. Please consider increasing the value of Timeout property on the SQL Server Destination in the dataflow.".
--

The RegExFlatFile doesn't seem to support Unicode at all because it uses DT_string. Why?
Posted by Microsoft on 8/26/2008 at 3:58 PM
We are looking into fixing this for the next major release following SQL Server 2008. In the meanwhile, we have created two samples on http://www.codeplex.com/SQLSrvIntegrationSrv that may be helpful: RegExFlatFileSource and DelimitedFileReader.

- Matt Carroll
Posted by ManServ on 8/14/2008 at 3:32 PM
Still broken in 2008 RTM and active...
Posted by Microsoft on 3/6/2008 at 1:28 PM
Thank you for your feedback and support of the SSIS product. This issue doesn’t currently meet the requirements for correction in SQL 2008. If you would like for us to consider this issue for correction after SQL 2008i RTM, please re-open or add your vote.

Again we appreciate your time and support.

-SSIS team
Posted by Microsoft on 12/5/2007 at 5:52 PM
Thanks for your feedback!

We are considering supporting embedded qualifiers in our flat file adapter for SQL Server release "Katmai. We'll keep you posted on the progress of this request.

Thanks,
SSIS Team.
Sign in to post a workaround.
Posted by cameron_eldridge on 9/28/2010 at 12:17 AM
I've been using the Jet driver with schema.ini as well. Unfortunately it doesn't handle large files (4.7 GB in this case). Which is usually the type of data you need to be importing from a flat course instead of a database connection......
Posted by Matt Carroll on 8/26/2008 at 4:01 PM
Take a look at RegExFlatFileSource and DelimitedFileReader samples on http://www.codeplex.com/SQLSrvIntegrationSrv. The sample sources may help solve your text parsing needs.
Posted by ManServ on 6/5/2007 at 1:59 AM
ODBC Text driver with a schema.ini, but it's bloody slow and circuitous to handle.