I'm trying to figure out how to pass the same variable to the sql
statement and the transaction. I tried to specify a variable in my sql
syntax during the dts wizard but it didn't seem to like it. The value of
the variable will be used as a column in one place and a value for a
different column. What I'm looking for looks like this, where VARIABLE
is the same variable I want to pass:

select [results].[department], [results].[location],
[results].[VARIABLE]
from [results]

' Visual Basic Transformation Script
' Copy each source column to the
' destination column
'************************************************* **********************
*

Function Main()
DTSDestination("department") = DTSSource("department")
DTSDestination("location") = DTSSource("location")
DTSDestination("Answer") = DTSSource("VARIABLE")
DTSDestination("qid") = "VARIABLE"
Main = DTSTransformStat_OK
End Function

--------------------------------------
Michael Haas
WAN & Telecom Tech
Lenape Regional High School District

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

The problem will arise if you change the metadata for the transform. Make
sure you expose the [VARIABLE] as the same column Name/Alias.

select [results].[department], [results].[location],
[results].[VARIABLE] as MetaCol
from [results]


You can substitute the value of variable int your statement using this

Global Variables and SQL statements in DTS
(http://www.sqldts.com/default.aspx?205)

If you expose it as the same column Name each time then you will not need to
change it inside the transform as it will always be the same.


--

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


"Mike Haas" <mhaas (AT) mail (DOT) lr.k12.nj.us> wrote