| Stephen 2005-12-22, 3:24 am |
| I am trying to configure the WHERE clause for the SQL statement I am using in
an OLE DB Source for a data flow so that I can do an incremental extraction
from the source system. The extraction criteria is based on a datetime
column on each of the tables in the source system. In my destination
database, I am storing the datetime of the latest record that was received
for each of the tables. This datetime is usually different for each table in
the source system.
I have been doing the following in DTS to achieve this in the past:
- extract a recordset from a SQL database that contains a list of tables and
dates that I last processed from the source system and temporarily store this
in a DTS Global Variable
- using an ActiveX script task, loop through the tasks collection finding
each datapump task. If the datapump task has a SQL source statement that for
a table in the recordset that has been stored in the global variable, update
the condition in the WHERE clause so that it uses the datetime from that
recordset.
Using SSIS, I have not been able to find an equivalent method to this.
The only option I can see is to create a SSIS variable for each OLE DB
Source and populate that variable with the relevant SQL. This requires
significantly more effort to code than my DTS datapump solution (which really
is the same code/tasks copied to each package and has minimal coding effort
required each time).
Hopefully, someone can tell me how to create the equivalent code (perhaps
using the SSIS Scipt Task
TIA
Stephen
|