• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 546
  • Last Modified:

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)

0
ChiBella
Asked:
ChiBella
  • 9
  • 8
1 Solution
 
HoggZillaCommented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
HoggZillaCommented:
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
 
HoggZillaCommented:
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
 
HoggZillaCommented:
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
 
HoggZillaCommented:
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
 
HoggZillaCommented:
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
 
HoggZillaCommented:
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
 
HoggZillaCommented:
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
 
ChiBellaAuthor Commented:
Found it...thanks...this is very cool. I think I can do the transformations within Business Intelligence Studio
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now