Putting New Data into SQLSpyNet with the DTS Wizard

The DTS Wizard is a very flexible tool that allows us to import data from almost any data source, including Excel, DB2, Oracle, Access, and even Text files. Why do we need this? Many organizations have data spread throughout the company. Charles will have a spreadsheet of his customers, Mary will have a small Access database that has all her suppliers, and Jenny, the secretary, might have a text file (word document) with the names and phone numbers of all the staff members.

With all of these disparate types of information floating around the office it's pretty obvious that data management can become a real nightmare! Duplication of data is inevitable, and retrieval is almost impossible.

But here comes SQL Server 2000 to the rescue! With the introduction of DTS in SQL Server version 7.0, many organizations have been able to relieve the pain of trying to get all these disparate pieces of information together. The DTS Wizard is a point-n-shoot approach to import (or even export) data into our database. We go through a series of steps, selecting the database into which to enter the data, where the data is to come from, and so forth.

This is all fine for simple data, but what happens when we have more complex data that doesn't have a clear definition between first name and surname? DTS can even take care of this, with your help of course.

Within a DTS package we can define VBScript that can be used to manipulate, format, or rerun a process. This gives us great flexibility when it comes to altering our data either before, during, or after the insert to the database.

There are many aspects to DTS packages, and we will only have a quick look at the basics. I suggest that you play with DTS packages in SQL Server 2000 because they rock! And the flexibility and control you have over the import/export process will surprise you.

Before we actually insert the data into our database, we are going to delete all the data out of our database. This will allow us to start from a clean slate. But just before we do this though, let's back up our database.

Backing Up the Database Before the Transfer

You might be wishing you didn't have to do this, but do you want to know the cool thing? You do not have to write the backup statement again! Because we wrote a backup task earlier (see Chapter 11, the section titled "Scheduling Jobs"), we can actually force it to run immediately. This means we can create a full database backup just by right-clicking on the job (under Management, SQL Server Agent, and then Jobs), and selecting Start Job.

You will see that the status of the job is set to executing. When the job has finished, the status will change to either Succeeded or Failed.

So there we go, a backup done nice and simply, with no extra code!