error: Cursor not returned from query

I'm a really beginner about sql2000.
During my test I have created the following query. It's works ok until I
do't add the code included in section A, when I add it the i obtain the
error: Cursor not returned from query

Anyone can help me?

Thanks Carlo M.

set nocount on

IF OBJECT_ID('storico_big') IS NULL -- section A begin
create table storico_big( data datetime,
bcarrier varchar(20),
bda CHAR(30),
bzone char(50),
bdur int) ;
insert into storico_big -- section A
end

select top 10000
adetdate,bcarrier,bda,bzone,bdur
from pp_cdr (nolock)
where
adetdate < :data_fin and adetdate > :data_in order by adetdate
set nocount off

-- end of queryIW2FIV (carlo.merlini[NONROMPERE]@.libero.it) writes:
> I'm a really beginner about sql2000.
> During my test I have created the following query. It's works ok until I
> do't add the code included in section A, when I add it the i obtain the
> error: Cursor not returned from query

Apparently you are using some environment unknown to me. At least I
don't recognize the message.

> IF OBJECT_ID('storico_big') IS NULL -- section A begin
> create table storico_big( data datetime,
> bcarrier varchar(20),
> bda CHAR(30),
> bzone char(50),
> bdur int) ;
> insert into storico_big -- section A
> end

Since there are several apparent syntax errors here, it would have been nice
if you environment had returned the errors from SQL Server, rather than
bitching about the missing cursor.

It can be a good to run the query from Query Analyzer to get better
error diagnostics.

I don't really want to suggest a correction, because I can't understand
what you are trying to do. It appears that in the same batch that you first
want to create a table, insert into it, and then select data from another
table.

Possibly you want to insert the data from the SELECT statement into
storico_big, but in such case you should

1) get rid of that extraneous end
2) add an explicit column list to the INSERT statement.

However, I have a feeling that if you insert data into the table, the
client environment will still complain about a missing cursor...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:
> IW2FIV (carlo.merlini[NONROMPERE]@.libero.it) writes:
> > I'm a really beginner about sql2000.
> > During my test I have created the following query. It's works ok until I
> > do't add the code included in section A, when I add it the i obtain the
> > error: Cursor not returned from query
> Apparently you are using some environment unknown to me. At least I
> don't recognize the message.
I would suspect that it's some data layer (such as ADO, ADO.NET, DAO,
etc), which has at least two different methods of performing work in
the database - one for performing data retrieval and another (possibly
on another object, if the layer is object oriented) which allows data
manipulation.

For instance, ADO.NET has ExecuteReader and ExecuteNonQuery methods on
it's command object.

Damien|||Damien (Damien_The_Unbeliever@.hotmail.com) writes:
> I would suspect that it's some data layer (such as ADO, ADO.NET, DAO,
> etc), which has at least two different methods of performing work in
> the database - one for performing data retrieval and another (possibly
> on another object, if the layer is object oriented) which allows data
> manipulation.
> For instance, ADO.NET has ExecuteReader and ExecuteNonQuery methods on
> it's command object.

Obviously IWZFIV is not using ADO .Net. ADO .Net does work with cursors
at all, as far as a I know. A more substantial clue is the condition:

adetdate < :data_fin and adetdate > :data_in order by adetdate

Apparently IWZFIV is using some form of embedded SQL.

Anyway, I would not really describe ADO .Net as providing different methods
for different purposes. If all you want is minimalism, you can do every-
thing with ExecuteReader. The other methods, ExecuteNonQuery, ExecuteScalar
and DataAdapter.Fill can be seen as convenience methods implemented on
top of ExecuteReader. (OK, this is not really true. There are some
fine differences when there are multiple error messages and result sets
interleaved.)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

error: Could not find SP upd.sp_MSrepl_xxx_xxxxxx_1

Trying to set up replication as follows:

SQL2000 publisher , acting as it's own distributor (v8.0.760)

Transactional Replication with updateable subscriptions

SQL2005 subscriber (v9.00.3042)

Push subscription with immediate updating from distributor to subscriber and queued updating from subscriber to distributor

The set-up went smoothly and changes in the publication db flow down to the subscriber as expected. However, changes at the subscriber fail to flow up to the publisher.

The Queue Reader agent reports the error "Server MyServer, Database publisher_db : ODBC Error: Could not find stored procedure "upd.sp_MSrepl_ins_mytable_1"

The SP 'sp_MSrepl_ins_mytable_1' exists in the publisher db but the owner is 'dbo' not 'upd' (there is no such login as 'upd' on the server)

The only useful article I have found on the net suggests that this could be caused by a bug (jn SP3?) but the situation described in that article and the fix/workaround for it don't seem to apply for me.

The problem and fix in that article was: table MSsubscription agents in the subscriber db had a queue_id value of 'mssqlqueue' and the fix was to set it to 'mssqlqueuev2'.
However, it is already ''mssqlqueuev2' in my subscription db so I'm at a loss to explain why it's still generating calls looking for SPs owned by 'upd'.

