DTS - Transform Data Task Destination Column Limits??

[BobbyPolo]

Hello,
I have a problem that I don't ever remember encountering before.

I have a text file that is comma separated like:
1,2,3,....37,38,39,40
1,2,3,....37,38,39,40
etc.

I use a Text File Source connection and point it to the file. I make a Text File Destination connection and point it to a file that doesn't yet exist.

I connect them with a Transform Data Task.

On the source tab of the Transform Data task I have the source text file selected. I then go to the destination tab and I click define columns (the first time is automatically goes to this). I click the "Populate from Source" button and I get 40 columns, 1 - 40. I click "Execute" but no columns are generated for my destination.

HOWEVER: If I make the text file only 37 columns wide:
1,2,3,....36,37
1,2,3,....36,37

It works just fine - I get 37 destination columns.

It seems like I can only have 37 columns (It blows up on the 38th colum), though I swear I have done really large text file conversions before.

We recently installed Patch 3 - I wonder if this is the cause.

Has anyone else had this problem? Has anyone recently installed patch 3 for SQL Server run into this problem?

Thanks!

 

[BobbyPolo]

I have done some more testing. The DTS packages that have CSVs with > 37 columns work fine if I do not modify them.

If I modify them and click "Define Columns" Enterprise Manager shuts down.

Anyone else out there having trouble?

 

[elainec]

I am having the exact same problem trying to run transformation from a table to a text file. It is an existing transformation in a dts package. I can open the transform data task, but it doesn't populate the columns in the destination tab. When I click define columns, get an unhandled exception in mmc.exe. I recall being able to edit these before we applied sp3.

 

[DDR]

I have the same exact problem too and that started after I applied service pack 3

 

[BobbyPolo]

This site is a link to a much more helpful forum:

http://www.sqlmag.com/forums/messageview.cfm?catid=4&threadid=12288

It seems there will be a fix shortly available.

 

[elainec]

Thanks for the info.

 

[BobbyPolo]

Here is a hotfix here:

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

 

[DDR]

Here I found a quick fix for this problem
Copy from you SQL Server 2000 the file named
DTSUI.dll then go to "Drive"\Program Files\Microsoft SQL Server\80\Tools\Binn and rename the old DTSUI.dll to something else and paste the one from the cd onto that folder.
This fix doesn't come from microsoft so you know that you do it at your OWN RISK.
But is working fine with me