June 11, 2008 at 11:30 am
Hi,
I have 12 tables for each month in sql 2000 instance....and I have 12 tables in sql 2005 instance...my task is to update all the 12 tables in sql 2005 with new year's data from sql 2000...I ve to truncate the old data and replace with new data...
1.is it possible with ssis??
2. If so, can we do that in a single package for all the tables or we ve to create 12 packages??
3. and procedure to do this?
can any one help me out?? any help would be appreciated....
June 11, 2008 at 1:39 pm
You can do it all in a single package. You will have 12 execute SQL tasks and 12 data flow tasks (one for each table).
OR
Use a Foreach loop container with the table name as the variable and then you'll only have one execute SQL task and one data flow task both contained inside the loop container.
June 11, 2008 at 2:14 pm
Thanks for ur reply...But my concern is all about comparing...we have a date field in each table...we ve to enter only the new records...how to compare?? I m new to SSIS...so I m in dilemma how to do this...
June 11, 2008 at 2:54 pm
Your original message indicated you wanted to truncate the data and I assumed you meant you wanted to truncate the table and just insert all of the records from 2000 to 2005.
Have you tried writing a Stored Procedure to do accomplish your goal?
June 11, 2008 at 3:02 pm
oh sorry abt that...but we just have to add the new data...may be we can do that using a stored procedure...my client wants me to use SSIS...
June 12, 2008 at 1:11 am
here is a bit of an "off the bat" suggestion (use it dont use it...)
Set up a linked server to your 2000 instance, and query for keys ( i would assume keys are in use).
Any keys not found on 2005 are to be transferred.
Now it becomes easy, when you query, use a lookup task to filter out the data you need
~PD
June 12, 2008 at 3:37 am
ok kool...Thanks a lot....let me try that...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply