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:

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!


Rudy Rodarte is a SQL Server Database professional based in Austin, TX. Over his career, Rudy has worked with SSRS, SSIS, performance tuning troubleshooting. When away from Keyboard, Rudy goes to Spurs and Baylor Bear sporting events.

More Posts - Website - Twitter