Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

SSIS scenarios...
Browse by Tags · View All
MSBI 30
SSIS 27
BRH 15
SQL Server 15
#BI 14
Script Component 9
#SQLSERVER 8
#SQL Server 7
Flat File Source 5
Script Component Source 5

Archive · View All
August 2010 4
March 2011 3
September 2010 3
February 2013 2
June 2011 2
November 2010 2
May 2010 2
November 2009 2
March 2010 2
May 2012 1

SSIS - Upload data to Oracle using SQL Loader

Feb 23 2011 4:34AM by Sudeep Raj   

While doing ETL performance is of utmost importance. Keeping this is mind how do we load data to Oracle server using SSIS in the most optimum way? We can follow the conventional method of using OLEDB Destination in a Data Flow Task. This is what is used most often. I too used this to perform ETL. But was not very happy with the kind of performance I got. I looked for other options available and came to know about the SQL*Loader. now how do we use this with SSIS?

Step 1. If the source is a text file very good!! If not don't worry, use SSIS Data Flow Task to dump the data to a flat file(C:\Data\File.csv). We have the data in the following format after step 1. Columns are Name, Age & Sex (but we do not have them in the output - not mandatory would need to modify step in that case)

Sam,20,M
Daniel,59,M
John,45,M

Step 2. Create a Control file and name it Loader.ctl and paste the below code in there:

LOAD DATA
INFILE 'C:\Data\File.csv'
REPLACE
INTO TABLE <Schema>.<TableName>
FIELDS Terminated by "," TRAILING NULLCOLS
(Name, Age, Sex)

Step 3. Create a Batch file in the same folder where you create the above control file. In the batch file put the below code:

sqlldr username@server/password control=loader.ctl
Step 4. Create the package, After the 1st data flow task where you dump the data to a flat file. Have an Execute Process Task to point to the above batch file. Execute the package and your data will be loaded to Oracle table in much shorter duration.

Note- You can use SQL loader control file for a number of other scenarios not necessarily the one I showcased here. For more on SQL*Loader check this link.

 

Tags: SSIS, SQL Server, MSBI, OLEDB Destination, #BI, BRH, #TSQL, #SQL Server, Control File, Oracle, SQL Loader,


Sudeep Raj
12 · 13% · 4303
2
 
0
Lifesaver
 
0
Refreshed
 
 
0
Incorrect



Submit

6  Comments  

  • Hi Sudeep,

    I'm new to bulk data transformations DB2 to Oracle by using SSIS. can you please give the sample SSIS project for do this or more instruction for needfully in oracle side like csv file location

    Thanks, Jinesh

    commented on Aug 8 2011 11:10PM
    Jineshm
    2754 · 0% · 4
  • Hi Jinesh,

    how much data do you intend to transfer? You can use a Data FlowTask with OLEBD source to get the data from DB2(provided you have the connection) and oledb destination to load the data to Oracle. You might want to check the performance of this approach.

    Secondly you could read the data from DB2 in a Data Flow Task and dump it to a local(on the SSIS Server which should be having connection to the Oracle server) CSV file. Now use SQL loader as I have explained in my blog to load it to Oracle. This would definitely be much faster than the 1st approach.

    I need tim to craete the package and I donot have a connection to either Oracle or DB2 so the package would not be a tested one. Let meknow if you face a problem at any specific part while package creation.

    commented on Aug 9 2011 4:51AM
    Sudeep Raj
    12 · 13% · 4303
  • Thanks for your reply Sudeep ,

    We have 3.5 million records for upload in to oracle DB each day end and data have to extract from DB2 server. I did the test run by using SQLLDR and it’s faster than the OLEDB /ADO but my problem is data security, IS audit will ask to avoid the intermediate files during the ETL. Do we have any way of doing data transformation DB2 to Oracle fastest way without using intermediate files (CSV ) .

    Thanks, Jinesh

    commented on Aug 10 2011 10:37PM
    Jineshm
    2754 · 0% · 4
  • Using SSIS this is the best option that you have. Ma be you could delete the CSV file after the load or in case of error in the package as a clean up. This will ensure that no data is stored.

    There are other tools you could use have look at the links below:

    http://www.swissql.com/products/db2-to-oracle/db2-to-oracle.html

    http://forums.oracle.com/forums/thread.jspa?threadID=608738

    commented on Aug 11 2011 2:39AM
    Sudeep Raj
    12 · 13% · 4303
  • Hi, Sudeep:

    Would you please show me how to run sql loader in execute process task in SSIS?

    Thank, aq

    commented on Jun 19 2012 7:37PM
    aq
    2754 · 0% · 4
  • AQ,

    All you need to do is in the Execute Process Task, Goto Process Tab and in the Executable field put thepath of the batch file I mentioned above. Now run the package and it should work.

    commented on Jun 20 2012 12:29AM
    Sudeep Raj
    12 · 13% · 4303

Your Comment


Sign Up or Login to post a comment.

"SSIS - Upload data to Oracle using SQL Loader" rated 5 out of 5 by 2 readers
SSIS - Upload data to Oracle using SQL Loader , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]