Tuesday, May 22, 2007

DTS -- My learnings today

Today I wanted to schedule a data update process to be processed every month for the table I created using DTS last month.

I wanted to populate a brand new database from an exisitng database. I thought I would just schedule a package with a few SQL queries to populate the different tables in the new database. But before I proceededI wantedto find out an efficient and best way to do it.

So I posted my questions in two forums. This is what I found out from the experts:

Before you decide on setting up the DTS package there are several aspects to consider.

Do you want to recover the database from a backup when something goes wrong?
Do you want to just reload everything when something fails?
Do you want to save logging information so that you can recover the database?

Based on the answers to these questions, the best way to create a DTS package for my scenario is to create the tables with the structure and then create the DTS package using many Data Driven Query Tasks that contains the SQL statemetns of select, Insert and Update depending on whatever the case may be.

DTS is an excellent tool to schedule population of data into a database.

No comments:

INSERT EXEC failed because the stored procedure altered the schema of the target table.

This morning I was trying to call a stored procedure and insert the results in a temp table using the following code  -- create table #...