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



Monday 31 October 2016

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
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