Data Conversion Errors on Excel Import into existing table

Recently installed Sql Server 2005 client and am now attempting to import data from a spreadsheet into an existing table. This works fine with Sql Server 2000 but I am getting data conversion truncation errors that stop the process when this runs using import utility in Sql Server 2005.

Any help would be appreciated.

More information needed.

Why is it failing? What does the error message say?

You will need to open up the package and edit it to do the conversions that you require.

-Jamie

Data Conversion Error on Excel Destination

I am inserting rows using OLEDBDestination and want to redirect all error rows to EXCEL Destination.

I have used Data Conversion Transformation to Convert all strings to Unicode string fields before sending it to Excel Destination.

But its gives the following error.

[Data Conversion [16]] Error: Data conversion failed while converting column 'A' (53) to column "Copy of A" (95). The conversion returned status value 8 and status text "DBSTATUS_UNAVAILABLE".

[Data Conversion [16]] Error: The "output column "Copy of A" (95)" failed because error code 0xC020908E occurred, and the error row disposition on "output column "Copy of A" (95)" specifies failure on error. An error occurred on the specified object of the specified component.

Can someone please tell me what should I do to make it work?

Thanks,

Did you set the error output of the OLE Destination to "Redirect Rows"?

You might also have metadata problems and could maybe stand to recreate the Excel destination.|||

I have set the error ouput to redirect rows

Actually I have a Data flow task with Oledbsource -->Oledbdestination (Redirect Error Rows ) -- >Data Conversion -- >Excel Destination.

When I do this its gives me the following error.

Data conversion failed while converting column "A" (53) to column "A" (95). The conversion returned status value 8 and status text "DBSTATUS_UNAVAILABL

[Data Conversion [16]] Error: The "output column "Copy of A" (95)" failed because error code 0xC020908E occurred, and the error row disposition on "output column "Copy of A" (95)" specifies failure on error. An error occurred on the specified object of the specified component.

But for this data flow task Oledbsource -- > Data Conversion - > Excel Destination. Its works fine.

I am doing the same data conversion in both data flow tasks.

Please let me know what I am doing wrong.

|||Well, I suppose check and double check your column mappings going into and out of the OLE DB destination.|||

prg wrote:

I am inserting rows using OLEDBDestination and want to redirect all error rows to EXCEL Destination.

I have used Data Conversion Transformation to Convert all strings to Unicode string fields before sending it to Excel Destination.

But its gives the following error.

[Data Conversion [16]] Error: Data conversion failed while converting column 'A' (53) to column "Copy of A" (95). The conversion returned status value 8 and status text "DBSTATUS_UNAVAILABLE".

[Data Conversion [16]] Error: The "output column "Copy of A" (95)" failed because error code 0xC020908E occurred, and the error row disposition on "output column "Copy of A" (95)" specifies failure on error. An error occurred on the specified object of the specified component.

Can someone please tell me what should I do to make it work?

Thanks,

Actually I would check the Data conversion transform. Specifically the column A; since is there where the error is being generated. Try placing a data view to inspected the values that go into the data conversion. What happens if you delete column A from the data conversion? Does it fail in a different column?

|||

I have checked the Data conversion transform again. I have removed column A and used another column. Still it fails.

This is kind of weird. I cant figure out where the problem is .

Is it that EXCEL Destination cannot be used to redirect rows that have errors? Because its seems to work perfectly when I do OLEDBSource -> Data Conversion ->Excel Destination.

|||Well, if the errors are created (redirected) because of conversion errors, then it is natural that the redirected error rows don't match your destination data types. They can't because that's why they are in error.

Make sure that the data types of the Excel destination match the data types of your SOURCE data, not the data types of the OLE DB Destination table. Not sure if this will work because the metadata probably won't match...|||

Hi all,

I think I have exactly the same problem using a sql destination for error output.

Error: 0xC02020C5 at GS_ORDREC, Data Conversion [1827]: Data conversion failed while converting column "OL_DONORD" (102) to column "Test" (1880). The conversion returned status value 8 and status text "DBSTATUS_UNAVAILABLE".

Error: 0xC0209029 at GS_ORDREC, Data Conversion [1827]: The "output column "Test" (1880)" failed because error code 0xC020908E occurred, and the error row disposition on "output column "Test" (1880)" specifies failure on error. An error occurred on the specified object of the specified component.

Error: 0xC0047022 at GS_ORDREC, DTS.Pipeline: The ProcessInput method on component "Data Conversion" (1827) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

SSIS package "GS_ODSREC.dtsx" finished: Failure.

-

I check and recheck the package, everything is ok. Strange thing, when I try to convert the "ErrorColumn" field, the package works fine.

Regards

Ayzan

|||

Ayzan wrote:

Hi all,

I think I have exactly the same problem using a sql destination for error output.

