Technical: Microsoft – SQL Server – Document DTS Packages

Technical: Microsoft – SQL Server – Document DTS Packages

Background

Prior to version 2005, MS SQL Server mainline ETL building block was DTS (Data Transformation Services.

Starting from v2005, DTS was replaced with SSIS (SQL Server Integration Services).

Introduction

Thankfully, legacy DTS Packages are still supported in v2005 through v2008/R2.

MS SQL Server v2005+, View MSDB – DTS Packages

In SQL Server Management Studio, to view the DTS Packages please do the following:

  • Launch Microsoft SQL Server Management Studio
  • Connect to the SQL Server Instance
  • Access the SQL Node
  • Transverse the SQL Node’s — Management,  Legacy, Data Transformation Services

Document DTS Packages

Using Transact SQL, list DTS Packages



use [msdb];
go

select 
	  tblDTSPackage.name as packageName
	, tblDTSPackage.[description] as packageDescription
	, tblDTSPackage.createdate		  
	, tblDTSPackage.[owner] as packageOwner
	, tblDTSCategory.name as dtsCategory		
	, tblDTSCategory.description as dtsCategory

from   msdb.dbo.sysdtspackages tblDTSPackage

	  left outer join msdb.dbo.sysdtscategories as tblDTSCategory

		on tblDTSPackage.categoryid = tblDTSCategory.id

where  tblDTSPackage.createdate =
		(
			select max(tblDTSPackage_Inner.createdate)
			from   sysdtspackages tblDTSPackage_Inner					where  tblDTSPackage.id = tblDTSPackage_Inner.id
		) 
order by 
	tblDTSPackage.name	

Additional Software

If you are running Microsoft SQL Server Management Studio v2005, you might need additional software as documented in “Integration Services download” ( http://msdn.microsoft.com/en-us/sqlserver/aa336314.aspx )

  • Microsoft SQL Server 2000 DTS Designer Components
  • Microsoft SQL Server 2005 Backward Compatibility Components

 

Ongoing Support

Starting from MS SQL Server v2012, Microsoft no longer supports the DTS run-time.

Discontinued Integration Services Functionality in SQL Server 2012
http://technet.microsoft.com/en-us/library/bb500429.aspx

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s