DTS Assistance required - Very Long!

Hello all,

Sorry for the length of the post but I want to be thorough and not waste anyones time suggesting something I've already tried. I have an application that pulls data from an oracle (8i) db into SQL Server for processing. At the moment it copies all of the data from the oracle tables into the SQL Tables, I would like to change it so that some of the tables only pull the data that has changes since the DTS package last ran. The first problem I encountered is that the Oracle OLE DB driver doesn't support Dynamic Parameters so I can't use

Select * from Table where Amended_On >= ? and Amended On <= Sysdate -1

(Well not that I would abuse the use of * in a Select query anyway )

Doing a search on Google revealed why I couldn't do it, and looking at www.sqldts.com revealed a possible work around was to add an ActiveX Scripting task to dynamically change the SQL of the Data Pump step along these lines

Option Explicit

Function Main()
Dim oPkg, oDataPump, sSQLStatement

' Get reference to the DataPump Task'
Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask

' Assign SQL Statement to Source of DataPump

sSQLStatement = oDatapump.SourceSQLStatement
' Build new SQL Statement

sSQLStatement = sSQLStatement & " AND TRUNC(AMENDED_ON) >= '" & _
Day( DTSGlobalVariables("LastRunDate").Value ) & "-" & _
UCASE(MonthName(Month( DTSGlobalVariables("LastRunDate").Value),True )) & "-" & _
Year( DTSGlobalVariables("LastRunDate").Value ) & "'"

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

' Clean Up
Set oDataPump = Nothing
Set oPkg = Nothing

Main = DTSTaskExecResult_Success

End Function

Which when I run the ActiveX step and then look at the Source of the Datapump the Clause has been added. Trouble is when I run the package I still retrieve 140000+ rows when I should actually only get one!

The other thing I've tried is to add an ActiveX Transform task to only copy the data if it falls between the date ranges and ordering the results by date descending. This works, but, due to the supplier letting the children into the coding room ,there isn't an index on the field I'm sorting on and the order by clause takes 5 Minutes(!!) which is longer than it takes to import the entire table, so no point there really.

I like the idea of Dynamically adding the parameter to the SQL but can't see what I'm doing wrong to get all the rows returned. If I copy the modified SQL and past it into PL/SQL developer, I get the right result.

Again I apologise for the length

Regards

TonyHi Tony,

This is a forum for the next version of DTS, called Integration Services, so you probably won't get enough qualified eye balls looking at your question to answer it.
Try the microsoft.public.sqlserver.dts newsgroup instead, that should have more activity on it.

thanks,
ash