Most of the time, the transformations between Oracle and SQL Server databases work without incident. But one project I was on experienced a several issues. 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. Now, please keep SQL Injection in mind. This particular project was not user facing and working off a read only database, so we moved ahead with the option above. Leave me a comment if this helped you out!
