Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Errors importing file in SSIS when upgrading from 2005 to 2012 Expand / Collapse
Author
Message
Posted Thursday, May 9, 2013 8:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 5:39 AM
Points: 7, Visits: 257
We are migrating to 2012 and all is going well except for an SSIS issue.

We nightly download a financial file from an external provider. An SSIS package then loads this into a table using a flat file source. Nothing too sophisticated.

This works fine under 2005 but have hit a problem in 2012, getting the error:

[Source File [116]] Error: The column delimiter for column "Description 2" was not found.
[Source File [116]] Error: An error occurred while processing file
"\\xxx\yyy\ myfile.csv" on data row 41935.

On further investigation of the file we can see the row has a double text delimiter (“) inside the column separators (,) (just prior to AAA below).

"56371","03-DEC-2010","22-JUN-2010","03-DEC-2012","ABC","PQR","ZZSWAP",""AAA Frz 15Oct@5 % PPP 22/SEP/2011", “CREQWE"

Researching this, it appears there are changes in this area for 2012.
This is causing us big issues as we have no ability to avoid this in the source. The format is delimited, delimiter is comma and text qualifier double quote.

Anyone any ideas how we can modify the package so it handles this like 2005 please?




Post #1451163
Posted Thursday, May 9, 2013 9:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:38 AM
Points: 5,079, Visits: 11,863
How did 2005 handle it? Did it ignore one of the two repeated "s?

If it did, sounds like a bug to me.

I just had a quick look - if you set the 'Text Qualifier' property to <empty string>, SSIS can at least parse the columns correctly (though of course the " characters remain).

You could subsequently do a Replace in your data flow to get rid of them.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1451204
Posted Friday, May 10, 2013 1:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 5:39 AM
Points: 7, Visits: 257
SQL 2005 treated the extra " as a normal character so " appears in the database column

Post #1451462
Posted Friday, May 10, 2013 2:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:38 AM
Points: 5,079, Visits: 11,863
How big is the file, roughly?

One option would be to pre-process it - maybe use s script task to replace "" with ".

If the file is huge, it might make sense to do it a different way though.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1451464
Posted Friday, May 10, 2013 7:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 5:39 AM
Points: 7, Visits: 257
That's the conclusion we're come to also!
Post #1451579
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse