USE MSDB
GO

DECLARE @INSTANCENAME SYSNAME
DECLARE @cmd nvarchar(4000) 

SET @INSTANCENAME = RTRIM(HOST_NAME()) + ISNULL( '\' + NULLIF(RIGHT ( @@SERVERNAME, CASE WHEN CHARINDEX('\',@@SERVERNAME ) > 0 THEN LEN(@@SERVERNAME) - CHARINDEX('\',@@SERVERNAME ) ELSE 0 END ),''),'' )

IF LEFT ( @@SERVERNAME, CASE WHEN CHARINDEX('\',@@SERVERNAME ) > 0 THEN CHARINDEX('\',@@SERVERNAME ) -1 ELSE LEN(@@SERVERNAME) END ) <> HOST_NAME() 
BEGIN 

	WITH PackageCTE(Id, PackageDataString)
	AS
	(
		SELECT id, CAST(CAST(packagedata AS varbinary(MAX)) AS varchar(MAX))
		FROM sysdtspackages90 
	)
	UPDATE P 
	SET PackageData = CAST(
						REPLACE(
						REPLACE(C.PackageDataString,
							'Data Source=''' + @@SERVERNAME + '''','Data Source=''' + @INSTANCENAME + ''''),
							'server=''' + @@SERVERNAME + '''', 'server=''' + @INSTANCENAME + '''')
						as varbinary(max))
	OUTPUT 'Changed ' + INSERTED.name + ' server from ' + @@SERVERNAME + ' to ' + @INSTANCENAME
	FROM sysdtspackages90  P
	JOIN PackageCTE C ON C.id = P.id
	WHERE C.PackageDataString LIKE '%server=''' + @@SERVERNAME +'''%'
	OR C.PackageDataString LIKE '%Data Source''' + @@SERVERNAME +'''%'

	SET @cmd = N'EXEC sp_dropserver ''' + @@SERVERNAME + N'''; 
		EXEC sp_addserver ''' + @INSTANCENAME + ''', local'  ;
	EXEC ( @cmd )  ;
END 
GO