Error: 0xC02020C5 at GS_ORDREC, Data Conversion [1827]: Data conversion failed while converting column "OL_DONORD" (102) to column "Test" (1880). The conversion returned status value 8 and status text "DBSTATUS_UNAVAILABLE".

Error: 0xC0209029 at GS_ORDREC, Data Conversion [1827]: The "output column "Test" (1880)" failed because error code 0xC020908E occurred, and the error row disposition on "output column "Test" (1880)" specifies failure on error. An error occurred on the specified object of the specified component.

Error: 0xC0047022 at GS_ORDREC, DTS.Pipeline: The ProcessInput method on component "Data Conversion" (1827) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

SSIS package "GS_ODSREC.dtsx" finished: Failure.

-

I check and recheck the package, everything is ok. Strange thing, when I try to convert the "ErrorColumn" field, the package works fine.

Regards

Ayzan

Okay, you *don't* have the same problem as the OP. If you are using a SQL Server destiantion, SSIS will not automatically cast/convert datatypes for you. The data flow datatypes (metadata) will need to match EXACTLY with the SQL Server destination table.|||

Thank you Phil Brammer,

Sorry this IS the same problem, cause I try with a Sql server destination, a data reader destination, a flat file destination, for redirecting the failed rows, and I have still the same message ... in spite of casting/converting datatypes

Regards

Ayzan

|||

Ayzan wrote:

Thank you Phil Brammer,

Sorry this IS the same problem, cause I try with a Sql server destination, a data reader destination, a flat file destination, for redirecting the failed rows, and I have still the same message ... in spite of casting/converting datatypes

Regards

Ayzan

Ah, see, you left out valuable information! So I'll ask this as I have done before. Before we continue, please provide the metadata information as it stands before going into the destination. Then, please provide the table column information for the destination as well.

One other thing to note is that there could be one bad row that is causing this error... Have you redirected errors and inspected them?|||

Ok,

There is a simple Data Flow, so as to reproduce the error:

#Data Source, OLEDB, AdventureWorksDW

#OLE DB Source, SQL Command :

SELECT TOP (10) TimeKey, OrganizationKey, DepartmentGroupKey, ScenarioKey, AccountKey+100 as AccountKey, Amount
FROM FactFinance

#OLE DB Destination

AdventureWorks.FactFinance

#Error Output to Data Conersion

TimeKey -> DT_STR

#DataReader Destination

Enjoy !

|||

Ayzan wrote:

Ok,

There is a simple Data Flow, so as to reproduce the error:

#Data Source, OLEDB, AdventureWorksDW

#OLE DB Source, SQL Command :

SELECT TOP (10) TimeKey, OrganizationKey, DepartmentGroupKey, ScenarioKey, AccountKey+100 as AccountKey, Amount
FROM FactFinance

#OLE DB Destination

AdventureWorks.FactFinance

#Error Output to Data Conersion

TimeKey -> DT_STR

#DataReader Destination

Enjoy !

I don't have a AdventureWorks.FactFinance table. I have the data warehouse table... Are you creating your own FactFinance table in the AdventureWorks table? The mere prefix of "Fact" indicates that it should be in AdventureWorksDW.|||

Sorry,

#OLE DB Destination

AdventureWorksDW.FactFinance

Well in fact, whatever the source/destination, you just have to generate an error while trying to insert new records.

Regards

Ayzan

|||

Ayzan wrote:

Sorry,

#OLE DB Destination

AdventureWorksDW.FactFinance

Well in fact, whatever the source/destination, you just have to generate an error while trying to insert new records.

Regards

Ayzan

I'm utterly confused now. So your source and destination tables are the same? What are you doing exactly, and what are you expecting to see? Why would I select the top 10 records from FactFinance and then turn around and insert them again? Regardless, nowhere should a DT_STR datatype be picked up on the Timekey field because it is an integer field.

Nevermind. I understand now. Hang on.

Data Conversion Error on Excel Destination

I am inserting rows using OLEDBDestination and want to redirect all error rows to EXCEL Destination.

I have used Data Conversion Transformation to Convert all strings to Unicode string fields before sending it to Excel Destination.

But its gives the following error.

[Data Conversion [16]] Error: Data conversion failed while converting column 'A' (53) to column "Copy of A" (95). The conversion returned status value 8 and status text "DBSTATUS_UNAVAILABLE".

[Data Conversion [16]] Error: The "output column "Copy of A" (95)" failed because error code 0xC020908E occurred, and the error row disposition on "output column "Copy of A" (95)" specifies failure on error. An error occurred on the specified object of the specified component.

Can someone please tell me what should I do to make it work?

Thanks,

Did you set the error output of the OLE Destination to "Redirect Rows"?

You might also have metadata problems and could maybe stand to recreate the Excel destination.|||

I have set the error ouput to redirect rows

Actually I have a Data flow task with Oledbsource -->Oledbdestination (Redirect Error Rows ) -- >Data Conversion -- >Excel Destination.

When I do this its gives me the following error.

Data conversion failed while converting column "A" (53) to column "A" (95). The conversion returned status value 8 and status text "DBSTATUS_UNAVAILABL

[Data Conversion [16]] Error: The "output column "Copy of A" (95)" failed because error code 0xC020908E occurred, and the error row disposition on "output column "Copy of A" (95)" specifies failure on error. An error occurred on the specified object of the specified component.

But for this data flow task Oledbsource -- > Data Conversion - > Excel Destination. Its works fine.

I am doing the same data conversion in both data flow tasks.

Please let me know what I am doing wrong.

|||Well, I suppose check and double check your column mappings going into and out of the OLE DB destination.|||

prg wrote:

I am inserting rows using OLEDBDestination and want to redirect all error rows to EXCEL Destination.

I have used Data Conversion Transformation to Convert all strings to Unicode string fields before sending it to Excel Destination.

But its gives the following error.

[Data Conversion [16]] Error: Data conversion failed while converting column 'A' (53) to column "Copy of A" (95). The conversion returned status value 8 and status text "DBSTATUS_UNAVAILABLE".

[Data Conversion [16]] Error: The "output column "Copy of A" (95)" failed because error code 0xC020908E occurred, and the error row disposition on "output column "Copy of A" (95)" specifies failure on error. An error occurred on the specified object of the specified component.

Can someone please tell me what should I do to make it work?

Thanks,

Actually I would check the Data conversion transform. Specifically the column A; since is there where the error is being generated. Try placing a data view to inspected the values that go into the data conversion. What happens if you delete column A from the data conversion? Does it fail in a different column?

|||

I have checked the Data conversion transform again. I have removed column A and used another column. Still it fails.

This is kind of weird. I cant figure out where the problem is .

Is it that EXCEL Destination cannot be used to redirect rows that have errors? Because its seems to work perfectly when I do OLEDBSource -> Data Conversion ->Excel Destination.

|||Well, if the errors are created (redirected) because of conversion errors, then it is natural that the redirected error rows don't match your destination data types. They can't because that's why they are in error.

Make sure that the data types of the Excel destination match the data types of your SOURCE data, not the data types of the OLE DB Destination table. Not sure if this will work because the metadata probably won't match...|||

Hi all,

I think I have exactly the same problem using a sql destination for error output.

Error: 0xC02020C5 at GS_ORDREC, Data Conversion [1827]: Data conversion failed while converting column "OL_DONORD" (102) to column "Test" (1880). The conversion returned status value 8 and status text "DBSTATUS_UNAVAILABLE".

Error: 0xC0209029 at GS_ORDREC, Data Conversion [1827]: The "output column "Test" (1880)" failed because error code 0xC020908E occurred, and the error row disposition on "output column "Test" (1880)" specifies failure on error. An error occurred on the specified object of the specified component.

Error: 0xC0047022 at GS_ORDREC, DTS.Pipeline: The ProcessInput method on component "Data Conversion" (1827) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

SSIS package "GS_ODSREC.dtsx" finished: Failure.

-

I check and recheck the package, everything is ok. Strange thing, when I try to convert the "ErrorColumn" field, the package works fine.

Regards

Ayzan

|||

Ayzan wrote:

Hi all,

I think I have exactly the same problem using a sql destination for error output.

Error: 0xC02020C5 at GS_ORDREC, Data Conversion [1827]: Data conversion failed while converting column "OL_DONORD" (102) to column "Test" (1880). The conversion returned status value 8 and status text "DBSTATUS_UNAVAILABLE".

Error: 0xC0209029 at GS_ORDREC, Data Conversion [1827]: The "output column "Test" (1880)" failed because error code 0xC020908E occurred, and the error row disposition on "output column "Test" (1880)" specifies failure on error. An error occurred on the specified object of the specified component.

Error: 0xC0047022 at GS_ORDREC, DTS.Pipeline: The ProcessInput method on component "Data Conversion" (1827) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

SSIS package "GS_ODSREC.dtsx" finished: Failure.

-

I check and recheck the package, everything is ok. Strange thing, when I try to convert the "ErrorColumn" field, the package works fine.

Regards

Ayzan

Okay, you *don't* have the same problem as the OP. If you are using a SQL Server destiantion, SSIS will not automatically cast/convert datatypes for you. The data flow datatypes (metadata) will need to match EXACTLY with the SQL Server destination table.|||

Thank you Phil Brammer,

Sorry this IS the same problem, cause I try with a Sql server destination, a data reader destination, a flat file destination, for redirecting the failed rows, and I have still the same message ... in spite of casting/converting datatypes

