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:
SELECT COL1, COL2, COL3
FROM ORACLEDB.TABLE
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
Function Main()
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.
