EXCEL XP - Need xls not MHTNL

I am running EXCEL 2002 SP#. When I export to Excel it shows the name of the
report in quotes in the filename box (i.e.; "My Report.xls"), however, it is
default saving as an MHTML document.
Is there any way to force an .xls extension?
--
aleJust install Reporting Services SP1 and it will generate BIFF instead of
MHTML.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"ale" <ale@.discussions.microsoft.com> wrote in message
news:445A9480-F90D-44A4-96A7-18B173380F67@.microsoft.com...
> I am running EXCEL 2002 SP#. When I export to Excel it shows the name of
the
> report in quotes in the filename box (i.e.; "My Report.xls"), however, it
is
> default saving as an MHTML document.
> Is there any way to force an .xls extension?
> --
> ale

Excel To Sql Server 2005

HI Friends,

i was created on xls file in my dektop name (student) with 2 columns

sno sname marks

1 a 10

2 b 20

3 c 30

4 d 40

these records added to excel file only

Now : i created a table in sql server 2005

sno :numeric(18, 0)

sname :varchar(50)

marks :numeric(18, 0)

NOW in ssis package

1) i place excel datasource (selected the student excel sheet$1)

2) i placed a lookup controle and selected the server student table

Question : when we map the excel sno= server sno

ERROR : data type mismatch to any of the column ?

please give me the related steps

You need to use a data convertor component in the data flow and convert the columns to be the same as the destination. SSIS does not allow implicit conversion.|||

Hint: If you use the editor correctly there is no need to create the table in SQL.

Add a Excel source

Use Excel Editor to look for your xls file

Add an SQL Server Destination -->Use an OLE DB Provider

Establish the path from Excel to SQL Server

Confgiure destination using the OLE DB Provider

Use the SQL Destination Editor to generate the table -->important step

You are done.

Excel Output

I want to run a stored procedure which will run a specific query and put out
a monthly file each month. The name of the file with be "jan.xls" for Jan
data and then the next month it will be "feb.xls" for Feb. In addition, I
want to place the output in a folder which cooresponds month it is.
If I run a query, is there a way for me to put out an xls file? I see that
I can do this via DTS, but is this the only way?
I am guessing that I can invoke a DTS package from within a stored
procedure, but I am not sure how I would vary the output name. Maybe I woul
d
put out a standard name and then within the stored procedure generate the do
s
commands to rename and move the output as desired. Seems fairly complex for
something which seems to be fairly simple and common.
So I am kind of new to SQL, so I am just trying to get up to speed.
What approach would be recommended?
Thanks in advance for your assistance!!!I reckon the easiest way to do all this is with a ActiveX task within a DTS
package. You could use a SQL Task or ADO to extract the information to a
recordset, the Excel object library to manipulate and save the data and the
FileSystemObject object library to move/create files and directories. Within
VB Script you could build your filename dynamically too.
You would probably want to do some performance testing though.
"Jim Heavey" wrote:

> I want to run a stored procedure which will run a specific query and put o
ut
> a monthly file each month. The name of the file with be "jan.xls" for Jan
> data and then the next month it will be "feb.xls" for Feb. In addition, I
> want to place the output in a folder which cooresponds month it is.
> If I run a query, is there a way for me to put out an xls file? I see tha
t
> I can do this via DTS, but is this the only way?
> I am guessing that I can invoke a DTS package from within a stored
> procedure, but I am not sure how I would vary the output name. Maybe I wo
uld
> put out a standard name and then within the stored procedure generate the
dos
> commands to rename and move the output as desired. Seems fairly complex f
or
> something which seems to be fairly simple and common.
> So I am kind of new to SQL, so I am just trying to get up to speed.
> What approach would be recommended?
> Thanks in advance for your assistance!!!

Excel Output

