DTS Set a global variable to a query result

  • I have a developer who has asked for my help in editing a DTS job. The job currently truncates a table in a MSSQL database and then populates the table with data extracted from an oracle database. This has worked well for a long time, but now it is taking to long. There are 11 million rows in the Oracle table and about ten thousand are being added each day. What he would like to do is just extract the the new rows and insert these into the MSSQL database. The is an integer sequence number that is automatically incremented with each insert into the Oracle table. So we thought is should be pretty easy to query the MSSQL table to find the largest sequence number, then use that value in the where clause of the select query on the Oracle database. It appears to us that a global variable will be the easiest way to do this. But we have been unable to find a way to dynamically set that variable based on a select max(seq) from table query. We have found ways to set it statically, but that isn't much help. Can someone please point me to how to do this?

    Thank you.

  • You could use an ActiveX Script task, to get the value from your table and then store that into a Global Variable, since the global variables within the package can be accessed through an activex script (something like DTSGlobalVariables("YourValueHere").Value.

    Not ideal and if you were using SSIS it would be easier, but this should work.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply