Friday, July 10, 2009

Using parameters in Oracle Queries via DTS

My latest adventure had me working with Oracle and SQL Server databases. The task required setting global variables in SQL DTS, then using those global variables in the Data Pump. However, it seems that the Oracle ODBC connection did not like me using the standard parameter formatting:



where COL4 = ?

As we all know, when you throw in a ? into a query, you pick the parameter at design time. But, no matter what I did, the task would not accept the parameter. It wouldn't even let me pick a parameter from the list, so I knew that I had to go about it a different way. As always, the trusty ActiveX task fit the bill. Here is a script to dynamically change the Query in a Data Pump:

Option Explicit

 Dim oPkg, oDataPump, sSQLStatement

 ' Build new SQL Statement
 sSQLStatement = "SELECT COL1, COL2, COL3" &_
 " FROM ORACLEDB.TABLE where COL4 = '" & DTSGlobalVariables("gv_Variable").Value & "'"

 ' Get reference to the DataPump Task
 ' This is for the first datapump. Change as needed

 Set oPkg = DTSGlobalVariables.Parent
 Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask

 ' Assign SQL Statement to Source of DataPump
 oDataPump.SourceSQLStatement = sSQLStatement

 ' Clean Up
 Set oDataPump = Nothing
 Set oPkg = Nothing

 Main = DTSTaskExecResult_Success
End Function

Now, the query text will include all of the previous text and the parameter.