Regards

Ayzan

|||

Ayzan wrote:

Thank you Phil Brammer,

Sorry this IS the same problem, cause I try with a Sql server destination, a data reader destination, a flat file destination, for redirecting the failed rows, and I have still the same message ... in spite of casting/converting datatypes

Regards

Ayzan

Ah, see, you left out valuable information! So I'll ask this as I have done before. Before we continue, please provide the metadata information as it stands before going into the destination. Then, please provide the table column information for the destination as well.

One other thing to note is that there could be one bad row that is causing this error... Have you redirected errors and inspected them?|||

Ok,

There is a simple Data Flow, so as to reproduce the error:

#Data Source, OLEDB, AdventureWorksDW

#OLE DB Source, SQL Command :

SELECT TOP (10) TimeKey, OrganizationKey, DepartmentGroupKey, ScenarioKey, AccountKey+100 as AccountKey, Amount
FROM FactFinance

#OLE DB Destination

AdventureWorks.FactFinance

#Error Output to Data Conersion

TimeKey -> DT_STR

#DataReader Destination

Enjoy !

|||

Ayzan wrote:

Ok,

There is a simple Data Flow, so as to reproduce the error:

#Data Source, OLEDB, AdventureWorksDW

#OLE DB Source, SQL Command :

SELECT TOP (10) TimeKey, OrganizationKey, DepartmentGroupKey, ScenarioKey, AccountKey+100 as AccountKey, Amount
FROM FactFinance

#OLE DB Destination

AdventureWorks.FactFinance

#Error Output to Data Conersion

TimeKey -> DT_STR

#DataReader Destination

Enjoy !

I don't have a AdventureWorks.FactFinance table. I have the data warehouse table... Are you creating your own FactFinance table in the AdventureWorks table? The mere prefix of "Fact" indicates that it should be in AdventureWorksDW.|||

Sorry,

#OLE DB Destination

AdventureWorksDW.FactFinance

Well in fact, whatever the source/destination, you just have to generate an error while trying to insert new records.

Regards

Ayzan

|||

Ayzan wrote:

Sorry,

#OLE DB Destination

AdventureWorksDW.FactFinance

Well in fact, whatever the source/destination, you just have to generate an error while trying to insert new records.

Regards

Ayzan

I'm utterly confused now. So your source and destination tables are the same? What are you doing exactly, and what are you expecting to see? Why would I select the top 10 records from FactFinance and then turn around and insert them again? Regardless, nowhere should a DT_STR datatype be picked up on the Timekey field because it is an integer field.

Nevermind. I understand now. Hang on.

Data Connections for SQL Server Express in Visual Studio Express

I have an Excel add-in that connects to a SQL Server Express 2005
database. I've decided to create a configuration piece for this add-in
in Visual Studio 2005 Express. I added a data connection using the data
connection wizard and all appeared to go well. Anyways when I attempt
to open SQL Server Express to administer the database, it was corrupted
and I had to restore it.

I eventually got it to work correctly (I'm pretty sure I followed
pretty much the same steps as before), but I was just wondering if
anyone had experienced problems like this? I find it a bit scary that
it may be that easy to corrupt the database by just creating a data
connection.Whats the error message of the database, that you mean that the db is
corrupt ? Maybe you did something wrong in there. The be would be to
post you used code here as well as the error message you are facing.

HTH, jens Suessmeyer.|||I have to apologize -- I can't recreate the error and I didn't write
the orginal error message down.

If I see it again I will post. Thanks for your response.

Crazy

Jens wrote:
> Whats the error message of the database, that you mean that the db is
> corrupt ? Maybe you did something wrong in there. The be would be to
> post you used code here as well as the error message you are facing.
> HTH, jens Suessmeyer.

Data Comparison

I have an Employee table with 3000 records and an Excel file having the
modified data of those emplyoees. Some of the data of Excel may be same
as that of table data but some may differ. EmpId is the unique field.
Other than this field, other fields of Excel may have modified data.I
need to compare the data from SQL Server table with Excel Data.
I decided to write a VB Program having two recordsets,one for SQL
Server and other for Excel and compare each field's value. If the
modified value is found then update that to table. Is there any way to
compare in SQL Server itself?

MadhivananProbably the easiest way is to create a second Employee table with the
same (or similar) structure as the current one. You can load the new
data into it with DTS or bcp.exe, and it's then easy to compare
whatever you want with SQL queries. This is quite a common general
technique for importing data - load into a staging table, check/clean
the data, then INSERT/UPDATE the destination table.

There are other options too, such as creating a linked server pointing
at the .xls file, but I would say that loading the data is probably the
simplest.

Simon|||Yes, I already decided to create new table with same structure and
export Excel to SQL Server table. Thanks

