Hi,
I currently use Transform Data Task to load data into a tbl. The source data
is a select stmt. I'd like to make the db names used in the select stmt
dynamic/varaible. I've unsuccessully tried using input params in the sql stmt
(the source tab). Also considered using an SP for the select stmt to create
the source data, but the output is a very large rowset (performance issues)
and won't be be possible to use the Transform Data Task to move the data
(have to resort to ActiveX datapump!).

Any suggestions are apprecaited.

I have added simplified code representing my select stmt in the Transform
Data Task, for ease of illust:

SELECT dw.cus_key, staging.cus_order
FROM [datawarehouse]..[customer] dw
INNER JOIN [staging]..[order] staging)
--I want the 'datawarehouse' & 'staging' db names to be variable (i.e. set @
exe time via GVs, INI, etc)

TIA

MA

The quickest way is to put in some global variables in the package and then
in the transform data

select * from dbo.aktivitet_dim
where aktivitetskode = ? and aktivitet = ?

Then you need to active the parameters button in the transform data task and
select these according
to the order of use in your sql statement.

You could also store these variables in a sql table and then read and use
this in the package.



"Bronz Fonz" <Bronz Fonz (AT) discussions (DOT) microsoft.com> wrote

Thanx for response Michael.
I'll add that the problem is with using an input param for DB names in the
SQL stmt. It seems a replacement of DB name with "?" doesn't work, whilst
using the "?" in a where clause (or perhaps after a comparison op.) allows
DTS to recognise the input param. Limitation of DTS i/p param?

"Michael Vardinghus" wrote:

Think you're right

I had some trouble at one point too but found out that when calling a stored
procedure from the execute sql task you can you use input parameters
for everything - not just where - statements.

Try this instead...


"Bronz Fonz" <BronzFonz (AT) discussions (DOT) microsoft.com> wrote