Error while importing Text file using Import Export Wizard

SQL Server Integration Services

Wail_gsm



Hi all

i have text file where i can import it to excel to access or sql2000 without problem but when i import it using (sql2005 pro) i get this error message during the import



Operation stopped...

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)

- Setting Source Connection (Success)

- Setting Destination Connection (Success)

- Validating (Success)

- Prepare for Execute (Success)

- Pre-execute (Success)

Messages

Information 0x402090dc: Data Flow Task: The processing of file "C:\Documents and Settings\Wail\Desktop\All_Alarm5.txt" has started.
(SQL Server Import and Export Wizard)

- Executing (Error)

Messages

Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "FRDNAME" 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 "FRDNAME" (25)" failed because truncation occurred, and the truncation row disposition on "output column "FRDNAME" (25)" 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:\Documents and Settings\Wail\Desktop\All_Alarm5.txt" on data row 9.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - All_Alarm5_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)

- Copying to [DXB_Data].[dbo].[All_Alarm5] (Stopped)

- Post-execute (Success)

Messages

Information 0x402090dd: Data Flow Task: The processing of file "C:\Documents and Settings\Wail\Desktop\All_Alarm5.txt" has ended.
(SQL Server Import and Export Wizard)

Information 0x402090df: Data Flow Task: The final commit for the data insertion has started.
(SQL Server Import and Export Wizard)

Information 0x402090e0: Data Flow Task: The final commit for the data insertion has ended.
(SQL Server Import and Export Wizard)

- Cleanup (Success)

Messages

Information 0x4004300b: Data Flow Task: "component "Destination - All_Alarm5" (64)" wrote 0 rows.
(SQL Server Import and Export Wizard)

 




=============================


**** from the error message (Executing)  error number 2 and 3 it shows that the error is comming from the (column FRDNAME) and row number 9 ...

this cells contains the following text:  ;Ruwais5 B60_Baynounah_R_H_Camp_PH rack1 shelf1 db4g 0;
where the ";" is the delimiter

moreover when i remove this row other problem comes in diffirent location with the following text: ;((AvailabilityStatus,failed ,),(OperationalState,disabled ,));
where the ";" is the delimiter


**** from the error message (Executing)  error number 1
there is somthing called ( code page ) this can be modified from the wizard it self and there are many options to choose. i tryed many of them but without solution


i hope that i will find solution for my problem

thank you

Top

Re: Error while importing Text file using Import Export Wizard

Jamie Thomson


Looking at the error messages it looks as though there's a truncation going on. What is the length of the value in the FRDNAME column in row 9 If that is longer than the length of the target field - that is the problem.

Import/Export wizard offers you the chance to save your package. Do that - then open it up and look at the metadata of the pipeline. See what the length of the FRDNAME field is.

-Jamie





Top

Re: Error while importing Text file using Import Export Wizard

Wail_gsm

dear sir
thank for ur replay

about what u say

Import/Export wizard offers you the chance to save your package

i didnt know how to do it
is it in the Import/Export wizard then in column maping click in Edit SQL ...

also when u say

then open it up and look at the metadata of the pipeline

i didnt under stand it ..... sorry Smile


but after long investegation i found the following

i instal SQL2000 and SQL2005 and i create table inside each of them with the
same spacifecation as follows

all the colums with data type: varchar  and   length: 8000 for both tabels.

i fined somthing very strange Tongue TiedTongue TiedTongue Tied

- when i use the Import/Export wizard of SQL2000
i can import the Text file to the table in SQL2000 and to the table in SQL2005

- when i use the Import/Export wizard of SQL2005
i can't import the same Text file to the table in SQL2000 and also can't import it to the table in SQL2005

- when i check the length of the value in the FRDNAME column in row 9 in the text file i found it 53 and when i reduce the length less than 50 for that value
i found that the Import/Export wizard of SQL2005 works for both table in SQL2000 and to the table in SQL2005

Even both table have length for all colums as 8000 also i change this length to 100, 500, 1000 and other value but the same result !!!!!!!!

Tongue TiedTongue TiedTongue TiedTongue TiedTongue Tied



Top

Re: Error while importing Text file using Import Export Wizard

Daniel Read

I'm convinced also that some kind of bug must exist here. I keep getting this error:

The "output column "XXXX" (42)" failed because truncation occurred, and the truncation row disposition on "output column "XXXX" (42)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

This makes no sense. The data in question is nowhere near large enough to cause a truncation. All the columns are properly mapped. All of the rows are properly delimited.

I've tried to make sense of the "Error Output" tab on the OLE DB Destination Editor, but I can't make any sense of it. If I could maybe I could find a way to tell SSIS to ignore whatever fictional truncation errors it is making.

Dan


Top

Re: Error while importing Text file using Import Export Wizard

Daniel Read

OK, time for me to eat some crow.

Long story short, turns out there was a corruption being introduced in the flat file from a zip extraction library. I was having this problem with multiple files, all of which had been extracted in the same way. The corruption did not exist in another copy of the file that I had, which is why I was so convinced that there was nothing wrong with the file. The copy of the file that was testing with BULK INSERT directly had the same corruption.

My apologies to the SQL team at Microsoft for being so convinced there was a bug here. :-)

Dan


Top

Re: Error while importing Text file using Import Export Wizard

dkpagan

Is anyone still getting this problem I get the error when importing a flat file into sql2005 but don't when importing to sql2000 using the exact same file.

Thanks!!


Top

Re: Error while importing Text file using Import Export Wizard

kReynolds

I get the same error while trying to import more then 50 characters. Same text file imports fine to sql2000


Top

Re: Error while importing Text file using Import Export Wizard

Rykie

I get the same error on all our development and production machines.

I believe that this is a Microsoft SQL Server 2005 *BUG*, yes a bug, because I can import the same data on sql 2000. Because our production environment is already running sql 2005 I had no choice but to leave the dts packages on a sql 2000 machine and simply point the connections to the new sql2005 box and that works 100%.

This will just show that there is a bug in SSIS 2005.

Please help MS! This is a big problem for us!


Top

Re: Error while importing Text file using Import Export Wizard

Jaypee

I agree. I have the same problem. I tried to import a basic file with 3 columns ( varchar(50) ) and I get the same error message The file is ok because the import in SQL Server 2000 works fine.
At first I thought that scandinavian characters are the problem but they are not.

Can anyone help us




Top

Re: Error while importing Text file using Import Export Wizard

Jaypee

I am quite convinced that the there is a bug in Import export wizard.
It looks like that wizard can handle only 50 characters tops in one column.
I resolved the problem by making the bulk insert in sql.

BULK INSERT dbo.[tablename] FROM 'c:\temp\bulkinsertfile.csv' WITH (FIELDTERMINATOR=';',ROWTERMINATOR='\n',CODEPAGE = 'ACP',FIRSTROW=2)

Hopefully this issue is resolved and fixed by Microsoft soon...


Top

Re: Error while importing Text file using Import Export Wizard

Bob Bojanic

The flat file defaults initially all columns to characters with length of 50. You can change this by going to the ˇ°Advancedˇ± page of the Flat File connection page and change the length manually, or you can click on "Suggest Types..." to get suggested column metadata attributes based on sampling a certain number of rows from the file.

Thanks.





Top

Re: Error while importing Text file using Import Export Wizard

Jaypee

Thanks for your answer,

I noticed that the default values for varchar fields are 50. I changed the values in Column Mapping -window but it does not take affect. I get the same error message even if I change the size of the column.
When I changed the values in Advanced page as you told everything works fine ;)
I gues that Column Mapping window does not do the same "trick" as the Advanced page...That suggest types works fine.

There are some problems in SSIS. When import fails and I have to change some parameters (not closing the window first).
I occasionally get the error messages and I have to start all over again. I can't say now what I exactly did.


Top

Re: Error while importing Text file using Import Export Wizard

hostile1

I think there is a bug in the product as well. I have tried to import SQL generate documents and received these errors. I have had to add the extra step of first importing them into a spreadsheet and then importing into SQL Server 2005. If anyone knows of a patch, I would love to know.


Top

Re: Error while importing Text file using Import Export Wizard

db_guy

I have been getting the same error trying to export an XML file into a SQL SERVER 2005 database.


Top

Re: Error while importing Text file using Import Export Wizard

paragp

hi jaypee,

I have just tried using the bulk insert as follows

BULK INSERT dbo.[table]

FROM 'Y:\data.csv'

with (FIELDTERMINATOR=',',ROWTERMINATOR='\n',CODEPAGE='ACP',FIRSTROW=2)

and I ge the following error for all the Datetime columns in the file.

Msg 4864, Level 16, State 1, Line 1

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 15 (EnteredDateTime).

any ideas




Top