MS SQL Server DTS - Parameterised Query vs LookUps

Author

Parameterised Query vs LookUps

Neil

2005-07-19, 7:24 am

Hi,

I have a Transform Data Tasks which I'm using to load data from an Oracle DB
into a SQL Server DB. The data in Oracle is for all time periods but I want
to filter for a specific day, this day is not known in advance but can be
derived from a UDF in the SQL Server DB.

Pseudo code
Select * From OracleDB Where TransactionDate = Result of UDF

What is the best way to achieve this?

I cannot use the results of the UDF in the SQL Query because the connection
being used is the Oracle one so it doesn't understand this. I have read that
you can execute a look up against a diff connection and use this but i've
been unable to get this to work.

Essentially I need to execute this UDF to get my selected date and then use
this in the where clause to filter.

Thanks
N

Andy S.

2005-07-19, 11:23 am

You could create a global variable in the package to store the code for your
SELECT, then use a Dynamic Properties task to assign it to the Data
Transform task. Then you can build the SQL statement with an ActiveX
script.

http://www.sqldts.com has some good examples.

"Neil" <Neil@discussions.microsoft.com> wrote in message
news:1BEEE5D3-0F09-4009-99DB- 1034F790D005@microso
ft.com...
> Hi,
>
> I have a Transform Data Tasks which I'm using to load data from an Oracle
> DB
> into a SQL Server DB. The data in Oracle is for all time periods but I
> want
> to filter for a specific day, this day is not known in advance but can be
> derived from a UDF in the SQL Server DB.
>
> Pseudo code
> Select * From OracleDB Where TransactionDate = Result of UDF
>
> What is the best way to achieve this?
>
> I cannot use the results of the UDF in the SQL Query because the
> connection
> being used is the Oracle one so it doesn't understand this. I have read
> that
> you can execute a look up against a diff connection and use this but i've
> been unable to get this to work.
>
> Essentially I need to execute this UDF to get my selected date and then
> use
> this in the where clause to filter.
>
> Thanks
> N

Neil

2005-07-19, 11:23 am

Thanks Andy,

What I've ended up doing is creating a GlobalVariable and setting its value
via the Dynamic Properties task. My Data Transform then uses parameters to
access this global variable.

Personally I think this is a bit convoluted and I would like to keep all the
logic inside the Transform task but it doesn't look like this is possible. I
thought the lookups might give me the option to look this value up
dynamically and pass it too the where clause in my query but it seems that
its not really designed for this. Lookups appear to use the data from the
initial query as the input to the lookup whereas I want the opposite...

"Andy S." wrote:

> You could create a global variable in the package to store the code for your
> SELECT, then use a Dynamic Properties task to assign it to the Data
> Transform task. Then you can build the SQL statement with an ActiveX
> script.
>
> http://www.sqldts.com has some good examples.
>
> "Neil" <Neil@discussions.microsoft.com> wrote in message
> news:1BEEE5D3-0F09-4009-99DB- 1034F790D005@microso
ft.com...
>
>
>

Sponsored Links