I'm exporting data to a csv file which needs to include some fields
that I dont have in my source table.

Some of these will have no value but must still be included in the csv
file

one will have a static value for all records and one will contain a
static URL ending in a querystring value from a data field (eg:
http://mydomain.com/filename?id=<my id field> )

How do I do this? I would appreciate any suggestions.
Thanks
Leslie

forgot to mention I'm using sql 2000

Leslie

On Wed, 14 Jan 2004 23:28:29 +0000, les wrote:

In message <dljb00hdhrg5q4vf95otqrjkcuvunvtsna (AT) 4ax (DOT) com>, les@?.?.invalid
writes
The simplest way to do this would be to use a source query like this-

SELECT Col1, Col2, Col3, '' AS Col4, '' AS Col5,
'http://mydomain.com/filename?id= + CAST(Col6 AS varchar(255)) AS Col6
FROM MyTable

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

OK the way I do this is:

1. In the Source Query statement add place holders.

select .............................., 'NewField' as NewField1, 'NewField'
as NewField2 from table

2. The text file will get created with two extra attributes

3. In the transformations section do not highlight the source attributes of
NewField and NewField2 highlight the others.
4. New Active script transform.

Function Main()
DTSDestination("au_id") = DTSSource("au_id")
DTSDestination("au_lname") = DTSSource("au_lname")
DTSDestination("au_fname") = DTSSource("au_fname")
DTSDestination("phone") = DTSSource("phone")
DTSDestination("address") = DTSSource("address")
DTSDestination("city") = DTSSource("city")
DTSDestination("state") = DTSSource("state")
DTSDestination("zip") = DTSSource("zip")
DTSDestination("contract") = DTSSource("contract")
DTSDestination("NewField") = "My Value 1"
DTSDestination("NewField2") = "MyValue 2"
Main = DTSTransformStat_OK
End Function

You obviously sub in your value for the new fields.

the outcome looks like this

"au_id","au_lname","au_fname","phone","address","c ity","state","zip","contra
ct","NewField","NewField2"
"172-32-1176","Mitchell","Johnson","408 496-7223","10932 Bigge Rd.","Menlo
Park","CA","94025",True,"My Value 1","MyValue 2"


--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


<les> wrote

Thanks, that was what I needed.

Leslie

On Thu, 15 Jan 2004 06:41:31 +0000, Darren Green
<darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote: