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