Exporting query result into excel file

Hi i am newbie . I want to transfer the query result into excel file throgh asp code. can any one help me out.

Also how can i automate my routing task that is data in excel to be updated in the table which is in SQL server. Say x.xls is the daily file maintaned by me and i want y table in sql server to be appended once this file is saved.Writing a macro in Excel could be one of the solutions!!!sql

Exporting From SQL Server To MSACCESS

Hi,

I Need to make a script or task that programmatically export data from SQL Server to Access File , and how to call the script from the .NET?

Create SSIS package using Import/Export Wizard, you can then run this package from any .NET application either by running DTEXEC or using SSIS object model - see SQL Books Online for sample syntax of both.

Exporting Binary Data from MSSQL to MySQL

I have a MSSQL table with Primary Keys, Company_Id, set as a binary
value. I used the dts Transform Data Task and to create a new table in
MySQL.
The default create code comes up with:
CREATE TABLE `New Table` (
`Web_Edited` tinyint unsigned NULL,
`Company_Id` date NOT NULL,
`Rn_Descriptor` varchar (80) NULL, ...
Why is the Company_Id set to date and not varbinary?
Secondly, if I change the create statement
...`Company_Id` date NOT NULL, ...
to
... `Company_Id` varbinary(8) NOT NULL, ...
then on the Transform Data Task's Destination Tab, the Company_Id is
displayed as longtext. And then I specify that the Company Id column
is the only column I wanted in the transformation, and executed the
trasformation.
I get an error stating this:
The number of failing rows exceeds the maximum specified.
TransformCopy 'DTSTransformation__1' conversion error: General
conversion failure on column pair 1(source column 'Company_Id'
(DBTYPE_BYTES), destination column 'Company_Id' (DBTYPE_STR))
But I already specified the Company_Id column as a binary, and I even
checked the mysql table's strucutre and it was also set to varbinary(8)
Please help.Hi
I don't know much about MYSQL, but this may be a driver issue, are you using
ODBC? If mysql can import text files you may want to try using BCP and then
importing from MYSQL.
John
"datenisis@.gmail.com" wrote:

> I have a MSSQL table with Primary Keys, Company_Id, set as a binary
> value. I used the dts Transform Data Task and to create a new table in
> MySQL.
> The default create code comes up with:
> CREATE TABLE `New Table` (
> `Web_Edited` tinyint unsigned NULL,
> `Company_Id` date NOT NULL,
> `Rn_Descriptor` varchar (80) NULL, ...
> Why is the Company_Id set to date and not varbinary?
> Secondly, if I change the create statement
> ...`Company_Id` date NOT NULL, ...
> to
> ... `Company_Id` varbinary(8) NOT NULL, ...
> then on the Transform Data Task's Destination Tab, the Company_Id is
> displayed as longtext. And then I specify that the Company Id column
> is the only column I wanted in the transformation, and executed the
> trasformation.
> I get an error stating this:
> The number of failing rows exceeds the maximum specified.
> TransformCopy 'DTSTransformation__1' conversion error: General
> conversion failure on column pair 1(source column 'Company_Id'
> (DBTYPE_BYTES), destination column 'Company_Id' (DBTYPE_STR))
> But I already specified the Company_Id column as a binary, and I even
> checked the mysql table's strucutre and it was also set to varbinary(8)
> Please help.
>sql

Exporting Binary Data from MSSQL to MySQL

I have a MSSQL table with Primary Keys, Company_Id, set as a binary
value. I used the dts Transform Data Task and to create a new table in
MySQL.
The default create code comes up with:
CREATE TABLE `New Table` (
`Web_Edited` tinyint unsigned NULL,
`Company_Id` date NOT NULL,
`Rn_Descriptor` varchar (80) NULL, ...
Why is the Company_Id set to date and not varbinary?
Secondly, if I change the create statement
...`Company_Id` date NOT NULL, ...
to
... `Company_Id` varbinary(8) NOT NULL, ...
then on the Transform Data Task's Destination Tab, the Company_Id is
displayed as longtext. And then I specify that the Company Id column
is the only column I wanted in the transformation, and executed the
trasformation.
I get an error stating this:
The number of failing rows exceeds the maximum specified.
TransformCopy 'DTSTransformation__1' conversion error: General
conversion failure on column pair 1(source column 'Company_Id'
(DBTYPE_BYTES), destination column 'Company_Id' (DBTYPE_STR))
But I already specified the Company_Id column as a binary, and I even
checked the MySQL table's strucutre and it was also set to varbinary(8)
Please help.Hi
I don't know much about MYSQL, but this may be a driver issue, are you using
ODBC? If MYSQL can import text files you may want to try using BCP and then
importing from MYSQL.
John
"datenisis@.gmail.com" wrote:
> I have a MSSQL table with Primary Keys, Company_Id, set as a binary
> value. I used the dts Transform Data Task and to create a new table in
> MySQL.
> The default create code comes up with:
> CREATE TABLE `New Table` (
> `Web_Edited` tinyint unsigned NULL,
> `Company_Id` date NOT NULL,
> `Rn_Descriptor` varchar (80) NULL, ...
> Why is the Company_Id set to date and not varbinary?
> Secondly, if I change the create statement
> ...`Company_Id` date NOT NULL, ...
> to
> ... `Company_Id` varbinary(8) NOT NULL, ...
> then on the Transform Data Task's Destination Tab, the Company_Id is
> displayed as longtext. And then I specify that the Company Id column
> is the only column I wanted in the transformation, and executed the
> trasformation.
> I get an error stating this:
> The number of failing rows exceeds the maximum specified.
> TransformCopy 'DTSTransformation__1' conversion error: General
> conversion failure on column pair 1(source column 'Company_Id'
> (DBTYPE_BYTES), destination column 'Company_Id' (DBTYPE_STR))
> But I already specified the Company_Id column as a binary, and I even
> checked the MySQL table's strucutre and it was also set to varbinary(8)
> Please help.
>

Export to Mulitple Sheets in Excel

Hi,

I want to export data from SQL to Excel using Data Flow Task. But Excel file can only contains max. of 65,536 rows per sheet. How can I transfer data to multiple sheets if SQL table contains more that 650000 rows.

I am also looking for an answer to this. Very good question....

export to excel - formatting number output

Hi all,

Once again, SSIS is giving me a 'F.U.N.' time (ask for definition of the F.U.N. acronym another time ).

I

have a relatively simple task - create an excel spreadsheet with 3

columns of data - Id, Description and Sales. ID and Description are

text, sales is int.

So my SP aggregates and creates my resultset

in my OLE DB Source in the Data Flow. It proceeds to the Excel

destination, and that all seems fine. My issue is that the data is

being written as text. Looking at the excel destination in Advanced

editor:
the Excel Destination Input, Input columns are formatted as

I expected: DT_WSTR 8 for the ID, DT_WSTR 100 for the Description and

DT_I4 for the Sales.
Excel Destination Input, External columns refuse to fall in line, though. They are all listed as DT_WSTR 255.

The

target excel spreadsheet is being created from a template file. That

template file has header columns. The target column for the Sales has

the entire column formatted to NUMBER (0 decimals). Yet to now avail.

When

I check the spreadsheet, the column has retained the cell formatting,

and I have a 'I' pop-up to inform me that 'someone' has inserted text

data into the number column (even though the data IS number).

Since

the SP spits out INT, it isn't a case of receiving a text value, imho.

While trying to change the external column data type in the advanced

editor, SSIS is quite happy to let me change the value for the Sales

output to DT_I4, apply, and ok. Then, when I open it immedaitely

aftgerwards, it has reverted to the DT_WSTR's! AArrgh. If is can't

handle it, at least tell me when I try and change it. don't let me

change it, and then revert back without telling me! Grumble grumble...

So - anyone know a way around this?

There are no perfect solutions for many behaviors of Jet with the Excel driver. However the driver is always happiest when the destination "table" has been created by the driver itself, which could be done through the Excel Destination or the Import and Export Wizard or ADO code. Excel column formatting seems to be disregarded completely.

The most comprehensive list of Excel driver-related issues is in 257819 How To Use ADO with Excel Data from Visual Basic or VBA
http://support.microsoft.com/default.aspx?scid=kb;EN-US;257819. Many of these will surface in SSIS in one form or another.

-Doug

|||

Hi Douglas,

As I read

the links I’ve found so far, IMEX=1 specifies text? Is there a way to have the

IMEX setting per column? If so, what would the correct IMEX setting for numeric

be?

TIA

|||

IMEX makes all data one type. By default that's Text (based on a Registry entry), and I've never heard of anyone changing it.

Please trust me on this -- if you want more predictable behavior, create the destination table in Excel by using the Excel driver. There appear to be hidden Excel column or cell settings that the driver is aware of but that are not exposed in the Excel UI (and not through cell formatting options).

-Doug

|||Thanks Douglas. I'll try that approach. It would seem to imply something like this:

CREATE TABLE `Sales Rank` (

`CatalogueItemReference` NVARCHAR(8),

`DescriptionText` NVARCHAR(100),

`TotalSalesForPeriod` INTEGER

)

that seem about right?|||

I'm not sure whether Jet SQL recognizes NVARCHAR but always have trouble finding the list. Somewhere on your disk there's a JETSQL40.CHM or SQLJET40.CHM or similar. I'd be more inclined to let the Import and Export Wizard or the Excel Destination write the statement for me.

-Doug

|||Thanks Doug, the sample I wrote was a slightly modified version of the

table create that the ssis excel destination task generated. I'll post

if it works :-)