Hi team, i have to create a process which will create a new table every day. and every one hour it will be loaded with the last one hour data what should the approach. i have approach in mind to create a table every day at 12:00 a.m. based on the getdate and from next hour onwards load this table until next 12:00 A.M. pls give some suggestions. Regards, Eshwar

Could you please provide a bit more detail on what it is you're trying to acheive i.e. why do you need to create a new table every day? Can't you just have a DATE column in the table which will allow you to retrieve records for a given day.Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD

thanks Jeff. my requirement is, i have a table which i need to backed up every one hour and every day i have to create a new table. so at 12:00 A.M. every day i have to create a table for that in the first step i have to check the time and if it is 12:00 i have to create a new table suffixed with todays date and from next hour onwards i have take the backup of the data to todays table till next 12:00A.M. and create new table at 12:00 A.M. i am not getting how to check if it 12:00 A.M. (which task to be used, if it is a script task i am pretty bad at vb script). i guess its more clear Regards, Eshwar.

You have following options: 1. Use SQL Agent task to create table, don't use SSIS at all. 2. Use SQL Agent to start SSIS package at 12:00AM. 3. Use standatd SQL Script task and use "WAITFOR TIME" in SQL Task (package needs to run 24x7)

thanks Jeff. my requirement is, i have a table which i need to backed up every one hour and every day i have to create a new table. So once you have backed up the table, is it ever used again or is it just for backup purposes?Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD

its a log table Jeff if it is like event_log table every day at 12:00 A.M. i have to create event_log_24052011 table and from next hour onwards i have to backup the data to this table from event_log table. regards, Eshwar

its a log table Jeff if it is like event_log table every day at 12:00 A.M. i have to create event_log_24052011 table and from next hour onwards i have to backup the data to this table from event_log table. regards, Eshwar You didn't actually answer my question. :-) I'm trying to figure out whether it would be better to schedule a database backup every hour however this won't be any use to you if you need the ability to quickly check past records as you'd need to restore the backup first. If you need the ability to view data in previous backups quickly, then Piotr's suggestions are goodJeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD

Why don't you just add an extra column to the table that contains a day indicator. Then you can use the same table...Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

Why don't you just add an extra column to the table that contains a day indicator. Then you can use the same table... Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com I originally told him to add a DATE column but that doesn't meet their requirements so it is unlikely adding a day indicator will meet their requirements eitherJeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD

Why don't you just add an extra column to the table that contains a day indicator. Then you can use the same table... Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com I originally told him to add a DATE column but that doesn't meet their requirements so it is unlikely adding a day indicator will meet their requirements either Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD but it seems an odd requirement... a new table every day (the requirement is not clear to me). But if you really want it, this could be a solution: - Schedule the package every hour. - Add a precedence constraint with an expression that onces a day executes the create table statement - And you have to come op with an expression that changes the destination table Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

I completely agree. The OP is creating a lot of extra work for themselfJeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD

Hi Eshwar, Flow chart for the package Create a package Add execute sql task to get the current time check the current time if it is 12.am then execute another sql task to create a table else execute the data flow task contain data load process. data source is the event log table, fetch data for an hour construct the target table name using variable and map it to the destination table Finally, create a job in SQL Agent with this package and schedule the package execution for every hour. that is all. :) Thanks Ayyappan Thangaraj UG Lead, Puducherry, http://SQLServerRider.blogspot.com