ERROR: "activex component can't create object: 'excel.application'"

When I run a DTS, I get the following error.
Error I get: "activex component can't create object:
'excel.application'"
I was told not to load excel into sql server. Well, in any case, there
should not be any need to load excel, it recognizes the excel component
in the DTS, that means, it should be recognizing excel.
Please assist.
Thx,
Swim.
'***************************************
*******************************
' Visual Basic Transformation Script
'***************************************
*********************************
Function Main()
' Initialise dimensions
Dim appExcel
Dim newBook
Dim oSheet
Dim oPackage
Dim oConn
'Set excel objects
Set appExcel = CreateObject("Excel.Application")
Set newBook = appExcel.Workbooks.Add
Set oSheet = newBook.Worksheets(1)
' Populate heading columns for mapping purposes and display in excel
oSheet.Range("A1").Value = "Col1"
oSheet.Range("B1").Value = "Col2"
oSheet.Range("C1").Value = "Col3"
oSheet.Range("D1").Value = "Col4"
'Dynamically specify the name of the new Excel file to be created and
exported to
DTSGlobalVariables("fileName").Value = "C:\Rpts\cc\a.xls"
'" & DTSGlobalVariables("gvID").value & ".xls"
With newBook
.SaveAs DTSGlobalVariables("fileName").Value
.save
End With
appExcel.quit
set oPackage = DTSGlobalVariables.parent
'connection 2 is to the Excel file
set oConn = oPackage.connections(2)
oConn.datasource = DTSGlobalVariables("fileName").Value
'Destroy objects
set oPackage = nothing
set oConn = nothing
Main = DTSTaskExecResult_Success 'End on success
End FunctionIs your DTS package just exporting to or import from Excel or is it
instantiating Excel from a VBScript task?
"Swim" <crombusch@.gmail.com> wrote in message
news:1134093275.263026.230460@.g44g2000cwa.googlegroups.com...
> When I run a DTS, I get the following error.
> Error I get: "activex component can't create object:
> 'excel.application'"
> I was told not to load excel into sql server. Well, in any case, there
> should not be any need to load excel, it recognizes the excel component
> in the DTS, that means, it should be recognizing excel.
> Please assist.
> Thx,
> Swim.
>
>
>
>
> '***************************************
*******************************
> ' Visual Basic Transformation Script
> '***************************************
*********************************
> Function Main()
> ' Initialise dimensions
> Dim appExcel
> Dim newBook
> Dim oSheet
> Dim oPackage
> Dim oConn
> 'Set excel objects
> Set appExcel = CreateObject("Excel.Application")
> Set newBook = appExcel.Workbooks.Add
> Set oSheet = newBook.Worksheets(1)
> ' Populate heading columns for mapping purposes and display in excel
> oSheet.Range("A1").Value = "Col1"
> oSheet.Range("B1").Value = "Col2"
> oSheet.Range("C1").Value = "Col3"
> oSheet.Range("D1").Value = "Col4"
>
> 'Dynamically specify the name of the new Excel file to be created and
> exported to
> DTSGlobalVariables("fileName").Value = "C:\Rpts\cc\a.xls"
> '" & DTSGlobalVariables("gvID").value & ".xls"
> With newBook
> .SaveAs DTSGlobalVariables("fileName").Value
> .save
> End With
> appExcel.quit
> set oPackage = DTSGlobalVariables.parent
> 'connection 2 is to the Excel file
> set oConn = oPackage.connections(2)
> oConn.datasource = DTSGlobalVariables("fileName").Value
> 'Destroy objects
> set oPackage = nothing
> set oConn = nothing
> Main = DTSTaskExecResult_Success 'End on success
> End Function
>

Error with SimplePageHeaders deviceinfo setting

I am using RS2000 SP2 and trying to implement SimplePageHeaders
deviceinfo to put the report header into the page header in Excel, but
cannot get this to work. I'm adding &rc:SimplePageHeaders=TRUE to my
URL but the Excel file does not appear to be generated.
If I add rc:RemoveSpace=0.5in, or rc:OmitFormulas=True, these work OK.
If I use SimplePageHeaders, the dialog to download the xls file
displays no File Name or File Type and if I select Open, I get an
Internet Explorer error "Internet Explorer cannot download from
<servername>. Internet Explorer was not able to open this Internet
site. The requested site is either unavailable or cannot be found.
Please try again later".
I have tried running tcptrace on it - this shows a rendering error:
"Microsoft.ReportingServices.ReportRendering.ReportRenderingException
was thrown. (rrRenderingError) ...... Object reference not set to an
instance of an object."
I've also tried adding the device info to the config file as
illustrated in MSDN online docs, but this appears to be completely
ignored.
Any assistance appreciated.I've had no response so far with the query below. Can anyone confirm
if using this deviceinfo setting should be feasible in RS2000 SP2 and
if there are any known issues with it?
AMasson wrote:
> I am using RS2000 SP2 and trying to implement SimplePageHeaders
> deviceinfo to put the report header into the page header in Excel, but
> cannot get this to work. I'm adding &rc:SimplePageHeaders=TRUE to my
> URL but the Excel file does not appear to be generated.
> If I add rc:RemoveSpace=0.5in, or rc:OmitFormulas=True, these work OK.
> If I use SimplePageHeaders, the dialog to download the xls file
> displays no File Name or File Type and if I select Open, I get an
> Internet Explorer error "Internet Explorer cannot download from
> <servername>. Internet Explorer was not able to open this Internet
> site. The requested site is either unavailable or cannot be found.
> Please try again later".
> I have tried running tcptrace on it - this shows a rendering error:
> "Microsoft.ReportingServices.ReportRendering.ReportRenderingException
> was thrown. (rrRenderingError) ...... Object reference not set to an
> instance of an object."
> I've also tried adding the device info to the config file as
> illustrated in MSDN online docs, but this appears to be completely
> ignored.
> Any assistance appreciated.

error with OpenRowSet

I'm trying to import a simple excel spreadsheet into MSDE using OpenRowSet,
but receive the following error:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
Connection Closed
here's the sql:
select *
into MyTable
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Default.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')
any help is appreciated...I think it should work if file name is correct. Check whether the file
name is correct
Madhivanan|||Hi
Try this one
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="d:\MyData.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Sheet1$
OR
Specify all columns
insert into MyTable (col1,col2)
SELECT col1,col2
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Default.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')
".bill" <wSweetman@.ucsd.edu> wrote in message news:O3rAPZfHFHA.3612@.TK2MSFTN
GP09.phx.gbl...
I'm trying to import a simple excel spreadsheet into MSDE using OpenRowSet,
but receive the following error:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
Connection Closed
here's the sql:
select *
into MyTable
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Default.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')
any help is appreciated...|||Do you have the file opened in Excel when you try to do this?
That would cause an error, but otherwise the syntax looks ok.
If the file C:\Default.xls exists and has a sheet named
Sheet1, it should work. Does the SQL Server
login have access to that file? What happens if you try
exec master..xp_cmdshell N'dir C:\*.xls'
Steve Kass
Drew University
.bill wrote:

>I'm trying to import a simple excel spreadsheet into MSDE using OpenRowSet,
but receive the following error:
>OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
>Connection Closed
>here's the sql:
>select *
>into MyTable
>FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;Database=C:\Default.xls;HDR=YES',
> 'SELECT * FROM [Sheet1$]')
>any help is appreciated...
>
>|||Hi there,
Has anyone found an answer to this yet?
I tried:
insert into tblTestSql (RowNo,RecNo,LdUserName,
Code,LogIn,LogOut)
SELECT RowNo,RecNo,LdUserName,
Code,LogIn,LogOut
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\ReleaseTwo\LogInandOut.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')
Which gives me
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Unspecified error]
When I try:
exec master..xp_cmdshell N'dir c:\ReleaseTwo\LogInandOut.xls'
I get
The system cannot find the file specified.
Any ideas?
Paul
".bill" wrote:

> I'm trying to import a simple excel spreadsheet into MSDE using OpenRowSet
, but receive the following error:
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> Connection Closed
> here's the sql:
> select *
> into MyTable
> FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;Database=C:\Default.xls;HDR=YES',
> 'SELECT * FROM [Sheet1$]')
> any help is appreciated..|||Hi Paul,
The file is in the local machine and you might have run the query in
the server which is not the local machine
Madhivanan|||Hi Madhivanan,
It dosn't matter whether the excel (or txt/csv) file is on the server on on
my local machine I get the same result.
Paul.
"Madhivanan" wrote:

