Table Name Changes Daily

[monkeybarfan]

Hi

I am trying to back up all the Symposium telephony system data onto our SQL Server using DTS.

It's straightforward enough for the majority of the tables. The daily eCallByCallStat tables are causing me problems. In case anyone isn't aware each day Symposium creates a new table eg eCallByCallStat20051108.

I need to append each daily table into one table on the SQL Server and then schedule the job. At present I have been doing it manually by changing the source data to the new table. I think I need something like a global variable to enable automation of the job.

I have a piece of script...

Function Main()
dim X
dim Y
dim W
W = dateadd("d","-1",date())
X = left(W,2)+mid(W,4,2)+right(W,4)
Y = "eCallByCallStat"+X
msgbox Y
Main = DTSTaskExecResult_Success
End Function

This provides me with a message box that displays yesterday's date. I could maybe use this if I ran the job daily for the previous day but I am unsure of where to fit it in the DTS package. SQL task, Active X or Dynamic Properties Task, for instance.

Can anyone provide some step by step instructions on where it fits in? (I'm not a programmer!) I'm also open to other suggestions.

 

[mrdenny]

Use the Dynamic Properties task within DTS to change the table name you are dealing with. You can use a SQL Query to get the value you will populate.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[stiej]

You on the right lines I'd say. You've already come up with the right table name using your function.

But store the result of the function in a global variable at the end of the function, create the GV inside the DTS designer environment (right click a blank area and choose properties, then the global varibales tab, and create a new [string] one).

then use a dynamic properties to set the source data to the value of the GV.

So in sequence it will be like this....run your function to define the tablename which populates your GV with the result. Run the Dynamic Properties task to set the source of the table to the value of the GV, then when your transform does it's stuff, it will be picking the correct table each time.

 

[monkeybarfan]

Thanks mate, will give this a go when the current workload permits!