November 21, 2007 at 7:50 am
I'm Kinda new to SQL, trying to create a SSIS package to transfer data between two databases. First I will want to truncate all tables of the old database, then reload data from the new database to the old database. Any tips will be greatly appreciated.
November 21, 2007 at 9:02 am
That's pretty simple if your schema is close to the same.
In the control flow, you will need an execute sql task and a data flow task (with a control flow arrow between them). Then when you double-click on the data flow task you will add your OLEDB source and destination components.
Even if you are very familiar with DTS, the tools is so much different that you should go through some of the MS sample projects and I would really recommend reading a book on SSIS. It is pretty flexible, but with that comes a degree of complication.
Also, after you have read up on the subject, think through your designs carefully at first. With DTS, truncating a table and re-populating it was probably the most efficient method in many cases, SSIS is pretty good at comparing data in doing inserts, updates, and deletes. So, truncating and re-populating is somewhat out-of-date thinking.
There is a great table comparison component available for free here:
http://www.sqlbi.eu/Projects/TableDifference/tabid/74/language/en-US/Default.aspx
It does some of the same things the Slowly Changing Dimension component does, but in many cases is much faster.
That is another good tip - if you are doing something not supported by the default components in SSIS, but it seems like something reasonably logical, look for a component that someone has already written for you.
November 21, 2007 at 9:59 am
Appreciate, will give it a try
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply