Exporting to .csv file using DTS Designer

[Paulo]

Hi,

I'm currently trying to get a DTS to export the results of a SQL query to a .csv file.

It used to be really easy to do in SQL7 - set up a connection, set up the destination file, set up the transform data task, set the source to a SQl query and when you clicked on destination, you could choose to define columns... done

When I try this in SQL2000, it's all the same until I click on destination - there is a create table button button which when clicked shows the SQL for creating a table, but when I click on it - I get the error message "No Such Interface Supported". Has anyone experienced this before, and if so do you know a way around it.

If I use the DTS export wizard - it works fine - but in this case I can't as there are multiple steps that require csvs to be produced. I've searched for quite some time to try and find out why this is and would appreciate any help anyone could offer.

Cheers

Paul

Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

[skhanal]

Are you talking about Transform Data Task Properties?. I only see Execute button on destination for a text file destination, no Create.

[Paulo]

Yes - in the transform data task properties dialog box between a connection and a destination file. On the destination tab, It says:

Table name: <select box> <Create btn>

When you click "create" it opeens a create destination table dialog box containing the SQL command:

CREATE TABLE tableName(tableDetails)

When you click "Execute" to that - it says "No such interface supported"

---------------------------------------

This situation is different when using SQL7 however as it says:

Table name: <select box> <Define Columns>

When you click "define columns" it opens a define columns dialog box with a list of the fields. When OK is clicked, this works fine...

Cheers

Paul

[skhanal]

Why the Table Name is blank in your screenshot?. You can't define a destination without specifying the file name.

Also, I have SQL2k and I see define columns not create option. You may have to reinstall client

[Paulo]

!!! Got it !!!

The table name was blank because it had errored...but...it turns out, it was a client issue. I ran the version I have installed locally and I got the define columns button which works as I remember SQL7 working... The version I was running before was through a citrix network and is obviously different somehow (although both say version 8)

...either way, thanks for your help - I'm off to try and catch up on this project before the weekend starts...

Have a top one

Paul