My current assignment involves building Analysis Services cubes from a SQL Server datamart, populated from an Oracle 10g database server, using SSIS.
I spent some time over the last few weeks using the Microsoft connector for Oracle by Attunity (available for download here) to populate my SQL Server. In terms of performance, this new connector is a vast improvement over the old Microsoft OLEDB Provider for Oracle (MSDAORA) or the Oracle OLEDB provider (ORAOLEDB.Oracle). If you were previously using a linked server to an Oracle database to populate a SQL Server database, you definitely want to check out that connector.
One thing that took me some time to find out was how to parameterize the sql command that you pass to the connector.
In the SQL Server connector, you can use a variable containing your view name or your sql command, and you pass this variable to database driver.
In the Microsoft connector for Oracle by Attunity, not so obvious.. There is nothing in the screen that allows you to use a variable to parameterize your sql command.
All you can do in this screen is choose either a table name or build a hard-coded sql command.
After much fiddling with the interface, I finally got around to parameterize my sql command. The trick is to display the properties of the parent container (in this case, the data flow). There, using property expressions, you can freely modify the Oracle sql command.
Click on ‘…’ in front of “Expressions”. In the Property listbox, select SqlCommand
Click on ‘…’ next to “Expressions” and start building your sql statement using the Expression Builder.
Enjoy!
Subscribe