Is there a way to possibly add rown nr's to a Text file export using DTS.
The result in the text file should read

1,FieldNr1,FieldNr2,FieldNr3,
2,FieldNr1,FieldNr2,FieldNr3,
3,FieldNr1,FieldNr2,FieldNr3,
etc

instead of

FieldNr1,FieldNr2,FieldNr3,
FieldNr1,FieldNr2,FieldNr3,
FieldNr1,FieldNr2,FieldNr3,

Any help will be appreciated. I am also lookign at adding the value in teh
actual query but have trouble as I am using a UNION between 2 Select
statements on 2 different views.

Thanks

Marinus

Marinus,

Add an identity column to the destination table.

Ilya

"Marinus Willemstijn" <willemma (AT) telkom (DOT) co.za> wrote

Ilya.

I think we are going from SQL Server to a text file so there is no
Destination table.

You can use a Global Variable that counts in the Active Script transform to
do this.

The problem will be that your Text File needs this column viewable when your
create it so what you can do is this

SELECT.................., 'Dummy' as RowNum
FROM.............................

You then have a text file defined correctly

For the purposes of your transforms however you do not use the Source RowNum

You can populate the destination through the incrementing of a Global
variable.

HTH


--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Ilya Margolin" <ilya_no_spam_ (AT) unapen (DOT) com> wrote

Guys

Thanks for your feedback. Allan you are right - there is no destination
table beng a Text file. I will try and go read up on the Active Scripting as
I have not yet done it before (Delphi programmer!).

Any resources?

Thanks again for your kind help.

Marinus


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Oh yes


www.SQLDTS.com

++

This Newsgroup is very good for your troubles.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Marinus Willemstijn" <willemma (AT) telkom (DOT) co.za> wrote

My mistake.

Using activeX is one way. The following works as well. Use Execute SQL task
to create a global temp table with an additional identity column:

select *
,identity(int, 1,1) as RowID
into ##WithIdentity
from <Source table>

In the Transformation task specify ##WithIdentity as a source.

Ilya

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote