Hi,

Please guide and help me with an sample ETL package to extract data from SQL Sever Database and load into Oracle Database using SQL SERVER INTEGRATION SERVICES 2008. The requirement is for full load and incremental load both

Thanks

Hello Neeraj - You have many options to achieve this but First you need to install Oracle OLE DB Drivers both 32-Bit & 64-Bit  drivers and I suggest you use light-weight ODAC drivers for this. You can download the drivers from here:

http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html

You need both 32-bit and 64-Bit. 32-Bit drivers for creating and using OLE DB Drivers in SSIS (Which is a 32-Bit application) and 64-Bit for scheduling the package in SQL Server Agent (Which is 64-Bit application)

Using Attunity Drivers: If you are using SQL Server Enterprise Edition:

• Then you can use Attunity drivers for Oracle in SSIS package (These drivers provides fast data exchange & are very optimize for bulk loading between SQL Server & Oracle)
• Download Attunity drivers for Oracle from here: http://www.microsoft.com/en-in/download/details.aspx?id=29283
• Create SSIS package, add Data Flow Task
• In Data Flow Task, Add SQL Server as OLEDB Source and configure properties for connection manager
• Then use Attunity Oracle OLEDB as destination and configure properties for connection manager (here you can use either EZConnect method or you can also use TNSNames.ora file for configuring the Oracle SID parameters)

If you are NOT using SQL Server Enterprise Edition:

• Create SSIS package, add a Data Flow Task
• In Data Flow Task, Add SQL Server as OLEDB Source and configure properties for connection manager
• Then add OLEDB as destination and configure properties for connection manager (here you can use either EZConnect method or you can also use TNSNames.ora file for configuring the Oracle SID parameters)

You can also refer following links for straight-forward examples:

1. http://blogs.msdn.com/b/dbrowne/archive/2013/10/02/creating-a-linked-server-for-oracle-in-64bit-sql-server.aspx

2. http://sqlmag.com/database-administration/connecting-oracle-database-sql-server-and-microsoft-bi-tools

Hope this helps !

HI Manu,

Thanks for your suggestion

Will  Incremental Load work when data is moved from SQL Database to Oracle Database.

Thanks

Check the commercial COZYROC Oracle Destination component. It provides bulk-load capability which is 10-30x faster compared to regular data load.

Regarding the incremental load, you can use COZYROC Table Difference component which provides functionality to make your process work.

Hi NEERAJ,

In SQL Server, Change Data Capture offers an effective solution to the challenge of efficiently performing incremental loads from source tables to data marts and data warehouses. Creating a package that performs an incremental load of changed data involves the following steps:

1. Designing the Control Flow
           1) Calculate the starting and ending datetime values for the interval of changes to the source data that you want to retrieve. To calculate these values, use an Execute SQL task or Integration Services expressions with datetime functions. You then store these endpoints in package variables for use later in the package.
           2) Determine whether the change data for the selected interval is ready. This step is necessary because the asynchronous capture process might not yet have reached the selected endpoint.
          3) Prepare the query string that will be used to query for the change data.
2. Setting Up the Query for Change Data 
   
3. Designing the Data Flow
   

For more information about it, please see: http://msdn.microsoft.com/en-us/library/bb895315.aspx

Besides, the following blogs are also helpful:
http://sqlblog.com/blogs/andy_leonard/archive/2007/07/09/ssis-design-pattern-incremental-loads.aspx 
http://sql-bi-dev.blogspot.com/2010/11/incremental-load-using-ssis-package.html
http://msdn.microsoft.com/en-us/library/bb895315.aspx 

Thanks,
Katherine Xiong