Monday 28 November 2016

Parameterized Query with OLEDB Source in Case of ORACLE

Introduction
SSIS OLEDB source the parameterized query with SQL server works Excellent. But with Microsoft OLEDB Provider for ORACLE is not working. So in this article I am trying describe related to Parameterised query with OLEDB source in case of MS SQL Server and ORACLE both.
  
Before going to deep drive, let’s understand the simple scenario.

Case Scenario
We have a Table object
SELECT * FROM tbl_Student;


IDNO
STDNAME
STDCLASS
1
Joydeep Das
1
2
Deepasree Das
1
3
Shipra Roy
1
4
Ripan Karmakar
1
5
Girish Agarwal
1
6
Shima Roy
2
7
Sukamal Jana
2
8
Sandip Dey
2
9
Raju Shiva
2
10
Ramgopal Naidu
2

We want to retrieve only Class 2 Student  from this table, so we need a parameterized query like this.

SELECT * FROM tbl_Student WHERE STDCLASS = 2;

IDNO
STDNAME
STDCLASS
6
Shima Roy
2
7
Sukamal Jana
2
8
Sandip Dey
2
9
Raju Shiva
2
10
Ramgopal Naidu
2

With Microsoft SQL Server and OLEDB Source

It’s quite simple






With ORACLE and OLEDB Source

Step-1 [ Create Variable ]



Step-2 [ Create Expression ]



Step-3 [ OLEDB Source Settings ]



Hope you like it.





Posted by: MR. JOYDEEP DAS



4 comments:

  1. hi joy i do not consider it as best approach as source is very easy in oracle and mysql but while going to destination it fails if you take odbc connection it increase the length of column pls call me

    ReplyDelete
    Replies
    1. Friend -- I know how tough it is to work with ODBC as Performance is concern.

      Delete
  2. It would be good if we can use column names instead of '*' to avoid any future failure.
    Working with variables in SSIS'

    ReplyDelete
    Replies
    1. @#$%^!* -- Look at the size of the table, Do u really think using * decrease the performance ?

      Delete