ChiBella
asked on
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)
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)
ASKER
This looks like a known issue (bug). But I see no solution to it anywhere.
package is attached.
test2.dtsx.txt
package is attached.
test2.dtsx.txt
ASKER
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.
Not sure what the problem is/was. Apparently you have to change the destination sizes within the Advanced section.
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.
If you modify any of the mappings then it is subject to fail, not always, but it is a risk.
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.
ASKER
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?
Is there a way to save the package and modify it to run with different input files?
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?
ASKER
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?
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?
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
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
ASKER
OK, I think my data is dirty...causing these issues.
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.
ASKER
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.
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.
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
I will build you a package to do the job - or die trying. :-)
I will be back in an hour or so. HZ
ASKER
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/conversion
You win the points here..should I open a new issue?
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/conversion
You win the points here..should I open a new issue?
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Found it...thanks...this is very cool. I think I can do the transformations within Business Intelligence Studio
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.