Technical: Microsoft – SQL Server – Document DTS Packages
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).
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
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
Starting from MS SQL Server v2012, Microsoft no longer supports the DTS run-time.
Discontinued Integration Services Functionality in SQL Server 2012