I have table of 90 columns and often up to 100k rows that I wish to output to a fixed width text file. I would like to achieve this via a DTS package as this is well suited to the setting up of lots of other related custom tasks that I need.
The problem I have (and one that I've heard mentione of before) is that when I create a Transform Data task between my source table and a text file connection, and I click Execute via the Define Columns interface, no column name are created. I have tried the same with other smaller files and works file.
I have previoulsly read that someone believe that there was a limitation of 256 byes for the total length of the column names, but i have tried renaming all my columns to 2 character names i.e. 180 and issue still occurs.
I've spent the last 2 hours searching various forums and sites, inclusing MS site, but have still not been abble to find out any infomation on this limitation or if there is a fix to it (I am using latest MSQL SP3).
I have considered using the Bulk Copy utitlity but as far as I can see this will not allow me to so any additional manipulation and formating that I require at the field level (which i could do in ActiveX section of Data Transform). What I can't really believe is this is not well documented? surely other people have the need to export large tables to text file, and if so how do they get around this? I'm totallly baffled. Please can someone either give me some clear info on how to overcome this apprarent bug or offer an alternative solution to allow me to achieve this export.