September 6, 2007 at 10:59 am
In SSIS I'm importing oracle tables. What I would like to do is somehow set it up where these automatically run when a new date is added to the oracle table.
Our oracle tables are updated about the third business day of every month. I would either like to look at the table and if a new date exist then run my jobs or just set up my task to run on the fourth business day.
Thanks in advance for any help.
October 18, 2007 at 3:40 am
You could try adding the oracle server as a linked server to your ms sql instance using the Microsoft OLEDB provider for Oracle. Then have a query running to check for the latest date on the oracle server. Then run the sp_start_job Procedure in msdb database to run the SSIS package.
I havent tried using the oledb provider but this should work 😉
November 14, 2007 at 8:08 pm
Hi Bill
I've handled this type of processing using a separate "load control" table - which you can put in either your oracle instance or in sql server. The table has just a few columns: a char column for a table name (or job name or load name) and a datetime column for date of the last load.
You start out your package with a task that reads the load control table and sets a task variable to the date. After that you can include the variable in a property expression to feed your data flow task. Lastly, you update the load control table with the new last load date.
HTH
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply