A Cheater’s Guide to DTS Packages

Let me just say, at this point, that SQL was a mystery to me before I started working with MIIS. I’d always looked at it from a sys admin point of view – set up the server, install SQL, maybe even do some patching, and then hand it straight over to the DB people.

But you can’t do MIIS without learning some SQL so I soon found myself bugging my friendly, neighbourhood DBA for advice on query scripts and how to go about running them. It seemed I needed something called a DTS package.

The reason I’m calling this post a cheater’s guide is because I’ve never done any proper reading or study on how DTS packages should be constructed – I’ve just figured out how to cobble one together for my own purposes. This is neither ground-breaking nor particularly challenging, but it is something I had to discover on my MIIS journey, so I’m including it here.

Some steps for creating a simple DTS.

1. Create the DTS:
   1. In SQL Enterprise Manager, open the local server and go to Data Transformation Services -> Local Packages.
   2. Create a New Package.

2. Create a Connection to the database you will be working on:
   1. Click Connection -> Microsoft OLE Provider for SQL Server.
   2. When configuring the Connection settings, I normally change the Connection name to be the database name.
   3. Just leave the server as (local). This makes it easier to replicate the DTS between dev, test and DR servers.
   4. I always use Windows Authentication. It is important to make sure the account that will be running the DTS has appropriate rights to the database in SQL. (You can always cheat and make the account a local Administrator – but no one ever does that right?)
      

3. Now create a SQL Task for the DTS to run:
   1. Click Task -> Execute SQL Task.
   2. Change the Description to something meaningful.
   3. Make sure the Existing Connection is set to use the Connection you just created.
   4. Paste your SQL query script into the SQL Statement window. This can be anything that you’ve successfully run from Query Analyzer. (For some examples see the delta table and attribute-level posts.)

4. Create further SQL tasks.

You could put all your SQL statements into a single Task, but it’s much nicer to split them down into sub-tasks. This makes the DTS easier to understand at a glance, and also gives you extra information if the DTS fails. As far as I can tell, native SQL logging for DTS packages is pretty much non-existant – about the only information you can get back is which task the DTS failed on. The more you break down your SQL statements into sub-tasks, the better idea you’ll have about where the failure occurred.

• Connect your tasks in running order.

1. Highlight the first and second task.
2. From the Workflow menu choose On Success.
3. Continue joining your other tasks until finished.

Here’s a picture of a DTS I created to generate four Delta tables.

Once you get your DTS going you’re going to want to synchronise the running of it with your MIIS import jobs, and there are also some issues about how and when to clear the delta tables- but these can be covered in other posts.