March 8, 2011 at 10:47 am
I wrote a package that is rather straight forward. It reads a flat file and places it in a table. I am having a problem in that some of data is formated bad. The file is comma delimited with Double qoutes (") around texts. Well one of the texts has two sets of double qoutes in it. I have tried redirecting error rows to a flat file and this would be acceptable. I continue to get an error the packge fails. Any ideas of how to get the package to reject the offending row and move on?
The error message is
[Input File [1]] Error: The column delimiter for column ""NABP"" was not found.
and
[Input File [1]] Error: An error occurred while processing file "C:\MSSQL\Packages\Input\ImportBillingData\RawFolder\Input Monthly Hospice" on data row 1636.
March 9, 2011 at 12:48 pm
I shall refer you to the following topic that discusses the same problem, but it unfortunately doesn't offer any direct solutions:
http://qa.sqlservercentral.com/Forums/Topic638571-148-1.aspx
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 9, 2011 at 3:28 pm
I have used a script object to clean up input files before the import.
read the source file, evaluate the line, then output to a new file.
or
ignore the "" qualifier on import.
use derived columns to strip out the double quotes using the replace function.
Usually I can push back on the provider of the data to clean it up before sending it to me
March 9, 2011 at 11:52 pm
nheller (3/9/2011)
ignore the "" qualifier on import.use derived columns to strip out the double quotes using the replace function.
What if there is a string with a comma in it? Won't that mess up your columns?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 10, 2011 at 6:10 am
This appears to be a problem with SSIS not using escape characters as other parts of the Windows Universe. Any one agree?
March 10, 2011 at 6:19 am
SW_Lindsay (3/10/2011)
This appears to be a problem with SSIS not using escape characters as other parts of the Windows Universe. Any one agree?
Or a problem with people not choosing a decent delimiter 😉
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply