sql server dts: I need to set up 2 exports in DTS, they both go to an Excel File. In
both cases I need the file name to be dynamic, i.e.
company_<todaysdate>.xls

1. The first one will be run weekly and dropped into a
folder on the shared server. What I need to do is make the file name
dynamic and have the date drop in after a bit of text. For example,
TEXT_09192006.xls.

2. The next one will run on the second of the month, and
will contain information on sales for the previous month, For example
I need the name to be TEXT_AUGUST.xls.

Simple enough...or so it would seem.

I have hunted around these groups for a solution for a few days now,
and all I seem to find is ActiveX and VBScript solutions. Both of which
I have no experience with, none, nada, zip ziltch. While i would be
more then willing to learn, i would need a complete discription on how
to do it, so if anyone can point me in the right direction, it would be
appreciated. if not. I am very interested in knowing if it is possible
to drop in a sql string into the destination portion of DTS wizard.

Thank you In advance for your time

Cavan

[quoted text, click to view]

Are you familiar with SSIS expressions?

Assuming you know how to output to an Excel sheet with a fixed name that
you've typed in, it's fairly easy to make the name dynamic. What you're
doing is causing the task to evaluate an expression rather than just use
what you've typed in.

(caveat: I haven't done output-to-excel yet, so this will be a bit generic
and from memory...)

On the task that creates the excel object, look in the editing dialog or the
properties dialog for "Expressions". Open that up, and drop down the list of
properties you can override. One will probably be "filename" or something
similar. Click the [...] button on the expression box for it to open the
expression editor.

You can fairly easily compose an expression similar to:

"TEXT_" + (str(2)) month(date()) + (str(2)) day(date()) + (str(4))
year(date()) + ".XLS"

....though I don't know how easily you could get the month *name*...

Play around with it, it should be fairly obvious once you figure it out.

Lots of tasks have lots of properties you can override with an expression.

Sadly, the expression library is rather limited compared to a full
programming language.

If you want to reuse a calculated value in lots of places, then create a
package variable whose value is calculated from an expression, then just use
that variable in the property expressions. Then you only need to make
changes in one place - the variable.

HTH.

--
John Hardin KA7OHZ
Senior Applications Developer, RetailCRM Development
web: http://www.epicor.com
voice: (425) 672-1304
fax: (425) 672-0192
email: <jhardin@epicor.com>
EPICOR|CRS Retail Solutions Division
3400 188th Street SW, Suite 185
Lynnwood, WA 98037 USA
Worldwide Headquarters 18200 Von Karman, Suite 1000, Irvine CA 92612 USA
------------------------------------------------------------------------
The first time I saw a bagpipe, I thought the player was torturing an
octopus. I was amazed they could scream so loudly.
------------------------------------------------------------------------