Solved

DTS from table to text delimited file

Posted on 2004-04-27
9
691 Views
Last Modified: 2012-06-27
I am trying to DTS from a sql server 2000 table to a | delimited text file.

I can get it to work fine with all the columns, but if I try and exclude some of the columns it comes up with the following error:

A DTSTransformCopy must specify no columns (signifying a 1-to-1 mapping of all columns) or the same number of source and desgination columns

However - the destination columns section is empty.

anyone got any ideas?

thanks...
0
Comment
Question by:jon100
  • 5
  • 3
9 Comments
 
LVL 3

Expert Comment

by:Frostbyte_Zero
ID: 10930984
Double Click the DataPump Task. In the Transformation tab: Delete all the transformations. Then recreate the transformations. You can just click New/ActiveX Transaformation. Then you have a dialog with 3 tabs. Click Source, select the fields you want to include. Click Destination, ensure that there are the same number of fields. Click Properties, then you should have a default ActiveX script for you. You can edit it if you want. Test it. And Save.

0
 

Author Comment

by:jon100
ID: 10931414
Thanks

The Destination Columns tab doesn't contain any fields.
0
 
LVL 3

Expert Comment

by:Frostbyte_Zero
ID: 10931593
Double Click the Destination Connection in your DTS Package and make sure that it is still valid.
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

Author Comment

by:jon100
ID: 10931602
its still fine - my destination is a text file remember...
0
 

Author Comment

by:jon100
ID: 10931889
Its strange - I can go in and select the source table fine

It then lets me 'define columns' - which I do (removing 4 from the end of the table) and go onto transformations - but none of the fields I created are in destination - whats going on?

Also if i go back in and try and edit the destination tab / Define Columns - SQL Server fully crashes.

Im on sp3 sql2k developer edition...
0
 
LVL 3

Accepted Solution

by:
Frostbyte_Zero earned 65 total points
ID: 10932063
Try rebuidling the DTS using the Impor/Export Wizard. Use a SQL statement instead of a Table as your Source.
0
 

Author Comment

by:jon100
ID: 10932282
Great thanks - good work around idea.

I wonder if Microsoft are aware of this problem (seems2b a prob2me)
0
 

Expert Comment

by:geoffbtbc
ID: 11262733
I have the same problem with SQl2k/sp3, on one of our servers. I want to do this as part of a DTS package, and I can use the wizard to create this part and then add to it; if I try to edit that transformation within the GUI, it forces me to redo all the mappings and then claims it has no destination column names to use.

Did you ever get it to work?
0
 

Author Comment

by:jon100
ID: 11262770
hi geoffbtbc - no never found a solution. What I had to do is use the wizard work around, which got round the issue for me.

Hopefully it's something they'll fix in the next sp / version.

Good luck
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question