sql server dts: Moving DTS package to another server loses Destination info -- sql server dts

Re: Moving DTS package to another server loses Destination info

Bruce Hendry
9/21/2006 12:00:00 AM

sql server dts:

Got the answer. The "Define Columns" button crashing Enterprise Manager when
output is to a text file is a known issue with sql SP3 specifically. Fixed
in SP4. This was a customer's server that hadn't been updated to SP4 yet.
Doh! Here's more detail about the bug...

http://www.eggheadcafe.com/forumarchives/sqlserverdts/nov2005/post24523051.asp

http://support.microsoft.com/default.aspx?scid=kb;en-us;814113

[quoted text, click to view]

Moving DTS package to another server loses Destination info

Bruce Hendry
9/21/2006 7:54:15 AM

Hi all,

I have a sql2000 DTS package I'm moving between windows2003 serves on the
same network. The moved package appears to lose all column definitions
defined for an output text file (source is a sql query). Clicking on the
"Define Columns" button in the Destination tab of the Transform Data Task
Properties dialog actually crashes Enterprise Manager.

If I go to Disconnected Edit and look at the Data Pump for the task, I can
see all the definitions for the Destination (text file) columns. The DTS
package even runs successfully and outputs to the text file as expected; it
is just that I cannot edit or see the column definitions in the Transform
Data Task Properties and clicking "Define Columns" crashes SQL. Obviously, I
don't want to stick something like that in a production environment.

I have moved the package by opening it and using Save As, and I also tried
using DTS to move the package via a datapump using msdb.dbo.sysdtspackages
as described on sqldts.com. Both yield the same results.

Any ideas greatly appreciated as I'm under the gun ;) thanks