Is there a way to pass database name as a parameter in DataStage ?
Basically, there can be many (hundreds) of databases on a single server, and I'm trying to find a way to dynamically switch the data source / target at run time.
I don't think this can be accomplished within ODBC Connector stage because DSN is bound to a single database (and I'm trying to avoid creating DSN for every database on the server).
Any way to accomplish this with SQL Server Enterprise or MS OleDB stage ?
Thank you!
How to pass database name as parameter for connectivity
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 13
- Joined: Wed Apr 07, 2010 11:50 pm
-
- Premium Member
- Posts: 120
- Joined: Thu Oct 28, 2004 4:24 pm
SELECT COMPANY_ID, ERP_DIVISION_ID, DIVISION_ID, LOCATION_ID, FEES_CODE_ID, FEES_CODE_DESC, FEES_CALC_CODE, UNIT_FEE_AMOUNT, FEES_CALC_TYPE, FEES_COMPARE_TEST_TYPE, FEES_COMPARE_TEST_AMOUNT, FEES_TIMING_FLAG, CHARGE_REG_ORDER_FLAG, CHARGE_POINT_OF_SALE_FLAG, CHARGE_BATCH_FLAG, CHARGE_CREDITS_FLAG, CHARGE_DROP_SHIP_FLAG, USE_ITEM_EXCLUSION_FLAG, ONE_CHARGE_PER_DAY_FLAG, ITEM_CLASS_ID, DROP_SITE_INVOICE_FLAG
FROM #STAGE_LIB#.dbo.tStage_#TableName#
Where
BIZSTAT = 'N' ;
FROM #STAGE_LIB#.dbo.tStage_#TableName#
Where
BIZSTAT = 'N' ;
"Don't let the bull between you and the fence"
Thanks
Gregg J Knight
"Never Never Never Quit"
Winston Churchill
Thanks
Gregg J Knight
"Never Never Never Quit"
Winston Churchill
Also, when you setup the DSN name in the odbc.ini file, you can leave the Database line blank if you don't want a default database:
Then the user name, password and database are passed into the job. We use ODBC Connectors like this and they work great.
Code: Select all
[DSNNAME]
Driver=/opt/IBM/InformationServer/Server/branded_odbc/lib/VMmsss24.so
Description=DataDirect SQL Server Wire Protocol driver
Database=
LogonID=
Password=
Address=hostname,1433
QuotedId=No
AnsiNPW=No