SSIS Fast Load Oracle Destination Step by Step :

SSIS Fast Load Oracle Destination Step by Step : 

To fast load the data into Oracle destination I am using here Oracle Attunity. Attunity help us to fast load the data. Here I am showing you step by step to load the data fastly for this I am using DB2 source.

Step 1: Take Dataflow Task for perform ETL operation.

Step 2 : Take OLEDB Source and create a connection which is your source database and choose SQL Command to write the script for fetch only those field which required.

Step 3 : Click on Columns tab.

Step 4 : Take Data Conversion Transformation if there is need in my scenario I need Data Conversion transformation to change some data types and their size.

Step 5: Here I have changed some columns data types according to destination columns data types.

Step 6: Take Oracle Attunity Destination and configure the connection string.
TNS Service Name: Is your TNS for Destination server
User Name: Server User name
Password: Server Password

Then Click on Test Connection if Test connection succeeded then press OK and again OK.

Step 7: Click on Name Of The Table Or The View Dropdown to select the correct table.

Step 8: Click on Mapping tab to map the columns and Click on OK.

Step 9: Click on RUN to Execute the Package.

It has taken only 0.22 Seconds to fetch 87,840 records.

Difference between OLAP and OLTP

What is the difference between OLAP and OLTP ?

* OLTP (On-line Transaction Processing) is characterized by a large number of short on-line transactions (INSERT, UPDATE, DELETE). The main emphasis for OLTP systems is put on very fast query processing, maintaining data integrity in multi-access environments and an effectiveness measured by number of transactions per second. In OLTP database there is detailed and current data, and schema used to store transactional databases is the entity model (usually3NF). Example: In a banking System, you withdraw amount through an ATM. Then account Number,ATM PIN Number,Amount  you are withdrawing, Balance amount in account etc are operational data elements.

* OLAP (On-line Analytical Processing) is characterized by relatively low volume of transactions. Queries are often very complex and involve aggregations. For OLAP systems a response time is an effectiveness measure. OLAP applications are widely used by Data Mining techniques. In OLAP database there is aggregated, historical data, stored in multi-dimensional schemas (usually star schema). Example: If we collect last 10 years data about flight reservation, The data can give us many meaningful information such as the trends in reservation. This may give useful information like peak time of travel, what kinds of people are traveling in various classes (Economy/Business)etc.

Online Transaction Processing (OLTP)	Online Analytical Processing (OLAP)
Designed to support Daily DML Operations of your application.	Designed to hold historical data for analyses and forecast business needs.
Holds daily Latest Transactional Data related to your application.	Data is consistent up to the last update that occurred in your Cube.
Data stored in normalized format.	Data stored in denormalized format.
Databases size is usually around 100 MB to 100 GB.	Databases size is usually around 100 GB to a few TB.
Used by normal users.	Used by users who are associated with the decision making process, e.g., Managers, CEO.
CPU, RAM, HDD space requirement is less.	CPU, RAM, HDD space requirement is higher.
Query response may be slower if the amount of data is very large,it can impact the reporting performance.	Query Response is quicker, management can do Trend analysis on their data easily and generate quicker reports.
T-SQL language used for query.	MDX is used for querying on OLAP Cube.
Backup religiously; operational data is critical to run the business, data loss is likely to entail significant monetary loss and legal liability.	Instead of regular backups, some environments may consider simply reloading the OLTP data as a recovery method.