Post reply

Max columns in Transform Data Task?

  • February 16, 2005 at 9:16 am

    #60698

    We have developers working with DTS packages to create text files.  When they try to map the columns to a fixed width text file in the Transform Data Task and they select every column and click 'define columns' they get an error and get tossed from Enterprise Manager.  If they combine the columns and do the mapping with fewer columns, it works.  It appears to be 20 columns.  I have found suggestions on the Microsoft site to keep the number under 20 columns for performance reasons, but not that it would fail.  The developer pcs are XP with MDAC 2.8.  We have some pcs (Windows 2000 Professional - MDAC 2.7) that work ok with these packages, but Windows 2000 Professional - MDAC 2.6 won't work. We have tried MDAC 2.7 and 2.8 on the XP machines.

    On some of the developer pc's, XP service pack 2 had been applied.  On others, it had not.  We have uninstalled and reinstalled MDAC  (multiple versions) and SQL on the pcs.

    Thanks in advance.

     

     

  • February 17, 2005 at 6:11 pm

    #542993

    I found a similar problem quite recently.

    In my case, it was the total number of characters in the field names.

    A couple of "as fld1" statements in my query and the transformation worked fine.

    Are you sure that your limit is 20 fields and not the sum of those fieldnames?

    Brian Bunin

  • February 17, 2005 at 6:26 pm

    #542995

    See http://support.microsoft.com/kb/814113/EN-US/.

    I've used a package that transforms out to ~140 fixed fields, a total of about 1650 characters wide.  I recently also set up an import from fixed field with ~50 fixed fields about 700 total characters wide with over 100,000 rows - I think it took about 15 minutes to load.

    So, you can definitely get way more than 20 columns.  I seem to recall that when the developer was setting up that 140 column package we ran into this problem and the hotfix fixed it.

    Bill

  • February 18, 2005 at 6:11 am

    #543060

    Thanks so much for the help Brian and Bill!  I'll get back with the developers this morning.

Viewing 4 posts - 1 through 3 (of 3 total)

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