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

Providing the Active Script task goes before the datapump task using workflow
and the statement in the DataPump task changes and the statement when used in
any other tool is evaluated correctly returning the 1 row you require then it
should work this way through DTS also.

Can you trace on Oracle what gets executed because it is obviously not the
statement you expect.

Thanks

Allan
www.SQLDTS.com

"BC DBA" wrote:

"Allan Mitchell" wrote:

Thanks Allan, Managed to get it sorted out. The problem was that Oracle was
incorrectly implictly casting the date that was passed, I would have thought
that
'23-MAY-2005' was pretty difficult to get wrong, However I've stumbled
accross these Date format problems with Oracle before So I amended the Where
Clause so that it was

WHERE AMENDED_ON >= TO_DATE ('23-MAY-2005', 'DD-MON-YYYY')

And this correctly returns the correct number of rows.

--
Regards

Tony