June 23, 2005 at 9:14 am
I am very new to DTS and have done a few very small packages but need some suggestions on a existing package. Currently we have a data pump (DTS package) that run every hours pulls information from another server does some date converstion and adds information based on what it is pulling from server 1 and we store it on server 2 so we can access it for reporting make changes to it or add new data. The first server collects data from our machines that include order numbers, run, downtime, what it went down for and anything to do with production. Each event has a start and end time so there could be several events in a 1 hour period. Currently the pump drops the tables on server 2 and re-creates the tables with the information from server 2 and the additional information that is changed/converted. Is there a better way to do this it seems to take a lot of resources to drop these tables every hour and recreate them since I have been working in this department the package time is getting longer and longer. Is it possible to just collect the new data from server 1 and pass it to server 2 to update it.
Kurt Kracaw
June 23, 2005 at 11:08 am
You are describing a typical data warehouse task.
The right and proper thing to do is:
Create a staging table with the relevane columns required for import ONLY.
Truncate the staging table.
Take the data from your source table into a staging table.
Write a stored procedure to do an insert from the staging table into the destination table using (NOT EXISTS) which is the quickest way
That's all there is to it and you can do it in one package. However, you need to have a unique identifier on the table to accomplish this. Also, if any existing data is changed you will need a last updated column or something similar, otherwise you need to write update statements for everything, in which case it will not be a lot faster than dropping and recreating the data.
This scenario gives you the most control and is the easiest way to trace anything going wrong. Just remember to clear that log file down regularly if a lot of data is involved.
Check out ralph kimball website for a detailed instruction on this scenario and other similar options.
As a side issue, you may want to look at replication as well for real time results.
June 23, 2005 at 11:11 am
Thanks very much for the quick response I will look at both replication and the staging table.
Kurt Kracaw
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply