XP SP2 screwing up DTS transform on Text field

Question

XP SP2 screwing up DTS transform on Text field

joshcsmith13

Hall of Fame

Points: 3381
More actions
September 1, 2004 at 12:40 pm

#59724

HELP! has anybody else experienced this?
Emperical evidence has shown that a DTS package that converts a table, containing a text field, with an ActiveX transform task used to work, and now fails with XP SP2 installed. Uninstalling SP2 brougt back a successful transformation. I searched for quite a long time for bad data or some scenario that should cause it to fail, but could find none. Interstingly, if each record was pumped/transformed one at a time, they all worked. As soon as the Source query started returning more than one record, it stopped. This is not good.
Any insight out there?

Thanks,
Josh

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

sqluser@sqluser.com Old Hand Points: 310 More actions · Answer 1

assuming you have a legal copy of sql server you have a support agreement with Microsoft (usually 2 free support calls per product, but you may have a premier agreement with your employer). You should surely raise this, as it will not decrement your support incidents (I understand) if it turns out to be a software fault, and I'm sure they'll be really keen to hear of any odd exceptions! They can then work on a QFE patch, or suggest an interim workaround for you

hth

David O Hall of Fame Points: 3228 More actions · Answer 2

I had the same DTS issue a couple of months ago on Win2k. Upgrading to Sql Server hotfix version 8.00.818 solved the problem. There is a knowledge base article on this issue.

Good luck

joshcsmith13 Hall of Fame Points: 3381 More actions · Answer 3

Hi guys,

While opening an issue with Microsoft is not out of the question, I have never found that to be a simple process. At the same time, employing a hotfix is not real appealing either, especially since the DTS package that is failing is being shipped to 500+ sites, with various installations of SQL Server! I do appreciate the ideas though.

So, it sounds like I'm on my own with this one... I've posted the same thread on Microsoft's SQLSERVER-DTS discussion board and haven't gotten much of a response there either. oh well. what fun would this job be without a challenge!

joshcsmith13 Hall of Fame Points: 3381 More actions · Answer 4

Here's an update, to keep you posted: so far I can't find anybody else reporting the same problem, and MS did not respond on their newsgroup. So, I attempted to create a work-around on for one package that was failing. Now it appears that subsequent packages will need modification as well. I think the following workaround is functioning, but I'll try to update you when I know for sure... The Text field in the Active-X transform was commented out and removed from the source and destination columns. Then, I created a Data-Driven query immediately following the Transform Data [pump] task, to update the Text column.

sqluser@sqluser.com Old Hand Points: 310 More actions · Answer 5

If this is going to 500+ sites, I suspect you may have a Premier agreement with Microsoft (if you're that big, you may well have). If so, contact your TAM (Technical Account Manager - Microsoft contact) for details of how to raise a support call. Personally I've found the support to be awesome once it's clear you need to get past the tier-1 helpdesk staff ('Have you tried rebooting?'). Put it like this - it takes you (say) 20 mins to raise a detailed support call (online is easiest imho) - how long have you already wasted on this issue? What is the dollar cost of that wasted time? Even if you've not got a Premier agreement, ~$100 to resolve this (refunded if it turns out to be a bug with the MS product in question - ~$100 is the price if you submit online, not by phone) is nothing compared to upsetting 500+ sites. And to top it all, you've probably got 2 free support calls anyway...

Tony Blighe Newbie Points: 1 More actions · Answer 6

We reported this to Microsoft. They say there is a bug in MDAC on XP SP2. Errors occur if text field is a zero length string.

We found a workaround - instead of a "copy column" transformation, use a "trim string" transformation, but don't tick any boxes in the transformation properties, i.e. data is unchanged.

Hope this helps 🙂 Tony Blighe http://www.123live.co.uk

joshcsmith13 Hall of Fame Points: 3381 More actions · Answer 7

Tony, thanks for your reply! I'm afraid this painful memory has already been repressed, so it's hard to recall exactly what was going on, but I thought that we actually had to remove the Text column from our source query to get the transformation to work properly. Our work-around was to add a subsequent Data-Driven-Query task that updated the Text field. This, of course, caused multiple cascading issues, do the introduction of a new task in the sequence. But, in the end, I think we got it working. So, certainly, if Tony's suggestion works for you, go with that, but there are other painful alternatives.