other info:

Just to see if it would work around my immediate peoblem I tried creating a user called 'upd' and duplicating the SPs.
This worked up to a point. Changes replicated up to my publisher ok. However, when I created a conflict (changed same row on publisher and subscriber at same time)
the Queue Reader fell over with error: "Server MyServer, Database publisher_db : ODBC Error: Could not find stored procedure "upd.1".
-

Investigated the stored procdure sys.sp_replqueuemonitor in my subscription database and found statements like this:
select publisher, publisher_db, publication, queue_server, queue_id
from dbo.MSsubscription_agents
where
publisher = case when @.publisher is NULL then publisher else UPPER(@.publisher) end AND
publisher_db = case when @.publisherdb is NULL then publisher_db else @.publisherdb end AND
publication = case when @.publication is NULL then publication else @.publication end AND
update_mode IN (2,3) AND
queue_id != N'mssqlqueue'
note that the queue_id is is looking for is 'mssqlqueue'.
If I manually run sys.sp_replqueuemonitor I get no results (unsurprising since the data in dbo.MSsubscription_agents has queue_id='mssqlqueuev2'.

It looks to me as if some of the SPs created when the publication and/or subscription was set up are incorrect...?
--

Any advice or suggestions on how to approach this problem?

Check your article properties, somehow the destination owner must have been changed to "upd" along the way. You can also query sysarticles in your published database to see if this owner exists anywhere. Please let me know if this is not the case.|||

The owner for each article is set to 'dbo'. When it first happened there wasn't a login (or schema) called 'upd' in existence on the either of the publication or subscriber servers.

I'm just trialling this with a very small test db (2 tables) so no one else would be playing around with it. I've also recreated the database and publication from scratch several times with the same result.

I'm thinking it may be a bug or an incorrectly applied service pack (the servers are not under our control, I don't even know who maintains them) .

It's bound to be probably related to the fact that I'm trying to replicate between SQL2000 and SQL2005 servers. I know that merge replication with a SQL2000 publisher and a SQL2005 subscriber is not supported but transactional replication is supported (though I'm not 100% sure that encompases transactional replication with updateable subscribers...)

|||sorry, i totally missed the updatable subscriber part, let me reread your thread and dig in some more.|||

The plot thickens...

If I create a subscriber on the same box as the publisher (i.e. publisher & subscriber are both SQL2000) it all works fine.

If the subscriber is SQL2005 it errors looking for SPs owned by 'upd'...

|||it's most likely a bug then. You're going to have to call customer support and open a case on this one if you want to get this type of scenario to work.|||

Thanks for the help. We've abandoned replication for now (we were trying to retrofit it into an existing legacy tas a temporary workaround which is really more trouble than it's worth)

If we do need to get it working I'll raise it with customer support...

Cheers

Tony

|||My opinion would be to upgrade the SQL 2000 node to SQL 2005 and look into merge replication or transactional replication using Peer to Peer.

Error while importing Text file using Import Export Wizard

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 youLooking 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|||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
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

- 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 !!!!!!!!

|||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|||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|||

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!!

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

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!

|||

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?

|||

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...

|||

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.

|||

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.

|||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.|||I have been getting the same error trying to export an XML file into a SQL SERVER 2005 database.|||

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?

Error while importing Text file using Import Export Wizard

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
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|||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
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

- 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 !!!!!!!!

|||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

|||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

|||

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!!

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

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!

|||

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?

|||

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...

|||

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.

|||

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.

|||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.
|||I have been getting the same error trying to export an XML file into a SQL SERVER 2005 database.|||

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?

Error while importing Text file using Import Export Wizard

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
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|||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
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

- 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 !!!!!!!!

|||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

|||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

|||

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!!

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

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!

|||

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?

|||

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...

|||

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.

|||

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.

|||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.
|||I have been getting the same error trying to export an XML file into a SQL SERVER 2005 database.|||

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?

Error while importing Text file using Import Export Wizard

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 youLooking 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|||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
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

- 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 !!!!!!!!

|||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|||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|||

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!!

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

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!

|||

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?

|||

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...

|||

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.

|||

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.

|||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.|||I have been getting the same error trying to export an XML file into a SQL SERVER 2005 database.|||

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?

Error while importing Text file using Import Export Wizard

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 youLooking 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|||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
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

- 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 !!!!!!!!

|||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|||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|||

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!!

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

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!

|||

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?

|||

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...

|||

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.

|||

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.

|||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.|||I have been getting the same error trying to export an XML file into a SQL SERVER 2005 database.|||

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?

Error while importing Text file using Import Export Wizard

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 youLooking 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|||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
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

- 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 !!!!!!!!

|||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|||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|||

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!!

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

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!

|||

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?

|||

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...

|||

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.

|||

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.

|||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.|||I have been getting the same error trying to export an XML file into a SQL SERVER 2005 database.|||

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?