Create a DTS package to Import Excel files

DTS stands for Data Transformation Services, and is the SQL Server 2000 forerunner to SSIS.  Among other things, it can be used for importing data from an Excel spreadsheet to a table in a SQL Server database.  That was what I needed to do, but you can import or export from and to pretty much anything.  

Before using the wizard to set up a DTS package, make sure that there are no existing versions e.g. for testing.  If a table already exists, the Wizard won’t put in a task to create the table, and DTS is fiddly to edit. 

Open SQL Server 2000 Enterprise Manager and navigate to the database you want.  Select the “Run a Wizard” task from the taskbar.  (It's the one that looks like a magic wand sprinkling stars...)

There are loads of wizards.  Select the Wizard you wish to use from the range available.  In this case we want to use the DTS Import wizard.

Select the data source type from the pull down menu – in this case Excel.

Browse to the source data file.  

If the source data is in Sharepoint, select My Network Places and choose the Portal.  

After choosing the file to be imported, click on Next.  

Now select the destination server and database.  If you forget to specify the database, the wizard will set up a table on the default database, which may not be what you intended.  In my case the default was Master.  So the package worked, and created a table, but I couldn’t see it anywhere because I was looking in the wrong place.  

You can copy the whole table, or use a query to select just the bits you want.  

In this example there are several worksheets in the spreadsheet, listed in the source column.  I have ticked All, and specified the name of the destination table. 

Click on Preview to make sure that you have the correct data. 

You can run the package immediately, or save it for later, or schedule it to run daily or weekly.  

.  If saving, specify the package name and optionally a description.  

Check the Wizard’s summary and click on Finish

This is what the wizard produces:

Right click on the Create Table task.  This allows you to edit the code used, e.g. to check whether the table already exists and if necessary, drop it before creating a new one.  

This sort of thing, perhaps:

-- ImportPortfolio Matrix

-- Jack Whittaker 02/06/2011

use SGSGROUP

go

-- Check for existence of table

if exists

(select * from dbo.sysobjects

where id = object_id(N'[dbo].[tblMasterCFCPortfolioMatrix]')

and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[tblMasterCFCPortfolioMatrix]

GO

-- create new table

 
If you forget this - and I did - then step one of the job will fail.  You can't create a new database if there is one there already.  But apart from that, it's not difficult at all.