DTS packages fail after changing server host name

Executed as user: DOMAIN\SQL.agent. DTSRun:  Loading...      
Error:  -2147467259 (80004005); Provider Error:  17 (11)      
The step failed.

After changing the host of a SQL server, i found that the DTS packages were still referencing the old server as the DTS Package Owner. This was resulting in the error above when run as a Agent job.

To resolve I’ve created a script which will dynamically create the SQL to change the owner of a DTS package. sp_reassign_dtspackageowner is an undocumented stored procedure (not that this matters in 2012 when SQL 2000 is unsupported! 🙂 )

SELECT DISTINCT [name], [id], 
'exec sp_reassign_dtspackageowner @name='''+[name]+''', @id='''
+CAST([id] as varchar(36))+''', @newloginname='''+'DESIRED\DTSPACKAGEOWNER'''
FROM sysdtspackages

Run the output from the SQL above (for the packages required), and this should rename the packages. 

The error can also occur if the SQL Agent Job which is running the DTS Package, has an old reference relating to the old server. This can be resolved by recreating the job by right clicking the package and scheduling.

Also see: https://dbamohsin.wordpress.com/2012/01/05/p2v-a-sql-server-2000-machine/

Advertisements

One Response to DTS packages fail after changing server host name

  1. Pingback: P2V a SQL Server 2000 Machine « Mohsin's DBA Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: