Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 creating different staging table with data extensi

Author  Topic 

robin712
Starting Member

2 Posts

Posted - 2008-06-11 : 10:38:49
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

Hommer
Aged Yak Warrior

808 Posts

Posted - 2008-06-11 : 15:57:47
Here is a easy way.

The idea is to use one staging table in the package, then move data into each daily table after each load. When it is done, truncate the staging table for next day's load.

The Select INTO or Insert INTO part has to be done in dynamic sql since you want to dynamically name the table based on the date.

The other option will be to use a global variable in the SSIS for destination table name. There are many examples of this method out there.

Last but not least, a lot of relational pros will against the idea of one staging table per day design. The data should all be in one table with a column like dteLoaded to distinguish the date when that set of data is loaded. That way, you wouldn't face a trouble later on tracking which dates the data are loaded, i.e. how do you know that tblProd081207 exists in your db.
Go to Top of Page

robin712
Starting Member

2 Posts

Posted - 2008-06-11 : 18:09:15
right now We just have tblProd in our database but i want to load everyday in same file but saved differently with different date extension.

Go to Top of Page
   

- Advertisement -