> Hi Paul,
> The file is in the local machine and you might have run the query in
> the server which is not the local machine
> Madhivanan
>|||Paul,
How do you know the file exists?
If it does, and exec master..xp_cmdshell N'dir
c:\ReleaseTwo\LogInandOut.xls' fails,
it's not a problem with OPENROWSET. It sounds like the account used by
SQL Server does not have permission to access the Excel file.
SK
Paul in Harrow wrote:
>Hi there,
>Has anyone found an answer to this yet?
>I tried:
>insert into tblTestSql (RowNo,RecNo,LdUserName,
>Code,LogIn,LogOut)
>SELECT RowNo,RecNo,LdUserName,
>Code,LogIn,LogOut
>FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;Database=C:\ReleaseTwo\LogInandOut.xls;HDR=YES',
> 'SELECT * FROM [Sheet1$]')
>Which gives me
>Server: Msg 7399, Level 16, State 1, Line 1
>OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
>[OLE/DB provider returned message: Unspecified error]
>When I try:
>exec master..xp_cmdshell N'dir c:\ReleaseTwo\LogInandOut.xls'
>I get
>The system cannot find the file specified.
>Any ideas?
>Paul
>".bill" wrote:
>
>|||> How do you know the file exists?
Because I created it.

> If it does, and exec master..xp_cmdshell N'dir
> c:\ReleaseTwo\LogInandOut.xls' fails,
> it's not a problem with OPENROWSET. It sounds like the account used by
> SQL Server does not have permission to access the Excel file.
I've just shown that to my techie and he's looking blank! How do I/we find
out what permissions SQL Server has & how do I/we (proberly me) change them?
Many thanks
Paul.|||
Paul in Harrow wrote:

>Because I created it.
>
>
>I've just shown that to my techie and he's looking blank! How do I/we find
>out what permissions SQL Server has & how do I/we (proberly me) change them
?
>
>
It might be as simple as: navigate to the file in Windows Explorer,
right-click, go to the security tab, and make sure the file is available
to the account under which the SQL Server service is running.
But managing Windows accounts is not my strong point, so you could also
look at "Setting up Windows Services Accounts" in Books Online and
see what you find on Usenet:
http://groups.google.co.uk/groups? ...missions%20file
http://www.google.co.uk/search? q=%...missions%20file
For the openquery statement to work, you should be able to log on to
Windows with the same account used by the SQL Server service and
then access the file.
SK

>Many thanks
>Paul.
>

Error with expoting to Excel

I got the following error when exporting a report to Excel
Reporting Services Error
----
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown. (rrRenderingError) Get Online Help
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown.
An unexpected error occurred in Report Processing. (rsInternalError) Get
Online Help
Object reference not set to an instance of an object
--
Amila IndikaI got the same error.
I have a table in report body and grouped by, say, ColA.
In the page footer, a textbox refererences ColA by
First(ReportItems!ColA.Value).
When I export the report in PDF format, it works fine, but I got the error
when exporting to EXCEL format.
Any solution? Thanks
"Amila Indika" wrote:
> I got the following error when exporting a report to Excel
> Reporting Services Error
> ----
> Exception of type
> Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> thrown. (rrRenderingError) Get Online Help
> Exception of type
> Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> thrown.
> An unexpected error occurred in Report Processing. (rsInternalError) Get
> Online Help
> Object reference not set to an instance of an object
> --
> Amila Indika
>

Error while trying to export to Excel

One of the RS2005 users is complaining that they are no longer able to export
data to Excel. The problem is specific to just one report. Following is the
error I am getting.
w3wp!ui!8!7/21/2007-16:51:14:: Unhandled exception: System.Exception: An
error occurred during rendering of the report. --> System.Exception: An
error occurred during rendering of the report. --> System.Exception: Excel
Rendering Extension : Number of columns in the Excel sheet exceeded the limit
of 256 columns.
The report was working just fine before. Has anyone faced a similar issue?On Jul 21, 5:10 pm, Adam <A...@.discussions.microsoft.com> wrote:
> One of the RS2005 users is complaining that they are no longer able to export
> data to Excel. The problem is specific to just one report. Following is the
> error I am getting.
> w3wp!ui!8!7/21/2007-16:51:14:: Unhandled exception: System.Exception: An
> error occurred during rendering of the report. --> System.Exception: An
> error occurred during rendering of the report. --> System.Exception: Excel
> Rendering Extension : Number of columns in the Excel sheet exceeded the limit
> of 256 columns.
> The report was working just fine before. Has anyone faced a similar issue?
There seems to be very little documentation on this Excel limit error.
If nothing has changed in the report itself, I would suggest re-
uploading the report to the report server. If this does not work, you
might try rebooting the server itself (of course this is a long shot).
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

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?