Introduction

I'll admit that I'm not a hardcore DTS developer. But I do use DTS all the time to get data into or out of SQL or from one server to another. I ran into an interesting problem the other day and found the most elegant solution was to use the Dynamic Property Task to write a SQL statement for me.

The Problem

I had written a DTS package to pull data from an Access database, massage the data using a stored procedure, and then populate a table on a SQL Server owned by a different department. I wanted to have as small an impact on their database as possible while still getting the job done. Since the application that I was writing to is an online calendar, part of our intranet, I was only concerned about populating future events.

My first design deleted all of the future records from the calendar and then inserted all of the records that I had imported from Access. I finally realized that it would make more sense to only delete records from the calendar when they had been removed from the source. I also decided, since the source records wouldn't be changed that frequently, that I would delete and reinsert any modified entries.

I added a new table to import the future dated records from the calendar into my database and added an extra field to mark records to be deleted. I could now compare my Access-imported data to the calendar data, setting the deletion flag in records that should be deleted from the calendar.

It seemed like a great plan, but I soon realized that the Execute SQL Task to delete the rows could only see data from one connection. I needed to delete rows from a table on one server, and the list of rows to delete was on another server. One solution would be to configure a linked server to the other department's server.

This seemed to be a simple task that should be easy to accomplish in DTS. As I said, I'm no DTS guru, so there might be any number of ways to get this done. If only I could change the SQL statement in my Execute SQL Task on-the-fly....

Dynamic Properties Task to the Rescue

Since I already had written a stored procedure that prepared the data to be inserted and created a list of records to delete, I added the following to my code:

Declare @DeleteList varchar(1000)
Declare @SQL varchar(1000)
-- Create a comma delimited list of ID numbers
Set @DeleteList = ''
Select @DeleteList = @DeleteList + CAL_ID + ',' 
from intranetCalendar where ToBeDeleted = 1
-- 
If len(@DeleteList) > 1 begin
   --remove the last comma
   Set @DeleteList = left(@DeleteList,len(@DeleteList)-1) 
End
Else begin
   --no records to delete
   set @DeleteList = '1 = 2'
end
--build the SQL statement
Set @SQL = 'Delete from Calendar where CAL_ID in (' + @DeleteList ')'
--add the statement to a table
Truncate table DeleteList
Insert into DeleteList select @SQL

This code added the SQL statement I needed as a record in a table.

I then added a Dynamic Property Task to my DTS package right after running the stored procedure, but before the step to delete the records. I configured Dynamic Property Task to run a query to determine the SQLStatement property of the "Execute SQL Task" that deleted the records. It worked! I now had a dynamic way to determine which records to delete.

Step By Step

• Run the following script in a sample database:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DeleteList]') 
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[DeleteList]
GO
CREATE TABLE [dbo].[DeleteList] (
[List] [varchar] (1000)  NULL 
) ON [PRIMARY]
GO
Insert into deleteList select 'Delete from Calendar where CAL_ID in (1,2,3,4)'
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Calendar]') 
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Calendar]
GO
CREATE TABLE [dbo].[Calendar] (
[CAL_ID] [int] NOT NULL ,
[Description] [varchar] (10)  NULL 
) ON [PRIMARY]
GO

insert into calendar select 1,'a'
insert into calendar select 2,'b'
insert into calendar select 3,'c'
insert into calendar select 4,'d'
insert into calendar select 5,'e'
insert into calendar select 6,'f'
insert into calendar select 7,'g'
insert into calendar select 8,'h'

• You should now have two new tables, one with eight sample records, and one

  table to hold the delete statement.

• Create a new DTS package using the DTS Designer in Enterprise Manager.
• Add a Connection to the local or another SQL Server.

Figure 1: Connection

• Add an Execute SQL Task. You have to type in a query, a select statement will do for now.
• Add a Dynamic Properties Task object.

Figure 2: The objects in the DTS package

• Double-click the Dynamic Properties Task and click "Add...".
• Expand "Tasks" and select the Execute SQL Task from the list on the left.

Figure 3: Dynamic Properties Task

• Select "SQLStatement" found in the right pane and click "Set...".
• The "Add/Edit Assignment" dialog will pop up. Choose "Query" from the "Source" dropdown list.
• Type "Select List from DeleteList" in the "Query" box.

Figure 4: Use "Query" as the source

• Click "OK" twice to close both dialogs.
• Select the Dynamic Properties Task.
• Hold down the CONTROL key and select the Execute SQL Task.
• From the menu, choose "Workflow" then "On Success".
• You should now see a green striped arrow pointing to the Execute SQL Task.
• Execute the package by choosing "Package" then "Execute" from the menu.
• If all went well, you should now be able to view the properties of the Execute SQL Task and see that it has been modified.

Figure 5: The Execute SQL Task was modified on-the-fly.

• The calendar table should now be missing rows 1 through 4.

Conclusions

By using the Dynamic Properties Task, I was able to solve an interesting problem. DTS in SQL 2000 is very useful and versatile. The Dynamic Properties Task makes it even more so. Can't wait until SQL 2005 and Integration Services for even more amazing functionality.