"BC DBA" wrote:

> 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
>
> <b>Select * from Table where Amended_On >= ? and Amended On <= Sysdate -1 <b>
>
> (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
>
> <b>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 </b>
>
> 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
>
> Tony
>