Madhivanan

Damage to file message when exporting to excel

I am getting a "Damage to the file was so extensive that repairs were not
possible. Excel attemted to recover your formulas and values, but some data
may have been lost or corrupted." in some instances when exporting to excel.
I have noticed that a couple other list members have also reported this
issue, but have been unable to narrow down the exact cause of the problem
(see post by DJJIII on 7/13/2005 subject: excel export /collapsed fails to
render excel file)
The interesting thing in my case is that I have a fairly complex report with
many charts and tables. My "top level" report throws this excel error.
However, if I add parameters ("drill down report") the structure of the
report is exactly the same with charts and tables, just less (or different)
data and then it renders fine. Incidentally the top-leve report has params
too, so having parameters itself is not the issue. I am not using groups at
all, so I don't think the issue is directly related to the use of groups as
was suspected in the post by DJJIII.
My guess is there is some data-related issue as our same report (both
top-level and drilldown) with different data works fine.
Our report actually is a collection of 10 or so subreports, so I will be
doing a little debugging by removing subreports to see if I can identify the
subreport or data that is causing the issue.
Microsoft, please let us know if there has been a confirmed issue related to
this and what causes the problem. I would be happy to provide an RDL, excel
file, etc. to help you resolve the issue.
Thanks, DavidI have run into the exact same issue except - my report is essentially a flat
file export. I do not have multiple leves, diagrams or charts.
The only difference I can isolate is a "comments" field that is provided to
users. They record pages and pages of text in this field. If I remove the
field it renders just fine. If I include it then error. So, either the field
length limit in excel? or a special character the users are including that
excel cannot handle? Just guesses.
Any help or fixes would be appreciated. Without this information & the
ability to export to excel the report is considered useless by the users.
Thank you.
--Cory
"David Swanson" wrote:
> I am getting a "Damage to the file was so extensive that repairs were not
> possible. Excel attemted to recover your formulas and values, but some data
> may have been lost or corrupted." in some instances when exporting to excel.
> I have noticed that a couple other list members have also reported this
> issue, but have been unable to narrow down the exact cause of the problem
> (see post by DJJIII on 7/13/2005 subject: excel export /collapsed fails to
> render excel file)
> The interesting thing in my case is that I have a fairly complex report with
> many charts and tables. My "top level" report throws this excel error.
> However, if I add parameters ("drill down report") the structure of the
> report is exactly the same with charts and tables, just less (or different)
> data and then it renders fine. Incidentally the top-leve report has params
> too, so having parameters itself is not the issue. I am not using groups at
> all, so I don't think the issue is directly related to the use of groups as
> was suspected in the post by DJJIII.
> My guess is there is some data-related issue as our same report (both
> top-level and drilldown) with different data works fine.
> Our report actually is a collection of 10 or so subreports, so I will be
> doing a little debugging by removing subreports to see if I can identify the
> subreport or data that is causing the issue.
> Microsoft, please let us know if there has been a confirmed issue related to
> this and what causes the problem. I would be happy to provide an RDL, excel
> file, etc. to help you resolve the issue.
> Thanks, David|||Hello David & Edgar,
There is a known issue that when exporting a report from Reporting Services
SP2 to Excel format results in the following error when trying to open the
Excel workbook:
Microsoft Office Excel File Repair Log
Errors were detected in file 'C:\Documents and Settings\<username>\Local
Settings\Temporary Internet Files\Content.IE5\4H4BKNSF\Report2[1].xls'
The following is a list of repairs:
Damage to the file was so extensive that repairs were not possible. Excel
attempted to recover your formulas and values, but some data may have been
lost or corrupted.
Further investigation indicates that this problem can occur if a field of
data type NTEXT contains more than 4110 characters.
This issue is supposed to be fixed in next edition of SQL reporting service
(sql 2005). If you believe this has big business impact, please contact CSS
open a Support incident with Microsoft Product Support Services so that a
dedicated Support Professional can work with you to evaluate this:
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--
| Thread-Topic: Damage to file message when exporting to excel
| thread-index: AcWhwO0iSuq0BbqxT5u6w58E6hDj9Q==| X-WBNR-Posting-Host: 134.131.125.49
| From: "=?Utf-8?B?Q29yeQ==?=" <Cory@.discussions.microsoft.com>
| References: <48600043-4159-4924-9CD6-3AAE2565F6D7@.microsoft.com>
| Subject: RE: Damage to file message when exporting to excel
| Date: Mon, 15 Aug 2005 10:44:03 -0700
| Lines: 48
| Message-ID: <EA86AEA5-D0AE-47E8-90BB-AAAA28FCF022@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:50386
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| I have run into the exact same issue except - my report is essentially a
flat
| file export. I do not have multiple leves, diagrams or charts.
|
| The only difference I can isolate is a "comments" field that is provided
to
| users. They record pages and pages of text in this field. If I remove
the
| field it renders just fine. If I include it then error. So, either the
field
| length limit in excel? or a special character the users are including
that
| excel cannot handle? Just guesses.
|
| Any help or fixes would be appreciated. Without this information & the
| ability to export to excel the report is considered useless by the users.
|
| Thank you.
|
| --Cory
|
| "David Swanson" wrote:
|
| > I am getting a "Damage to the file was so extensive that repairs were
not
| > possible. Excel attemted to recover your formulas and values, but some
data
| > may have been lost or corrupted." in some instances when exporting to
excel.
| >
| > I have noticed that a couple other list members have also reported this
| > issue, but have been unable to narrow down the exact cause of the
problem
| > (see post by DJJIII on 7/13/2005 subject: excel export /collapsed
fails to
| > render excel file)
| >
| > The interesting thing in my case is that I have a fairly complex report
with
| > many charts and tables. My "top level" report throws this excel error.
| > However, if I add parameters ("drill down report") the structure of the
| > report is exactly the same with charts and tables, just less (or
different)
| > data and then it renders fine. Incidentally the top-leve report has
params
| > too, so having parameters itself is not the issue. I am not using
groups at
| > all, so I don't think the issue is directly related to the use of
groups as
| > was suspected in the post by DJJIII.
| >
| > My guess is there is some data-related issue as our same report (both
| > top-level and drilldown) with different data works fine.
| >
| > Our report actually is a collection of 10 or so subreports, so I will
be
| > doing a little debugging by removing subreports to see if I can
identify the
| > subreport or data that is causing the issue.
| >
| > Microsoft, please let us know if there has been a confirmed issue
related to
| > this and what causes the problem. I would be happy to provide an RDL,
excel
| > file, etc. to help you resolve the issue.
| >
| > Thanks, David
||||I'm experimenting the same thing on one of my reports. I've occurs only when
I'm displaying point labels on which I apply this function :
=ROUND((Fields!CHANGE.Value)*100).TOSTRING & " %"
so there are no ntext field involved here. (I have a work around for my
problem but it requires quit a bit of code change database side)
I'm on RS 2000 sp1 (we haven't applied sp 2 yet because of custom code that
crashes under sp 2. We will be migrating to SQL 2005 in 2 to 3 months.)
thx
Fred
"Peter Yang [MSFT]" wrote:
> Hello David & Edgar,
> There is a known issue that when exporting a report from Reporting Services
> SP2 to Excel format results in the following error when trying to open the
> Excel workbook:
> Microsoft Office Excel File Repair Log
> Errors were detected in file 'C:\Documents and Settings\<username>\Local
> Settings\Temporary Internet Files\Content.IE5\4H4BKNSF\Report2[1].xls'
> The following is a list of repairs:
> Damage to the file was so extensive that repairs were not possible. Excel
> attempted to recover your formulas and values, but some data may have been
> lost or corrupted.
> Further investigation indicates that this problem can occur if a field of
> data type NTEXT contains more than 4110 characters.
> This issue is supposed to be fixed in next edition of SQL reporting service
> (sql 2005). If you believe this has big business impact, please contact CSS
> open a Support incident with Microsoft Product Support Services so that a
> dedicated Support Professional can work with you to evaluate this:
> For a complete list of Microsoft Product Support Services phone numbers,
> please go to the following address on the World Wide Web:
> http://support.microsoft.com/directory/overview.asp
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
> | Thread-Topic: Damage to file message when exporting to excel
> | thread-index: AcWhwO0iSuq0BbqxT5u6w58E6hDj9Q==> | X-WBNR-Posting-Host: 134.131.125.49
> | From: "=?Utf-8?B?Q29yeQ==?=" <Cory@.discussions.microsoft.com>
> | References: <48600043-4159-4924-9CD6-3AAE2565F6D7@.microsoft.com>
> | Subject: RE: Damage to file message when exporting to excel
> | Date: Mon, 15 Aug 2005 10:44:03 -0700
> | Lines: 48
> | Message-ID: <EA86AEA5-D0AE-47E8-90BB-AAAA28FCF022@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:50386
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | I have run into the exact same issue except - my report is essentially a
> flat
> | file export. I do not have multiple leves, diagrams or charts.
> |
> | The only difference I can isolate is a "comments" field that is provided
> to
> | users. They record pages and pages of text in this field. If I remove
> the
> | field it renders just fine. If I include it then error. So, either the
> field
> | length limit in excel? or a special character the users are including
> that
> | excel cannot handle? Just guesses.
> |
> | Any help or fixes would be appreciated. Without this information & the
> | ability to export to excel the report is considered useless by the users.
> |
> | Thank you.
> |
> | --Cory
> |
> | "David Swanson" wrote:
> |
> | > I am getting a "Damage to the file was so extensive that repairs were
> not
> | > possible. Excel attemted to recover your formulas and values, but some
> data
> | > may have been lost or corrupted." in some instances when exporting to
> excel.
> | >
> | > I have noticed that a couple other list members have also reported this
> | > issue, but have been unable to narrow down the exact cause of the
> problem
> | > (see post by DJJIII on 7/13/2005 subject: excel export /collapsed
> fails to
> | > render excel file)
> | >
> | > The interesting thing in my case is that I have a fairly complex report
> with
> | > many charts and tables. My "top level" report throws this excel error.
> | > However, if I add parameters ("drill down report") the structure of the
> | > report is exactly the same with charts and tables, just less (or
> different)
> | > data and then it renders fine. Incidentally the top-leve report has
> params
> | > too, so having parameters itself is not the issue. I am not using
> groups at
> | > all, so I don't think the issue is directly related to the use of
> groups as
> | > was suspected in the post by DJJIII.
> | >
> | > My guess is there is some data-related issue as our same report (both
> | > top-level and drilldown) with different data works fine.
> | >
> | > Our report actually is a collection of 10 or so subreports, so I will
> be
> | > doing a little debugging by removing subreports to see if I can
> identify the
> | > subreport or data that is causing the issue.
> | >
> | > Microsoft, please let us know if there has been a confirmed issue
> related to
> | > this and what causes the problem. I would be happy to provide an RDL,
> excel
> | > file, etc. to help you resolve the issue.
> | >
> | > Thanks, David
> |
>

