August 19, 2003 at 8:02 am
I have the following source sql string in a transformation that pulls from an Oracle OLEDB connection.
I would like to create this string dynamically by pulling the CREDIT_SCORE list from
a table in SQL Server that contains the valid credit scores I want to pull on any given day.
I think I need an Active X script to generate a global string, and a dynamic properties task. I need
some help conceptualizing how to accomplish this.
SELECT C.ACCOUNT_NUMBER
, C.CURRENT_BAL
, C.BILLING_CYCLE
, C.COLLECT_STAT
, C.CREDIT_SCORE
, C.FILE_DATE
, D.HOME_PHONE
, D.NAME_LINE_1
, D.BUS_PHONE
FROM STG.TSYS_ACCOUNT_STATUS C
LEFT join STGDW.TSYS_ACCOUNT2_CUR D
ON C.ACCOUNT_NUMBER = D.ACCOUNT_NUM
WHERE C.CREDIT_SCORE in ('4344','4353','4349')
J. Moseley
[font="Courier New"]ZenDada[/font]
August 22, 2003 at 8:00 am
This was removed by the editor as SPAM
August 22, 2003 at 10:23 am
If I am understanding you correctly you might try creating a stored procedure that allows a parameter to be passed to it (Credit Score) and stores this in a table. Within the same stored procedure execute (xp_cmdshell) the DTS package modifying it to read from the Credit Score table.
September 3, 2003 at 10:44 am
Here was my final solution:
I used ADO in an ActiveX Script to generate the sourceSQL string, stuffed it in a global variable, then assigned this string to the sourceSQL string of the Oracle Data Pump Task by using the Dynamic Properties Task. It was really easy. The Dynamic Properties Task is a new feature in 2000. Back in the old days, all of this had to be done programmatically in an ActiveX Script referencing the various package objects in code.
Thanks to those who replied. I hope my answer prompts some of you to start using the Dynamic Properties Task in your DTS packages.
Cheers!
J. Moseley
[font="Courier New"]ZenDada[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply