Dynamically Change Table Name

[monkeybarfan]

Ok, here is what I need to do...

Each day our telephony system (Nortel Symposium) stores event data in tables called, for example, eCallByCallStat20061031. These tables only stay on the system for 2 months and each day the oldest one gets dropped. For several months I have been manually running a DTS package to append all the October tables to, for example, eCallByCallStat_Oct2006.

Essentially I have been appending all the daily tables into one consolidated table per month.

Obviously, doing this manually every few weeks is not good practice and I want to automate the process.

I have automated numerous DTS packages where data is moved between static tables and I have successfully made a few slight amendments with SQL Tasks, but now I have exhausted all my expertise.

I guess the steps I really need the package to go through are...
1. check to see if the table, such as eCallByCallStat_Nov2006 already exists.
2. If it already exists go to step 4.
3. If it doesn't exist then create it.
4. Append eCallByCallStat20060101 to the table above

I would then schedule this package to run daily.

I have tried all sorts of GetDate functions and tried creating variables and using Dynamic Properties Tasks, but each time I have encountered errors and these tightwads I work for won't even purchase books on the subject.

Can anyone help?
Cheers

 

[AlexCuse]

I think you may be able to skip checking for the table, provided you have a daily table for every single day. All you would need to do is look at the 'day' portion of your source table name. If this is 01, then create the new table, if not continue on.

As far as naming your new table, you could use something like this to get the date portion:

select left(datename(month, getdate()), 3) + datename(year, getdate())

The destination table could be determined by the same query, only use

dateadd(dd, -1, getdate())

(as I assume you will be bringing in previous day's files)

Hopefully this will help get you started.

Alex





A wise man once said
"The only thing normal about database guys is their tables".

 

[monkeybarfan]

Thanks Alex they parse fine when I test them as individual statements, but where do I implement them in a package as a whole?

Are they put separately into SQL Tasks? Do I need to use the statements to create variables?

Talk to me as if I'm a child

 

[monkeybarfan]

Another problem is that the code datename(day, getdate()) will return 9 but the table name will contain 09. Is there an easy way of adding a zero if it's less than 10 by using T-SQL?

 

[AlexCuse]

You will need to use them to set values for variables. You will then need to use these variables to assign your source and destination table names.

If you want to make sure it's 09, try setting up a SQL task, with a string global variable set as the output for hte value. The SQL task could contain this:

select right(replicate(0,1) +  convert(varchar(2), datepart(dd, dateadd(dd, -1, getdate()))), 2)

What this does is append a zero to the front of your datepart, then take the right 2 characters. The convert varchar(2) ensures that your datepart value will be treated as a character string rather than an integer. If it were treated as an integer, you would get 0 + 8 = 8 rather than the 08 you need. You also need to be certain your output global variable is set to 'String' type, or else all this will be for nothing and you'll end up with 8 anyway.

(all of this is still assuming you will be loading previous day's table)

Hope this helps,

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[monkeybarfan]

Thanks

when I have created the variables, how do I get the Transform Data Task to use these variables?

I have tried selecting the SQL Query part on the Source Tab and using the following code:

DECLARE @inputeventtable_gv VARCHAR

@inputeventtable_gv = inputeventtable_gv

blue.dbo. + @inputeventtable_gv

It parses fine but when I click on ok I get Cannot generate SSPI context

?????

 

[AlexCuse]

I don't think that you can use variables within your source query.

Global variables should be created under your package's properties (as they are present for the whole package). You can use your front end (or activeX) to set values, and use activeX to manipulate things like data source.

Check out this link, it will probably help you out a bit with understanding how the scripting stuff works:

http://www.sqldts.com/default.aspx?6,103,246,0,1

Hope this helps,

Alex



A wise man once said
"The only thing normal about database guys is their tables".