Redactuk
asked on
DTS Transfer Data Column Definitions Problem
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.
Many thanks
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.
Many thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Anthony Perkins
What service pack are you using?
Do you have the box checked where it say
First rows has column names - if not please check it
and run the DTS package.
First rows has column names - if not please check it
and run the DTS package.
ASKER
In response to your comments.. in order:
I do not have the freedom to 'massage the data' in the table I wish to export to a text file.
As stated in my original message I'm using SP3a
Yes I have tried both with that checked and unchecked. Makes no difference.
I would be intested if anyone else can recreate this problem? i.e. create table with 90 fields named C1-C90 then try and export to a text file in DTS. does Define Column names give you the list on Execute?
I do not have the freedom to 'massage the data' in the table I wish to export to a text file.
As stated in my original message I'm using SP3a
Yes I have tried both with that checked and unchecked. Makes no difference.
I would be intested if anyone else can recreate this problem? i.e. create table with 90 fields named C1-C90 then try and export to a text file in DTS. does Define Column names give you the list on Execute?
Out of curiosity I tried it, and it definitely doesn't work with 90 columns. I got the same problems you described.
I tried manually populating the destination file with columns and that didn't even work.
You can use BCP with a query. You can transform your data with a query as long as your transformations can be covered with inline SQL functions.
Theres probably a size limit for the query you plug in.
If you can stretch your freedom enough to create a view in the database, that would fix that problem also
That must be quite a denormalised table with 90 columns?
I tried manually populating the destination file with columns and that didn't even work.
You can use BCP with a query. You can transform your data with a query as long as your transformations can be covered with inline SQL functions.
Theres probably a size limit for the query you plug in.
If you can stretch your freedom enough to create a view in the database, that would fix that problem also
That must be quite a denormalised table with 90 columns?
>>As stated in my original message I'm using SP3a<<
No, please re-read your comments you said specifically "I am using latest MSQL SP3" and I know exactly what you are talking about it only happens when you have more than 24 columns and I resolved it by installing either a MS Hotfix or SP3a as I recall.
Good luck.
No, please re-read your comments you said specifically "I am using latest MSQL SP3" and I know exactly what you are talking about it only happens when you have more than 24 columns and I resolved it by installing either a MS Hotfix or SP3a as I recall.
Good luck.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Do you still need help with this question?
Do you still need help with this question or can we now consider this question abandoned?
Split the points among all contributors.