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