How can I create an excel file dynamically and export data to a specific
sheet from sql server dts?.

Karunakaran

In design view for creating a DTS package create a connection for your
SQL server that will be the data source. Create an Excel connection
and specify the excel filename and location that you would like to
output to.
Now click on your SQL server source connection, then click Transform
Data Task, then click the Excel Connection to set it as your
destination connection.

Now double click the workflow link you just created to open the
properties. Under the Destination tab you can specify the sheet name
in the "Table" field. For example I have a destination excel file with
three sheets, the Table drop down menu displays these three sheet names
preceded by a $ character. If your sheets was named Bob, then the
destination table field would read $Bob

The create button should create new sheets for you I think as well.

Have fun.

Set the connection to the desired file name, and then create the sheet.
Sheets are analogous to tables, so you can use a create table statement
inside an Exec SQL Task that references the Excel connection. This will
force the file to be created as well if it does not already exist at
run-time.

Use the designer to help get the syntax right, but for an example-

CREATE TABLE `AdamAntTest` (
`RowNumber` Long ,
`EventClass` Long ,
`TextData` LongText ,
`SPID` Long )

Note the slanted single quote is used, and Excel types not SQL.


"Karunakaran" <Karunakaran (AT) discussions (DOT) microsoft.com> wrote