I want to run a stored procedure which will run a specific query and put out
a monthly file each month. The name of the file with be "jan.xls" for Jan
data and then the next month it will be "feb.xls" for Feb. In addition, I
want to place the output in a folder which cooresponds month it is.
If I run a query, is there a way for me to put out an xls file? I see that
I can do this via DTS, but is this the only way?
I am guessing that I can invoke a DTS package from within a stored
procedure, but I am not sure how I would vary the output name. Maybe I would
put out a standard name and then within the stored procedure generate the dos
commands to rename and move the output as desired. Seems fairly complex for
something which seems to be fairly simple and common.
So I am kind of new to SQL, so I am just trying to get up to speed.
What approach would be recommended?
Thanks in advance for your assistance!!!I reckon the easiest way to do all this is with a ActiveX task within a DTS
package. You could use a SQL Task or ADO to extract the information to a
recordset, the Excel object library to manipulate and save the data and the
FileSystemObject object library to move/create files and directories. Within
VB Script you could build your filename dynamically too.
You would probably want to do some performance testing though.
"Jim Heavey" wrote:
> I want to run a stored procedure which will run a specific query and put out
> a monthly file each month. The name of the file with be "jan.xls" for Jan
> data and then the next month it will be "feb.xls" for Feb. In addition, I
> want to place the output in a folder which cooresponds month it is.
> If I run a query, is there a way for me to put out an xls file? I see that
> I can do this via DTS, but is this the only way?
> I am guessing that I can invoke a DTS package from within a stored
> procedure, but I am not sure how I would vary the output name. Maybe I would
> put out a standard name and then within the stored procedure generate the dos
> commands to rename and move the output as desired. Seems fairly complex for
> something which seems to be fairly simple and common.
> So I am kind of new to SQL, so I am just trying to get up to speed.
> What approach would be recommended?
> Thanks in advance for your assistance!!!

Excel Import Error

Hello,

I am trying out a simple SSIS package to import data from an xls into a table. For this I am using an Excel Source and an OLEDB Destination.
One of the fields is called Notes. In the Excel Source, I have specified the Notes columns as Unicode string [DT_WSTR]. This maps to a SQL Table column which is nvarchar(max)
The error messages that I get are :

[Excel Source [1]] Error: An OLE DB error has occurred. Error code: 0x80040E21.

[Excel Source [1]] Error: There was an error with output column "Notes" (272) on output "Excel Source Output" (9). The column status returned was: "DBSTATUS_UNAVAILABLE".

[Excel Source [1]] Error: The "output column "Notes" (272)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "Notes" (272)" specifies failure on error. An error occurred on the specified object of the specified component.

Could anyone please help me with this.

regards,

Satya

Hi Satya,

I'm no expert so this is a best guess as to what might be happening. It sounds as though there may be rows in the excel file where the Notes filed contains data which isn't compatible with the DT_WSTR data type. What i would be inclined to do first is the following:

