DTS Transformation Query

,

To transfer all the DTS package from one SQL Server to another.

Create a DTS package.
Select source and destination SQL Servers
Create a DataPumptask
Go to disconnected edit
In the DTS Data transformation task Set
a) DestinationObjectName: [msdb].[dbo].[sysdtspackages]
b) SQL Statement :
SELECT a.name, a.id, a.versionid, a.description, a.categoryid, a.createdate, a.owner, a.packagedata, a.owner_sid FROM msdb.dbo.sysdtspackages AS A INNER JOIN (SELECT B.[name] , B.[id], B.[createdate] FROM msdb.dbo.sysdtspackages B GROUP BY B.[name], B.[id], B.[createdate] HAVING B.[createdate] IN (SELECT TOP 1 C.[createdate] FROM msdb.dbo.sysdtspackages C Where B.[ID] = C.[ID] ORDER BY C.[createdate] DESC) ) AS B ON A.[id] = B.[id] AND A.[createdate] = B.[createdate]



                
                                    


                
                                                    
                                                    
                                                    
            

Rate

Share

Share

Rate