We need to import the data from Oracle 10g database to SQL Server 2005 database.
In order to accomplish the above, we used SQL Server Integration Services along with Business Intelligence Developer Studio.
There are around 17 packages, each retrieving data from Oracle and storing into SQL Server based on current date. Following are the steps used:
- Get current date from SQL Server [Execute SQL Task].
- Create Query based on date obtained from step (1) [Script Task].
- Based on query obtained from step (2). Inside a [Data Flow Task] following sub actions are performed:
Actions performed in [Data Flow Task]
- Retrieve data from Oracle database using query through [OLE DB Source].
- Perform any data modifications to correct the data [Derived Column].
- Store data in SQL Server [OLE DB Destination].
- Rejections are saved in text file [Flat File Destination].
View Complete Post