Error Importing Flat File CSV

Question

Error Importing Flat File CSV

binford79

Old Hand

Points: 313
More actions
January 5, 2009 at 3:40 pm

#133472

Hello guys, i´ve been searching the whole google for this problem, but i´m still missing a solution....anyway this is the problem hope anyone can help me in the right direction:
I want to import a small csv file, wich contains max 10 columns separated by (;). I this case i only try to test an import of the first column --> "Name" and than through a Lookup to send the data to the destination "SQL DB".
In the csv file the column "Name" has data like "Tom-Taylor, McDonald", so first and last name as a string separated by an (,). Maybe this could be the reason to the reported error?
Here comes the Log Data:
[Source Flat File [1]] Error: Data conversion failed. The data conversion for column ""Name"" returned status
[value 2 and status text "The value could not be converted because of a potential loss of data.".
[Source Flat File [1]] Error: The "output column ""Name"" (542)" failed because error code 0xC0209084 occurred,
and the error row disposition on "output column ""Name"" (542)" specifies failure on error. An error occurred on the
specified object of the specified component.
[Source Flat File [1]] Error: An error occurred while processing file "C:\Data\MSSQL\ETL\CSV\01_2006.csv" on data row 2.
[DTS.Pipeline] Error: The PrimeOutput method on component "Source Flat File" (1) 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.
[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.
[DTS.Pipeline] Error: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown,
or an error in another thread is causing the pipeline to shutdown.
[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0047039.
From other sources i found out that this problem can be resolved by the comulativ update 5 for sql 2005. But i can´t believe that sql 2005 is with sp2 is unable to import such a source data.....!!
Please help....
Thx.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246962 More actions · Answer 1

So your field delimiter is a semicolon - sorry if this is obvious, but did you define this as your field delimiter in SSIS?

Can you post a couple of lines of sample data?

Phil

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Eric SSCommitted Points: 1803 More actions · Answer 2

In the flat file source, is the output column width large enough for the row that is causing the failure?

Open the connection manager wizard for your flat file connection, go to advanced and check that the data type and OutputColumnWidth for each column is correct (matches your file spec or corresponds with the data typing of your destination).

Eric SSCommitted Points: 1803 More actions · Answer 3

While you are debugging you might want to set the flat file error outputs to redirect rows instead of fail on error. You can send them to a Row Count transform and put a data viewer on the error output to see which rows are failing.

binford79 Old Hand Points: 313 More actions · Answer 4

Hi,

thanks for answers, here comes the source data as a sample.

The Field Delimeter is set to = Row Delimeter ({CR}{LF}), Column Delimeter (Semicolon {;})

"Name";"Date";"Month";"Year";"Client";"Project";"In";"Out";"Description"

"Mc-Taylor, Tom";"9.1.2006";"Jan06";"2006";"Company-West";"Testing";"8,5";"2,75";"Testing the Source Environment"

"Ruiz-Garcia, John";"12.1.2006";"Jan06";"2006";"Company-West";"Testing";"8,0";"4,75";"Testing the Source Environment"

"Craig, Larbi";"23.1.2006";"Jan06";"2006";"Company-West";"Design";"8,0";"8,0";"Getting startet - Live Console"

I´ve checked in the Connection Manager that the type and OutputColumnWidth for each column is correct!

I´ll now install SP3.....what else?

I´ll be back!

binford79 Old Hand Points: 313 More actions · Answer 5

Ok the reason for this problem suprised me really, x64 SQL Integration Services is just unable to import a small csv flat file - on ther contrary x32 SQL IS imports the file without any problems!!

Amazing for me, but has anyone experienced this behavior too?

Thx.

Alan. T. Hall of Fame Points: 3221 More actions · Answer 6

Yes we are running x64 in a as400 shop and used to used tto and csv files to do all of the transfers from the as400. I never got the csv importer to work correctly and still have an open ticket with MS to see if they are going to fix it. If you search the MS forums you will see a lot of bad answers. Mostly involving coding loaders.

My solution was to switch to the ADO.NET driver for the as400 and read directly from the files.

For a third party I did get a loader for the csv to work as long asthe format is exact but I don't trust it.

binford79 Old Hand Points: 313 More actions · Answer 7

WOW what a shame on SQL 64bit! then back to 32bit....

If you get an Resolution from MS, you are wolcome to inform us !!

thx.

COZYROC One Orange Chip Points: 28499 More actions · Answer 8

binford79 (1/7/2009)
WOW what a shame on SQL 64bit! then back to 32bit....
If you get an Resolution from MS, you are wolcome to inform us !!
thx.

Do not expect resolution based on current Flat File Source / Connection Manager any time soon. It is based on the same old MS JET technology, which is 32bit.

You may check the Delimited File Reader sample.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/