Hi,
i have a question to make.
Is it possible to do the following query in the "Transform
Data Task" Source folder,

SELECT ? FROM TABLE

where the parameter would be a given GlobalVariable.

Thanks,
Paco Nassa

I'm sure there are other ways to do this, but this is what comes to mind.

First, I'm assuming that your plan is to change the Global Variable each
time.

Second, the whole query must go into the Global Variable string. Whatever
technique you're using to change the GV so that it knows what field to bring
back, use that to throw in the whole query.

Third, precede the first connection (of the Transform Data Task) with a
Dynamic Properties Task, tying them together with an OnSuccess arrow.

Fourth, use that Dyn. Prop. Task to set the SourceSQLStatement property of
the Transform Data Task using that GV.

Now, I know you probably just want to throw in the field of your choice into
that GV, and I'm just not seeing a way to parse the one GV into another so
that the SQL statement can be strung together. Well, of course, you could
do it with a stored procedure, and set one GV to have a SQL string based
what's in another GV. You can do that from a stored procedure by using the
sp_OA* tools, and then call that procedure using an Execute SQL Task prior
to the Dynamic Properties Task.

Best regards.


JOH



"Paco Nassa" <ricardo.silva (AT) link (DOT) pt> wrote

In addition to JOH's post you can see how to manipulate the statements in
DTS and substitute in Global Variables here

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


Nothe though that your transformations will be expecting the same fieldnames
in the SourceColumns collection. You can get around this by always aliasing
them as the same name

--


Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org