SQL Server Flat File Import Error

I am receiving the following error(s) while trying to import a "|"delimited flat file into sql server 2005:


Executing (Error)
Messages
Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "DATA_FIELDA" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
 (SQL Server Import and Export Wizard)
 
Error 0xc020902a: Data Flow Task: The "output column "DATA_FIELDA" (79)" failed because truncation occurred, and the truncation row disposition on "output column "DATA_FIELDA" (79)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
 (SQL Server Import and Export Wizard)
 
Error 0xc0202092: Data Flow Task: An error occurred while processing file "C:\DATA\DATA.txt" on data row 2.
 (SQL Server Import and Export Wizard)
 
Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "8_txt" (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.
 (SQL Server Import and Export Wizard)
 
Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038.
 (SQL Server Import and Export Wizard)
 
Error 0xc0047039: Data Flow Task: 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.
 (SQL Server Import and Export Wizard)
 
Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039.
 (SQL Server Import and Export Wizard)

ChiBellaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve HoggITCommented:
OK, post a sample of the file here for more help. But it looks like you are trying to import into an existing table - don't do that, way too much room for errors like above.
When you use the wizard, create a new table for insert. Then, choose to execute immediately and save to SSIS package. Post the package here if it does not work. You will have to change the package name from Package1.dtsx to Package1.dtsx.txt to upload.
0
ChiBellaAuthor Commented:
This looks like a known issue (bug). But I see no solution to it anywhere.

package is attached.
test2.dtsx.txt
0
ChiBellaAuthor Commented:
ok, I finally got it to load.

Not sure what the problem is/was. Apparently you have to change the destination sizes within the Advanced section.

0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Steve HoggITCommented:
Good. When you run the Wizard, it builds the table for you and creates a Data Conversion task which makes sure everything will load. It converts everything to NVARCHAR using th DT_WSTR data type.
If you modify any of the mappings then it is subject to fail, not always, but it is a risk.
0
Steve HoggITCommented:
When you are defining the file layout, it wants to default everything to string, 50 length. If you change these, it needs to be changed accurately or else it will fail. If you know certain columns are longer than 50, definately change those. I always find it easier to bring the file into the database and then change the column details - at least you can clearly see what the changes are and what errors occur trying to change them, if any.
0
ChiBellaAuthor Commented:
Well, it doesn't seem to get it right at all without modifying. I am now trying to import others and having same issues.

Is there a way to save the package and modify it to run with different input files?
0
Steve HoggITCommented:
You can save the package but if the files are different formats then it wont help. When you run the wizard, are you letting the wizard create the table without any changes? Are you adding length in the advanced tab to any columns over 50?
0
ChiBellaAuthor Commented:
Seeing all kinds of issues. Seems buggy to me.

If I save a package, can I go in and change the source Name...not field lengths? How do I just change the source name?
0
Steve HoggITCommented:
If the files are the same format, no problem here. You change the file name in the Connection Manager.
Open SSIS. From the menu, SQL Server 2005, Visual Studio Business Intelligence Development Studio. Create a new Integration Services project. Right click o Packages in the Solution Explorer and Add Existing Package. Using File System, go find it and select OK.
Double click on your package to open it up. You will then double click on the Connection Manager for SourceConnectionFlatFile. Picture attached. Change the file name here.

results.bmp
0
ChiBellaAuthor Commented:
OK, I think my data is dirty...causing these issues.
0
Steve HoggITCommented:
Can you send me a sample file? The reason I ask is this, you should still be able to import the data into NVARCHAR columns, even if it is mis-typed data.
0
ChiBellaAuthor Commented:
No, can't send sample due to the nature of the data.

I don't understand myself, everything is nvarchar, but I still get exceptions.

Also, whenever I try to rerun, I don't get the option to drop and re-create destination table...so I have to start all over again.

0
Steve HoggITCommented:
Ok, throw together a file with a couple rows of "fake" data that simulates what is in the file. Is the data in all the text files the same? Same format, number of columns?
I will build you a package to do the job - or die trying. :-)
I will be back in an hour or so. HZ
0
ChiBellaAuthor Commented:
thank you ....

I got it to work. Seems the data is really dirty. Also, I made the mistake of letting it figure out the format which changed some of the nvarchar's to numeric, int..and at various places contained text. Once I corrected that, the remaining issues were really the size at various places within the data and the fact that it would not let me recreate the table between iterations of fixing the field sizes.

I just deleted the table in-between iterations until I got all the correct sizes of the fields and then it worked.

Thanks for your input. What I need to do next is a transformation from this initial import to my database table. Can you do data transformations/conversions within a package?

You win the points here..should I open a new issue?

0
ChiBellaAuthor Commented:
Where are you going to do this?

Open SSIS. From the menu, SQL Server 2005, Visual Studio Business Intelligence Development Studio. Create a new Integration Services project. Right click o Packages in the Solution Explorer and Add Existing Package. Using File System, go find it and select OK.

I don't see this is SSIS?
0
Steve HoggITCommented:
Yes, SSIS. If you open BIDS, Visual Studio Business Intelligence Development Studio, the create a new project, type of Integration Services. That is SSIS. Do you have that available? What version of 2005 are your running?
In the Data Flow task you put a Data Conversion object inbetween the Source and Destination. Then you choose the Source columns, choose a name for the converted column and pick the new Data Type. If it is reasonable, it will work. You can configure error output to Ignore errors but you might miss some dirty data.
I normally do my Data conversions on trick / messy data, all in the database by changing the column data types, modifying data with Replace, Cast, Convert or whatever to clean it up.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ChiBellaAuthor Commented:
Found it...thanks...this is very cool. I think I can do the transformations within Business Intelligence Studio
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.