DTS Package help with naming table

[MMichaels]

I am working on a Windows 2000 box with SQL Server 2000. I have a DTS package that I want to create that I want to run the first of every month. The goal I am trying to achieve is that I want it to automatically name the table I am copying with the day before's date. So, if I was running it today, I would want the DTS Package to run and throw a table in my Archive database and name it "member022508", the table is originally called "member". I tried a few things, and with my lack of experience, nothing worked. I tried naming the table member(getdate()), it took it literal... is there a way to do this? Am I being clear?

 

[AlexCuse]

you want to use dynamic sql for this.

Something like

[COLOR=blue]declare[/color] @sql nvarchar(4000)

[COLOR=blue]set[/color] @sql = [COLOR=red]'select * into member'[/color] + [COLOR=#FF00FF]convert[/color]([COLOR=blue]varchar[/color](10), [COLOR=#FF00FF]getdate[/color](), 112) + [COLOR=red]' from member'[/color]

[COLOR=blue]exec[/color] sp_executesql @sql

Hope this helps,

Alex

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page