• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 216
  • Last Modified:

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



0
Redactuk
Asked:
Redactuk
  • 6
  • 2
  • 2
  • +1
3 Solutions
 
nmcdermaidCommented:
You can use bulk copy. You just need to massage the data in the export table before it gets exported. This can be done with T-SQL.

ActiveX transformations are very inefficient.

Unfortunately I can't help you with your other problem!
0
 
Anthony PerkinsCommented:
What service pack are you using?
0
 
JulianvaCommented:
Do you have the box checked where it say

First rows has column names - if not please check it
and run the DTS package.



0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
RedactukAuthor Commented:
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?

0
 
nmcdermaidCommented:
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?


0
 
Anthony PerkinsCommented:
>>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.
0
 
JulianvaCommented:
I am using SQL server 7 and i tried to create the table
with 90 columns - does Define Column names give you the list on Execute? - yes it does - all from c1 to c90

my datatype is all nvarchar size 255
0
 
Anthony PerkinsCommented:
>>I am using latest MSQL SP3<<
Microsoft SQL Server 2000 Service Pack 4
http://www.microsoft.com/downloads/details.aspx?familyid=8E2DFC8D-C20E-4446-99A9-B7F0213F8BC5&displaylang=en
0
 
Anthony PerkinsCommented:
Do you still need help with this question?
0
 
Anthony PerkinsCommented:
Do you still need help with this question or can we now consider this question abandoned?
0
 
Anthony PerkinsCommented:
Split the points among all contributors.
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 6
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now