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

My Last Twelve Month Post

Hello friends,

Here i am summarized my Last Twelve (12) Month post. 

Date	Blog Post	URL	Subject
27-Oct-16	SSIS Execute Process Task – TimeOut Property	http://sqlknowledgebank.blogspot.in/2016/10/ssis-execute-process-task-timeout.html	SSIS
08-Oct-16	Weekend SQL Workshop at Home	http://sqlknowledgebank.blogspot.in/2016/10/weekend-sql-workshop-at-home.html	Activity
30-Sep-16	SSIS Execute Process Task a Alternate Approach to Find File	http://sqlknowledgebank.blogspot.in/2016/09/ssis-execute-process-task-alternate.html	SSIS
03-Jul-16	Multi Columns Value Pivot in SSIS	http://sqlknowledgebank.blogspot.in/2016/07/multi-columns-value-pivot-in-ssis.html	SSIS
25-May-16	Early Arriving Facts	http://sqlknowledgebank.blogspot.in/2016/05/early-arriving-facts.html	SSAS
16-May-16	Is it Easier to Design Dimension And Fact Table	http://sqlknowledgebank.blogspot.in/2016/05/is-it-easier-to-design-dimension-and.html	SSAS
01-Apr-16	Understanding with Factless Fact Table	http://sqlknowledgebank.blogspot.in/2016/04/understanding-with-fact-less-fact-table.html	SSAS
22-Mar-16	XQuery Made Simple	http://sqlknowledgebank.blogspot.in/2016/03/xquery-made-simple.html	SQL
15-Mar-16	How to make the DE-Normalized DB for OLAP from OLTP	http://sqlknowledgebank.blogspot.in/2016/03/how-to-make-de-normalized-db-for-olap.html	SSAS
14-Mar-16	Incremental Load Made Easy in Case of UPDATE Records	http://sqlknowledgebank.blogspot.in/2016/03/incremental-load-made-easy-in-case-of.html	SSIS
11-Mar-16	Trying to Compare Between Two Table	http://sqlknowledgebank.blogspot.in/2016/03/trying-to-compare-between-two-table.html	SQL
10-Mar-16	Default Value of SELECT When NO Records Found	http://sqlknowledgebank.blogspot.in/2016/03/default-value-of-select-when-no-records.html	SQL
24-Jan-16	SSIS - Configuring Integration Service Catalog	http://sqlknowledgebank.blogspot.in/2016/01/ssis-configuring-integration-service.html	SSIS
24-Jan-16	Multi CTE is a Single SELECT Statement	http://sqlknowledgebank.blogspot.in/2016/01/multi-cte-is-single-select-statement.html	SQL
03-Jan-16	SSIS – Using BULK INSERT Part -II	http://sqlknowledgebank.blogspot.in/2016/01/ssis-using-bulk-insert-part-ii_3.html	SSIS
27-Dec-15	SSIS – Using BULK INSERT	http://sqlknowledgebank.blogspot.in/2015/12/ssis-using-bulk-insert.html	SSIS
18-Dec-15	SSIS – Incremental Process by CHECKSUM() Function	http://sqlknowledgebank.blogspot.in/2015/12/ssis-incremental-process-by-checksum.html	SSIS
13-Dec-15	SSIS – Dynamic Excel Sheet Choosing in Destination	http://sqlknowledgebank.blogspot.in/2015/12/ssis-dynamic-excel-sheet-choosing-in.html	SSIS
13-Dec-15	SSIS – Error Handling Design Pattern in Data Flow	http://sqlknowledgebank.blogspot.in/2015/12/ssis-error-handling-design-pattern-in.html	SSIS
12-Dec-15	SSIS – Where Destination is Excel	http://sqlknowledgebank.blogspot.in/2015/12/ssis-where-destination-is-excel.html	SSIS
10-Dec-15	SSIS – Related to Transaction in Case of Parallel Processing	http://sqlknowledgebank.blogspot.in/2015/12/ssis-related-to-transaction-in-case-of.html	SSIS
06-Dec-15	SSIS – Again Play with FLAT File	http://sqlknowledgebank.blogspot.in/2015/12/ssis-again-play-with-flat-file.html	SSIS
05-Dec-15	SSIS – Passing Table Variable Into Stored Procedure	http://sqlknowledgebank.blogspot.in/2015/12/ssis-passing-table-variable-into-stored.html	SSIS
04-Dec-15	SSIS – Passing XML parameter in a Stored Procedure and Retrieve the Result in Table	http://sqlknowledgebank.blogspot.in/2015/12/ssis-passing-xml-parameter-in-stored.html	SSIS
04-Dec-15	SSIS – Benefits of Execute T-SQL Statement Task	http://sqlknowledgebank.blogspot.in/2015/12/ssis-benefits-of-execute-t-sql.html	SSIS
03-Dec-15	SSIS – Data Type Vs SQL Server Data type	http://sqlknowledgebank.blogspot.in/2015/12/ssis-data-type-vs-sql-server-data-type.html	SSIS
01-Dec-15	SSIS – Working with Preformatted Excel and Stored Procedure	http://sqlknowledgebank.blogspot.in/2015/12/ssis-working-with-preformatted-excel.html	SSIS
01-Dec-15	SSIS – Choosing the Proper Destination for Performance	http://sqlknowledgebank.blogspot.in/2015/12/ssis-choosing-proper-destination-for.html	SSIS
28-Nov-15	SSIS – Case IN Sensitive Match by Lookup Transform	http://sqlknowledgebank.blogspot.in/2015/11/ssis-case-in-sensitive-match-by-lookup.html	SSIS
28-Nov-15	SSIS – Error Handling With ForEach Loop Container	http://sqlknowledgebank.blogspot.in/2015/11/ssis-error-handling-with-foreach-loop.html	SSIS
27-Nov-15	SSIS – Dynamically Sequential Execution of Child Package	http://sqlknowledgebank.blogspot.in/2015/11/ssis-dynamically-sequential-execution.html	SSIS
24-Nov-15	SSIS – Flat File Retrieval with Real-time Scenario Based	http://sqlknowledgebank.blogspot.in/2015/11/ssis-flat-file-retrieval-with-real-time.html	SSIS
22-Nov-15	SSIS – Working With Flat File	http://sqlknowledgebank.blogspot.in/2015/11/ssis-working-with-flat-file.html	SSIS
20-Nov-15	SSIS - Interview Question and Answer	http://sqlknowledgebank.blogspot.in/2015/11/ssis-interview-question-and-answer.html	SSIS
18-Nov-15	SSIS – Reading Excel Sheet With Different Meta Data and Store it in Multiple Table	http://sqlknowledgebank.blogspot.in/2015/11/ssis-reading-excel-sheet-with-different.html	SSIS
18-Nov-15	SSIS - Moving File on Different Folder Based on Size	http://sqlknowledgebank.blogspot.in/2015/11/ssis-moving-file-on-different-folder.html	SSIS
15-Nov-15	Complete demonstration of Web Service Task in SSIS	http://sqlknowledgebank.blogspot.in/2015/11/complete-demonstration-of-web-service.html	SSIS
11-Nov-15	Incremental Load without CDC in SSIS	http://sqlknowledgebank.blogspot.in/2015/11/incremental-load-without-scd-in-ssis.html	SSIS
09-Nov-15	Understanding CDC and Implementation in SSIS (Part – II)	http://sqlknowledgebank.blogspot.in/2015/11/understanding-cdc-and-implementation-in_9.html	SQL
07-Nov-15	Understanding CDC and Implementation in SSIS (PART-I)	http://sqlknowledgebank.blogspot.in/2015/11/understanding-cdc-and-implementation-in.html	SSIS
06-Nov-15	SELECT DISTINCT and GROUP BY not Work With XML Columns	http://sqlknowledgebank.blogspot.in/2015/11/select-distinct-and-roup-by-not-work.html	SQL