View DTS packages with Powershell – DTS to SSIS planning

Question: How can I gather DTS package counts from a large number of SQL Server Instances, (over 500) ? We’re in the planning stage of a SQL Server 2012 upgrade . DTS is discontinued in SQL Server 2012, and I’m sizing the amount of effort required for upgrading DTS to SSIS. Answer: Estimating the amount of effort of DTS to SSIS is critical SQL Server 2012 SSIS upgrade consideration. Your immediate requirement is to document the package count but further effort will be required by developers to analyse the packages . Using Powershell , it is easy to iterate through... Read more →

SQL Server - Viewing legacy DTS packages in SSMS

Viewing legacy DTS packages in SSMS is useful. There are still many DTS packages in SQL Server environments , for different reasons DBAs decide not to convert in SSIS packages. I installed SQL Server Management Studio , to view a SQL Server 2000 instance . I attempted to access the packages via the : "Management | Legacy | Data Tramsformation Services" and got the following message: SELECT permission denied on table sysdtspackages, database 'master', owner 'dbo' I was perplexed , because when I attempted to view the DTS packages via SQL Server 2000 Enterprise Manager - I could view the... Read more →