CODEWITHSUNDEEP
sqlsql-serverssissubqueryetl
YannickIngenierie
YannickIngenierie

Reputation: 622

How to create a sourceOLEDB in dataflow with where clause is a subquery from another database

I've in a database some order ids. And I want select in another database take le line of these orders.

To achieve my goal I create a SQL task to get all orders id in variable (object) and a for each loop I include dataflow to extract each id to put in request of my ole db source. But it's long and I make lot of connections.

I want bypass this each loop and make one request with where clause using IN instead of = ?

But when I want map my parameter, I get an error saying it's not possible to use

SELECT .... WHERE Colon IN ?

Thanks for your help

Upvotes: 2

Views: 377

Answers (3)

userfl89
userfl89

Reputation: 4800

Are both databases hosted on the same SQL Server instance? If so, can perform the filtering in the initial query by using the three part names, for example Database.Schema.Table. In the example below, TABLE2 is used to check for matching IDs. Change the Data Access mode to SQL Command in the OLE DB Source and you can then reference tables in other databases (on the same server) using this naming convention. The INNER JOIN is used to return only matching rows from TABLE1. The question didn't indicate if the ID column was unique, so to be safe I assumed it isn't and DISTINCT is used in the subquery to ensure only unique values are returned. If the tables are in databases on different servers then a Lookup Transformation can be used to return only the rows from the source table with a matching ID in the table where the lookup is being done. This is further outlined below.

  • On the Lookup Transformation editor, choose the Redirect Rows to No Match Output option on the General page.
  • In the Connection tab, choose the OLE DB connection manager for the database containing the table that will be used to perform the lookup (filtering on ID). I'd recommend using the SQL query option instead of a table/view so that only the necessary column is retrieved. For the SQL query, write a query selecting the ID column from the table that will be used to match the IDs.
  • On the Columns pane, drag a line from the ID column in the Available Input Columns box to the ID column in the Available Lookup Columns box.
  • When connecting the output of the Lookup Transformation to the destination choose Lookup Match Output to return the matching rows. Lookup No Match Output can be ignored and these rows will be discarded.

Example Three Part Naming Convention:

SELECT
    T1.ID
FROM DATABASE1.SCHEMA.TABLE1 T1
INNER JOIN (SELECT DISTINCT ID FROM DATABASE2.SCHEMA.TABLE2) T2 ON T1.ID = T2.ID

Upvotes: 2

Hadi
Hadi

Reputation: 37328

You can use Expression to achieve that.

In this answer i will assume that you have concatenated all ID's in one string variable like 1,2,3,4,5.

Now you must create a variable of type string @[User::SQLQuery], and set the variable EvaluateAsExpression property to true, and define the use the following expression:

"SELECT * FROM SourceTable WHERE ID IN (" + @[User::InClauseVariable] + ")"

(Where @[User::InClauseVariable] is the variable that contains the values)

And in the OLEDB source change the Access mode to SQL Command from Variable and use the variable @[User::SQLQuery].

Upvotes: 1

J Weezy
J Weezy

Reputation: 3957

You will need to create two OLEDB source queries and then MERGE JOIN (use INNER JOIN) them on the column that you want to use for your IN clause.

Upvotes: 3

Related Questions