sql server dts: assign excel file to a global variable name -- sql server dts

assign excel file to a global variable name

chris
3/25/2004 2:04:29 PM

sql server dts:

sql2k sp3

Every day I need to run an sql query and shove those
results into an excel spreadsheet. So what I wanted to do
was have a dts package that:

1 SQL connection. 1 Excel connection.
1; Active X Script Task--Got todays date, replcaced
the "/"'s with "", and then stuck a .xls on the end of it.
2; Dynamic Props Task-- Assigned that new value to a
Global Variable.
3; Exec SQL Task-- Ran the query to get the data I need.
4; Shoved that data into an Excel file named the value of
the Global Variable.

The problem here is that in order to use an Excel
connection, I need to specify a file name. Thats tough
since I havent created doesn't get created until the
package runs.

Any ideas???

Re: assign excel file to a global variable name

Allan Mitchell
3/26/2004 6:45:20 AM

Is the metadata dynamic?

You can create tables/named ranges in Excel using an ExecuteSQL task.

You will then need to do the mappings yourself between the source and
destination.

If the metadata is the same then you could create a template spreadsheet and
use a filesystem copy dynamically naming the destination file

How to manipulate the Execute Process task.
(http://www.sqldts.com/default.aspx?251)

you woul then point your connection object to this new file

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

[quoted text, click to view]