creating new statging table for everyday with date extension

Hi All,

I am extracting source data which is in txt fille to OLE DB destination. But data of each day I want to save in different staging table. For Eg; tblProduct20081206, tblProduct20081207. How can it be done. I have seen lots of posting and script when destination is Txt. I want to use same table for staging but want to create different table for each day with adding date extension.

Please Help

You have to use the dynamic sql to create the table...
declare @sql Varchar(1000), @tablename Varchar(100)
select @tablename = 'tblProduct'+Convert(char(8), getdate(),112)
select @tablename
select @sql = 'create table '+@tablename+' (col1 int, col2 int...)'
exec (@sql)
OR
You can have a regular table without date stamp and rename it once populated with date stamp...

Without knowing your design constraints another option that may work for you would be to dump all your source data into the same destination table and use a view to partition or seperate the data by day.