Right click on the Excel data source object and bring up the advanced edit options. Select the Input and Output Properties tab and then the output columns folder under inputs and outputs treeview. You should (if i'm not mistaken) find a property for ErrorRowDisposition. Change this value to RD_IgnoreFailure.

If you now save and run the package does it execute as expected. If there are indeed rows which have caused problems these should have been ignored by the transformation.

Let me know if this helps.

Cheers,

Grant|||

Great workaround, I had the similar problem, but is there any way to avoid the error itself?

Thanks

Atul

Excel Import Error

Hello,

I am trying out a simple SSIS package to import data from an xls into a table. For this I am using an Excel Source and an OLEDB Destination.
One of the fields is called Notes. In the Excel Source, I have specified the Notes columns as Unicode string [DT_WSTR]. This maps to a SQL Table column which is nvarchar(max)
The error messages that I get are :

[Excel Source [1]] Error: An OLE DB error has occurred. Error code: 0x80040E21.

[Excel Source [1]] Error: There was an error with output column "Notes" (272) on output "Excel Source Output" (9). The column status returned was: "DBSTATUS_UNAVAILABLE".

[Excel Source [1]] Error: The "output column "Notes" (272)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "Notes" (272)" specifies failure on error. An error occurred on the specified object of the specified component.

Could anyone please help me with this.

regards,

Satya

Hi Satya,

I'm no expert so this is a best guess as to what might be happening. It sounds as though there may be rows in the excel file where the Notes filed contains data which isn't compatible with the DT_WSTR data type. What i would be inclined to do first is the following:

Right click on the Excel data source object and bring up the advanced edit options. Select the Input and Output Properties tab and then the output columns folder under inputs and outputs treeview. You should (if i'm not mistaken) find a property for ErrorRowDisposition. Change this value to RD_IgnoreFailure.

If you now save and run the package does it execute as expected. If there are indeed rows which have caused problems these should have been ignored by the transformation.

Let me know if this helps.

Cheers,

Grant|||

Great workaround, I had the similar problem, but is there any way to avoid the error itself?

Thanks

Atul

|||You should use a data conversion to convert the strings to unicode

excel generated xml file to sql

hi,
i hv a report generated from a web-based application. although the extension
is '.xls', this is really an xml doc (am i correct?). i say this because if
i
'save as' the file, the extension that appears is 'XML spreadsheet'.
i don't want to open the file and save it as an excel file, because that
would mean there will be a manual process in my script. what is the best way
to import this file directly to sql? i tried DTS but DTS cannot recognize th
e
file.
thanks for your help!Do you want to import the XML as a BLOB or put it into tables?
Michael
"juvethski" <juvethski@.discussions.microsoft.com> wrote in message
news:A2B28AB0-A366-4E5E-AF3A-224E5E59239D@.microsoft.com...
> hi,
> i hv a report generated from a web-based application. although the
> extension
> is '.xls', this is really an xml doc (am i correct?). i say this because
> if i
> 'save as' the file, the extension that appears is 'XML spreadsheet'.
> i don't want to open the file and save it as an excel file, because that
> would mean there will be a manual process in my script. what is the best
> way
> to import this file directly to sql? i tried DTS but DTS cannot recognize
> the
> file.
> thanks for your help!|||i want to put it into a table. thanks in advance for the help.
cheers.
"Michael Rys [MSFT]" wrote:

> Do you want to import the XML as a BLOB or put it into tables?
> Michael
> "juvethski" <juvethski@.discussions.microsoft.com> wrote in message
> news:A2B28AB0-A366-4E5E-AF3A-224E5E59239D@.microsoft.com...
>
>|||i want to put it into a table. the table can be existing, or not yet
existing. thanks in advance.
cheers
"Michael Rys [MSFT]" wrote:

> Do you want to import the XML as a BLOB or put it into tables?
> Michael
> "juvethski" <juvethski@.discussions.microsoft.com> wrote in message
> news:A2B28AB0-A366-4E5E-AF3A-224E5E59239D@.microsoft.com...
>
>|||You may consider use Bulkload from Sqlxml or using OpenXml i nT-SQL if your
file size is not big.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"juvethski" <juvethski@.discussions.microsoft.com> wrote in message
news:A2B28AB0-A366-4E5E-AF3A-224E5E59239D@.microsoft.com...
> hi,
> i hv a report generated from a web-based application. although the
> extension
> is '.xls', this is really an xml doc (am i correct?). i say this because
> if i
> 'save as' the file, the extension that appears is 'XML spreadsheet'.
> i don't want to open the file and save it as an excel file, because that
> would mean there will be a manual process in my script. what is the best
> way
> to import this file directly to sql? i tried DTS but DTS cannot recognize
> the
> file.
> thanks for your help!

excel generated xml file to sql

hi,
i hv a report generated from a web-based application. although the extension
is '.xls', this is really an xml doc (am i correct?). i say this because if i
'save as' the file, the extension that appears is 'XML spreadsheet'.
i don't want to open the file and save it as an excel file, because that
would mean there will be a manual process in my script. what is the best way
to import this file directly to sql? i tried DTS but DTS cannot recognize the
file.
thanks for your help!
Do you want to import the XML as a BLOB or put it into tables?
Michael
"juvethski" <juvethski@.discussions.microsoft.com> wrote in message
news:A2B28AB0-A366-4E5E-AF3A-224E5E59239D@.microsoft.com...
> hi,
> i hv a report generated from a web-based application. although the
> extension
> is '.xls', this is really an xml doc (am i correct?). i say this because
> if i
> 'save as' the file, the extension that appears is 'XML spreadsheet'.
> i don't want to open the file and save it as an excel file, because that
> would mean there will be a manual process in my script. what is the best
> way
> to import this file directly to sql? i tried DTS but DTS cannot recognize
> the
> file.
> thanks for your help!
|||i want to put it into a table. thanks in advance for the help.
cheers.
"Michael Rys [MSFT]" wrote:

> Do you want to import the XML as a BLOB or put it into tables?
> Michael
> "juvethski" <juvethski@.discussions.microsoft.com> wrote in message
> news:A2B28AB0-A366-4E5E-AF3A-224E5E59239D@.microsoft.com...
>
>
|||i want to put it into a table. the table can be existing, or not yet
existing. thanks in advance.
cheers
"Michael Rys [MSFT]" wrote:

> Do you want to import the XML as a BLOB or put it into tables?
> Michael
> "juvethski" <juvethski@.discussions.microsoft.com> wrote in message
> news:A2B28AB0-A366-4E5E-AF3A-224E5E59239D@.microsoft.com...
>
>
|||You may consider use Bulkload from Sqlxml or using OpenXml i nT-SQL if your
file size is not big.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"juvethski" <juvethski@.discussions.microsoft.com> wrote in message
news:A2B28AB0-A366-4E5E-AF3A-224E5E59239D@.microsoft.com...
> hi,
> i hv a report generated from a web-based application. although the
> extension
> is '.xls', this is really an xml doc (am i correct?). i say this because
> if i
> 'save as' the file, the extension that appears is 'XML spreadsheet'.
> i don't want to open the file and save it as an excel file, because that
> would mean there will be a manual process in my script. what is the best
> way
> to import this file directly to sql? i tried DTS but DTS cannot recognize
> the
> file.
> thanks for your help!