So I am designated at putting together an SSIS package to convert data between an Oracle View to a Sql Server Table.
I was looking for some design tips to put this thing together really well. I will explain the high level of the tasks.
So first this is an Oracle view ...I access it on my machine through an ODBC connection. I am creating an OLD DB Source in the SSIS package using the Oracle 11g Drivers.
I need to create a table in SQL Server so that nightly a job can run that basically takes the data from the Oracle view....and dumps it into a sql table....Each night I need this job to run and basically update the information.
Once this table is in SQL ..stored procs will run against that table which will update tables in my SQL database.
However I would think I would want to do some type of comparison between the Oracle View and the table its being dumped into ...like a merge....so that only certain data that has changed would change my master import table.
Any suggestions on the best way to set this up. I am thinking I will Need an OLe DB Source for the Oracle View...and SQL Server Destination ...but I would think I need something else in between so its not a complete dump.....
How would I manage this?