tag:blogger.com,1999:blog-7979948.post113820132685708666..comments2023-07-14T12:48:01.476+02:00Comments on Vittorio Pavesi: SQL 2000: Change DTS OwnerVittorio Pavesihttp://www.blogger.com/profile/02804415638484029352noreply@blogger.comBlogger6125tag:blogger.com,1999:blog-7979948.post-77390407404822486172012-01-16T16:14:14.069+01:002012-01-16T16:14:14.069+01:00dynamically create the reassign SQL...
SELECT DIS...dynamically create the reassign SQL...<br /><br />SELECT DISTINCT [name], [id], <br />'exec sp_reassign_dtspackageowner @name='''+[name]+''', @id='''<br />+CAST([id] as varchar(36))+''', @newloginname='''+'CAPREPSERVER1\DMSSQL'''<br />FROM sysdtspackagesMohsinhttp://dbamohsin.wordpress.com/noreply@blogger.comtag:blogger.com,1999:blog-7979948.post-19689816635873057332009-08-27T19:20:50.736+02:002009-08-27T19:20:50.736+02:00Three and a half years later and it's still he...Three and a half years later and it's still helping. Thanks!Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-7979948.post-6153042115354638372009-08-12T16:32:50.355+02:002009-08-12T16:32:50.355+02:00Thanks a LOT!! it works!!
from Monterrey Mexico
Se...Thanks a LOT!! it works!!<br />from Monterrey Mexico<br />Sergio FierroUnknownhttps://www.blogger.com/profile/07555987297914839632noreply@blogger.comtag:blogger.com,1999:blog-7979948.post-73159045036247713792007-12-13T14:10:00.000+01:002007-12-13T14:10:00.000+01:00Thank you all very much for the tip!It saved us a ...Thank you all very much for the tip!<BR/>It saved us a great amount of time!Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-7979948.post-1157546309623544812006-09-06T14:38:00.000+02:002006-09-06T14:38:00.000+02:00To copy a DTS just follow these instructions:Open ...To copy a DTS just follow these instructions:<BR/><BR/>Open the DTS package, save it as a .dts file.<BR/>Copy this .dts file to the target machine. <BR/>Open Enterprise Manager on<BR/>that machine, right click on 'Data Transformation Services' node, and select<BR/>'Open package' option. <BR/>Select this .dts package then save it again to your target SQL Server.Vittorio Pavesihttps://www.blogger.com/profile/02804415638484029352noreply@blogger.comtag:blogger.com,1999:blog-7979948.post-1153473019944011522006-07-21T11:10:00.000+02:002006-07-21T11:10:00.000+02:00---try this sproc that I created ----that will cha...---try this sproc that I created ----that will change the ownername of all dts packages to "sa" for package names begining with the letters "espa"<BR/><BR/>---------<BR/><BR/>CREATE PROCEDURE [dbo].[csp_Utils_Change_Owner_to_SA_for_ESPA_DTS_Packages] <BR/><BR/>AS<BR/><BR/>DECLARE @PACKAGE_NAME AS NVARCHAR(1000)<BR/>DECLARE @OBJECT_ID AS NVARCHAR(1000)<BR/><BR/>DECLARE @NEW_OBJECT_OWNER AS VARCHAR(20)<BR/><BR/>SELECT @NEW_OBJECT_OWNER= 'sa'<BR/><BR/>CREATE TABLE #TMP_DATA_DTS_PACKAGE<BR/> (<BR/> PACKAGE_NAME VARCHAR(1000),<BR/> PACKAGE_ID VARCHAR(1000)<BR/> )<BR/><BR/> <BR/>INSERT INTO #TMP_DATA_DTS_PACKAGE<BR/><BR/> SELECT NAME, ID FROM msdb..sysdtspackages<BR/><BR/> WHERE NAME LIKE 'espa%'<BR/><BR/> GROUP BY NAME, ID<BR/> <BR/>----------------<BR/><BR/>DECLARE proc_cur CURSOR FOR SELECT PACKAGE_NAME, PACKAGE_ID FROM #TMP_DATA_DTS_PACKAGE<BR/><BR/>OPEN proc_cur <BR/><BR/>FETCH NEXT FROM proc_cur INTO @PACKAGE_NAME, @OBJECT_ID<BR/><BR/>WHILE @@fetch_status = 0<BR/><BR/>BEGIN<BR/><BR/> exec msdb..sp_reassign_dtspackageowner @PACKAGE_NAME, @OBJECT_ID, @newloginname = @NEW_OBJECT_OWNER<BR/><BR/> PRINT @PACKAGE_NAME<BR/><BR/> FETCH NEXT FROM proc_cur INTO @PACKAGE_NAME, @OBJECT_ID<BR/><BR/>END<BR/><BR/>CLOSE proc_cur<BR/> <BR/>DEALLOCATE proc_cur<BR/><BR/>DROP TABLE #TMP_DATA_DTS_PACKAGE<BR/><BR/>GOAnonymousnoreply@blogger.com