If I have a Transform Data Task importing a text file to
a table. The table has 1 more field (Trancount)which
is a Integer field, than the text file. Is it possible to
use the activex part of the Transform Data Task and inset
a number into the Trancount field for each record
inserted and if so How ??

I.e if the file has three records the table will have
the following data

Fld1 Fld2 Fld3 Fld4 Fld5 TranCount

A B C D E 1
A H D Q Q 2
D D D S D 3

Thnaks again for all your help

Yes

Use a Global Variable. You will also need to use an ActiveX transform.

You could also simply set the field to have an identity property



--

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

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


"Peter Newman" <anonymous (AT) discussions (DOT) microsoft.com> wrote

How Allen?

OK

Let's walk through an example then

CREATE TABLE AddAColumnAtRuntime(col1 int, col2 int, colRunningVal int)

My Text file looks like this

1,2
2,3
3,4
4,5
5,6


My ActiveX transform looks like this

Function Main()
DTSDestination("col1") = DTSSource("Col001")
DTSDestination("col2") = DTSSource("Col002")
DTSDestination("colRunningVal") = DTSGlobalVariables("gv_Counter").Value
DTSGlobalVariables("gv_Counter").Value =
Cint(DTSGlobalVariables("gv_Counter").Value) + 1
Main = DTSTransformStat_OK
End Function



SELECT * FROM AddAColumnAtRuntime

col1 col2 colRunningVal
----------- ----------- -------------
1 2 0
2 3 1
3 4 2
4 5 3
5 6 4

(5 row(s) affected)




--

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

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


"Peter Newman" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Allan,

What would i do with out your help. Thanks once again,
especially for the examples

Pete