[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Server Flat File Import Error

Posted on 2008-11-14
17
Medium Priority
?
496 Views
Last Modified: 2012-05-05
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
Comment
Question by:ChiBella
  • 9
  • 8
17 Comments
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22966994
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
 

Author Comment

by:ChiBella
ID: 22967035
This looks like a known issue (bug). But I see no solution to it anywhere.

package is attached.
test2.dtsx.txt
0
 

Author Comment

by:ChiBella
ID: 22967137
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 17

Expert Comment

by:HoggZilla
ID: 22967159
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
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22967198
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
 

Author Comment

by:ChiBella
ID: 22967208
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
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22967219
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
 

Author Comment

by:ChiBella
ID: 22967247
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
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22967266
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
 

Author Comment

by:ChiBella
ID: 22967295
OK, I think my data is dirty...causing these issues.
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22967300
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
 

Author Comment

by:ChiBella
ID: 22967413
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
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22967476
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
 

Author Comment

by:ChiBella
ID: 22967724
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
 

Author Comment

by:ChiBella
ID: 22967751
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
 
LVL 17

Accepted Solution

by:
HoggZilla earned 2000 total points
ID: 22967774
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
 

Author Comment

by:ChiBella
ID: 22967775
Found it...thanks...this is very cool. I think I can do the transformations within Business Intelligence Studio
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Loops Section Overview
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question