One of my AD teams asked for DTS packages to be moved from a SQL Server 2000 server to a SQL Server 2005 Server.
These packages are stored in the msdb database.
I created an SSIS package per the instructions at the following link to copy packages from a SQL Server 2000 server to a SQL Server 2005 server.
http://blogs.interfacett.com/jeff-jones-blog/2006/10/20/moving-dts-packages-to-a-sql-server-2005-server.html
I specified a query as my source.
SELECT t1.*
FROM dbo.sysdtspackages as t1
INNER JOIN ( SELECT [name],
[id],
MAX([createdate]) as [createdate]
FROM dbo.sysdtspackages
GROUP BY [name],
[id]
) AS t2 ON t1.[id] = t2.[id]
AND t1.[createdate] = t2.[createdate]
WHERE t1.name LIKE 'CRD_%'
This package fails with the following error...
Error messages from SSIS package I created to move CRD DTS packages from SQL143 to SQL050.
[OLE DB Destination [61]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E2F. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "Violation of PRIMARY KEY constraint 'pk_dtspackages'. Cannot insert duplicate key in object 'sysdtspackages'.".
The packages do not exist on the target server. I have confirmed that I am getting 132 distinct rows from the source server.
The primary key for sysdtspackages is id and versionid.
The error is occurring in the OLE DB DESTINATION.
I have tried modified the above query to list each package by name. Same error. Also, I have tried moving the WHERE clause into the derived table clause. Same result.
I am a novice with SSIS at the moment. Haven't found a solution to this issue via Google.
I have successfully transfered packages between 2000 servers using a similar method described on SQLDTS.com in the article "Transferring DTS Packages."
While waiting on an answer I intend to hit the books and do some more debugging.
Regards,
Ronald Dameron