daily import into ms sql from access or excel

hi, my company needs to import 3 access or excel ,customer order table, into
ms sql database daily.orders(basic customer info), items(product info and quanity), options (options of quantity). The problem is that 3 table has to get additional column when it is imported into sql. For example, when an order comes in its intranet application, it will keep track if it is in stock or out of stock checking the product table. those 2 databases ms sql and access or excel has identical columns but sql one has more in addition . This is my first time to work this kind of case and if somebody could give me a abstract step or suggestions to accomplish this.I'll be very happy;)

Thanks
kiss... use default values and/or dts to transfer the data to it's respective area.

if you wish to create an application to handle it, then just make your sql more precise to match your needs.

no need for kisses. It's unprofessional.

CVS Export in ASCII format

For the CSV export problem, where excel opens data into one column, I
had to add a report link using the below code to fix the problem. Is
there a way to change the underlying export encoding in reporting
services?
="javascript:void(window.open(top.frames[0].frames[1].location.href.replace('Format=HTML4.0','Format=CSV&rc%3aEncoding=ASCII'),'_blank'))"There is for RS 2005. Not for RS 2000.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<slov1@.hotmail.com> wrote in message
news:1135297330.158158.120010@.g49g2000cwa.googlegroups.com...
> For the CSV export problem, where excel opens data into one column, I
> had to add a report link using the below code to fix the problem. Is
> there a way to change the underlying export encoding in reporting
> services?
> ="javascript:void(window.open(top.frames[0].frames[1].location.href.replace('Format=HTML4.0','Format=CSV&rc%3aEncoding=ASCII'),'_blank'))"
>

Cutomizing Exported Excel tabs

Hi All
I have a very simple report that is grouped on a particular field. I have
also set the "Page Break at end property" set to true for this group. I wish
to set up a subscription that will email this report as an excel attachment.
The excel export shows the records of each group in a separate sheet -
Exactly the way I need. What I also want is that the tabs of each sheet
should show the group field name as its name instead of sheet1, sheet2
etc....
Is there any way to achieve this ?
ThanksSorry, there is no property in the RDL that would allow you to achieve
customizing the sheet names.
It is under consideration for inclusion in a future release.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"PR" <PR@.discussions.microsoft.com> wrote in message
news:FEDD440A-49E9-4F2F-9DA3-08CC995E2D7B@.microsoft.com...
> Hi All
> I have a very simple report that is grouped on a particular field. I have
> also set the "Page Break at end property" set to true for this group. I
wish
> to set up a subscription that will email this report as an excel
attachment.
> The excel export shows the records of each group in a separate sheet -
> Exactly the way I need. What I also want is that the tabs of each sheet
> should show the group field name as its name instead of sheet1, sheet2
> etc....
> Is there any way to achieve this ?
> Thanks|||Robert
Can it be moved off the consideration list onto the must do list?
Providing the functionality to output to multiple sheets without the ability
to dynamically name those sheets makes multiple sheets pretty pointless.
I have one report that generates an Excel file for state managers. The file
contains a seperate sheet for each consultant in their state. As I can't
name the sheets from RS all they see is Sheet1, Sheet2, etc...
One of these files has over 200 sheets and the generic naming is a major
pain in the butt. Due to this problem most of the managers won't sign-off on
the costs for developing/deploying RS at our site.
Thanks
Phill
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:OA99z8MBFHA.3924@.TK2MSFTNGP10.phx.gbl...
> Sorry, there is no property in the RDL that would allow you to achieve
> customizing the sheet names.
> It is under consideration for inclusion in a future release.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "PR" <PR@.discussions.microsoft.com> wrote in message
> news:FEDD440A-49E9-4F2F-9DA3-08CC995E2D7B@.microsoft.com...
>> Hi All
>> I have a very simple report that is grouped on a particular field. I have
>> also set the "Page Break at end property" set to true for this group. I
> wish
>> to set up a subscription that will email this report as an excel
> attachment.
>> The excel export shows the records of each group in a separate sheet -
>> Exactly the way I need. What I also want is that the tabs of each sheet
>> should show the group field name as its name instead of sheet1, sheet2
>> etc....
>> Is there any way to achieve this ?
>> Thanks
>
>|||I use the document map facility to get aroung this. It exports as the front
sheet of the excel file and clicking on each title takes you to the correct
sheet.
"news.microsoft.com" <pcarter_NOT_AT_SPAM_bellpotter.com.au> wrote in
message news:ueUeHQPBFHA.2624@.TK2MSFTNGP11.phx.gbl...
> Robert
> Can it be moved off the consideration list onto the must do list?
> Providing the functionality to output to multiple sheets without the
> ability to dynamically name those sheets makes multiple sheets pretty
> pointless.
> I have one report that generates an Excel file for state managers. The
> file contains a seperate sheet for each consultant in their state. As I
> can't name the sheets from RS all they see is Sheet1, Sheet2, etc...
> One of these files has over 200 sheets and the generic naming is a major
> pain in the butt. Due to this problem most of the managers won't sign-off
> on the costs for developing/deploying RS at our site.
> Thanks
> Phill
>
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:OA99z8MBFHA.3924@.TK2MSFTNGP10.phx.gbl...
>> Sorry, there is no property in the RDL that would allow you to achieve
>> customizing the sheet names.
>> It is under consideration for inclusion in a future release.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "PR" <PR@.discussions.microsoft.com> wrote in message
>> news:FEDD440A-49E9-4F2F-9DA3-08CC995E2D7B@.microsoft.com...
>> Hi All
>> I have a very simple report that is grouped on a particular field. I
>> have
>> also set the "Page Break at end property" set to true for this group. I
>> wish
>> to set up a subscription that will email this report as an excel
>> attachment.
>> The excel export shows the records of each group in a separate sheet -
>> Exactly the way I need. What I also want is that the tabs of each sheet
>> should show the group field name as its name instead of sheet1, sheet2
>> etc....
>> Is there any way to achieve this ?
>> Thanks
>>
>|||I agree with you Phill... my company likes seamless transitions. Reporting
Services would've been the easiest solution if we had control on the
sheetnames.
I'm keeping my fingers crossed that this is included in the SP2 that's going
to be realeased soon (mid-April last I read). I cannot see the funcitonal
enhancements list because it is in the beta site which I don't have access
to.
Pete
"news.microsoft.com" wrote:
> Robert
> Can it be moved off the consideration list onto the must do list?
> Providing the functionality to output to multiple sheets without the ability
> to dynamically name those sheets makes multiple sheets pretty pointless.
> I have one report that generates an Excel file for state managers. The file
> contains a seperate sheet for each consultant in their state. As I can't
> name the sheets from RS all they see is Sheet1, Sheet2, etc...
> One of these files has over 200 sheets and the generic naming is a major
> pain in the butt. Due to this problem most of the managers won't sign-off on
> the costs for developing/deploying RS at our site.
> Thanks
> Phill
>
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:OA99z8MBFHA.3924@.TK2MSFTNGP10.phx.gbl...
> > Sorry, there is no property in the RDL that would allow you to achieve
> > customizing the sheet names.
> > It is under consideration for inclusion in a future release.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >
> >
> > "PR" <PR@.discussions.microsoft.com> wrote in message
> > news:FEDD440A-49E9-4F2F-9DA3-08CC995E2D7B@.microsoft.com...
> >> Hi All
> >>
> >> I have a very simple report that is grouped on a particular field. I have
> >> also set the "Page Break at end property" set to true for this group. I
> > wish
> >> to set up a subscription that will email this report as an excel
> > attachment.
> >> The excel export shows the records of each group in a separate sheet -
> >> Exactly the way I need. What I also want is that the tabs of each sheet
> >> should show the group field name as its name instead of sheet1, sheet2
> >> etc....
> >>
> >> Is there any way to achieve this ?
> >>
> >> Thanks
> >
> >
> >
>
>