DTS is dead, long live SSIS. Way back when SQL 2005 was announced then released DTS was pronounced DOA. SSIS was the wave of the future. Everyone was so thrilled that posts like SSIS’ 15 faults and Why I hate SSIS: Part N+1 were all over the place, and I was right there with them. My first impression was that SSIS was designed by not one committee but several, and they hate each other.

Now to the present. I’m used to SSIS now, I even quite like it. I think it is a very powerful ETL tool with lots of applications and I use it frequently. I even think in general it was well written. However, I still haven’t changed my mind about the committees and I still think SSIS was poorly designed in some ways. I don’t think I’m all that unusual either. I don’t see many blogs complaining about SSIS anymore, but I still see plenty of DTS packages. In fact I have developers in my office that are actively resisting moving to SSIS and continue to create new DTS packages.

This is a problem. A big problem. As of SQL 2012 DTS is no longer supported. At all. Not even using the add on components that were available for SQL 2005, 2008 and 2008R2.

This of course means that in order to move to SQL 2012 all of the DTS packages have to be converted to something else, presumably SSIS. Just use the conversion tool right? Well, as anyone who has used it can tell you the conversion tool can be somewhat iffy. That means converting some of them by hand and double checking the rest. Somewhat labor intensive but doable.

Unless you have a server with 1300 DTS packages on it. And yes, my office does in fact have a server with over 1300 DTS packages. I’m going to say that one more time, 1300 DTS packages on one server! Imagine trying to do that conversion. To say it would be a nightmare is an understatement.

So how can we can reduce that number a bit? There is no reason to convert the packages that are no longer in use. I mean they can’t be using 1300 different DTS packages right? So how do we tell what packages are being used? Well, we could check the various jobs, ask the developers, ask any power users and keep our fingers crossed. I’ve gone this route before. Depending on the size of the operation, age of the packages, how often the packages are used, etc. I’ve gotten around 50-75% of the packages actually in use. Not good enough. I want something a bit more certain.

It turns out that any time you open a DTS package either to edit it or to run it SQL uses the undocumented stored procedure ‘sp_get_dtspackage’ to load it. And that means we can create a trace to catch uses of it. For SQL 2008 or 2008 R2 you can also use extended events but I’m not going to demonstrate that here since tracing will work for all versions needed and extended events will only work for 2008 and 2008 R2.

Here is a sample trace script I generated from profiler. It’s fairly light weight because I’m only pulling the RPC Completed and Stmt Completed events and the TextData, Spid and EndTime columns. Really no more is needed for this purpose.

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5 
exec @rc = sp_trace_create @TraceID output, 0, N'C:\TEMP\Running_DTS', @maxfilesize, NULL 
if (@rc != 0) goto error
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 1, 0, 6, N'%sp_get_dtspackage%'
exec sp_trace_setfilter @TraceID, 1, 0, 1, NULL
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error: 
select ErrorCode=@rc
finish: 
go

Here I open and close a DTS package called Test_Move. Next I stop and close the trace.

declare @TraceID int
SET @TraceID = 2
EXEC sp_trace_setstatus @TraceID,0
EXEC sp_trace_setstatus @TraceID,2

And read from the trace file.

SELECT * FROM fn_trace_gettable ('C:\TEMP\Running_DTS.trc',1)

Then here is an example of the command you get.

exec msdb..sp_get_dtspackage N'Test_Move', '{7343C93E-0D24-444B-B491-4F153F0A0BA4}', '{B750E449-0C7F-4B33-8306-E682AC5C0705}' 

At this point it is easy enough to substring out the name of the DTS package. Run this over the course of a couple weeks to a month and you can be fairly sure to get most if not all of the active DTS packages. You need to be somewhat careful as you are still likely to miss the infrequently run packages. Those that run monthly, quarterly, yearly, or at need. Personally I would take the list from the trace along with a list of those packages that aren’t in the first list as a starting point. From there search active jobs, ask developers etc to generate a final conversion list. Then of course make sure that you have a backup of msdb and a place you can bring it up at need to get any missed DTS packages in the future.

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, SSIS, System Functions and Stored Procedures, T-SQL Tagged: code language, DTS, language sql, microsoft sql server, sql statements, SSIS, system functions, T-SQL