passing variable from execute sql task to ole db source

Question

passing variable from execute sql task to ole db source

var05

Hall of Fame

Points: 3011
More actions
May 29, 2013 at 6:13 am

#274366

Hi All
I would like to know how to pass a paramater from Execute SQL task to OLE DB source
I have an execute sql task returing a full result query. And, I wanted to pass the parameter in OLE DB souce , so that the selected columns alone pass through the olde db source as a filter
Say, Execute SQL task returs top 5 employee names from a SQL table and I wanted to pass those top 5 employees as a filter in ole db query.
I can see we can use for each loop to enumerate the object using For each ado object. Is this the only approach or any other is there as well?
Any help on this? Thanks!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Bhaskar.Shetty Hall of Fame Points: 3054 More actions · Answer 1

Instead of passing the output back to OLDB Source (i.e. I am assuming you are taking about Front end code's here like .net C# or vb.net etc...) you should process the final output with filter and return it to source,

Phil Parkin SSC Guru Points: 246985 More actions · Answer 2

var05 (5/29/2013)
Hi All
I would like to know how to pass a paramater from Execute SQL task to OLE DB source
I have an execute sql task returing a full result query. And, I wanted to pass the parameter in OLE DB souce , so that the selected columns alone pass through the olde db source as a filter
Say, Execute SQL task returs top 5 employee names from a SQL table and I wanted to pass those top 5 employees as a filter in ole db query.
I can see we can use for each loop to enumerate the object using For each ado object. Is this the only approach or any other is there as well?
Any help on this? Thanks!

"selected columns"? Do you mean rows? If not, please elaborate on what you mean here.

Whatever query ExecuteSQL task is running, why not just replicate it in your OLEDB Source component? Set the Data Access mode to SQL Command, or SQL Command from Variable and you'll be able to do either enter your query as text or enter the variable which contains the query.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

var05 Hall of Fame Points: 3011 More actions · Answer 3

Thanks both!

Say I have a table in SQL where in I add and remove the employee names.

My Execute sql task will pick up the employees in that ABC sql table alone and pass them as a filter condition to the ole db source

I don wanna hard code the list of employees in ole db source. Say If i wanted to add/remove then I got to do it manually by opening the pkg everytime. Instead I want to add/remove the list in ABC sql table and pass it in filter condition in ole db source.

Hope you get what Im trying to achieve. any help?

Phil Parkin SSC Guru Points: 246985 More actions · Answer 4

var05 (5/29/2013)
Thanks both!
Say I have a table in SQL where in I add and remove the employee names.
My Execute sql task will pick up the employees in that ABC sql table alone and pass them as a filter condition to the ole db source
I don wanna hard code the list of employees in ole db source. Say If i wanted to add/remove then I got to do it manually by opening the pkg everytime. Instead I want to add/remove the list in ABC sql table and pass it in filter condition in ole db source.
Hope you get what Im trying to achieve. any help?

A bit of guesswork here, but I imagine that your ExecuteSQL task is giving you a list of Ids - and you want to select another record set containing only those Ids?

If true, why not put the whole query in your OLEDB source?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

var05 Hall of Fame Points: 3011 More actions · Answer 5

Ya you are right... List of ids to be pulled from one table and pass it to a query as a filter...

The problem is both the tables dont reside in the same server or database.. From the dynamic table I collect the list of ids using execute sql task and hold it in a variable and trying to pass the variable in the olde db source

Any better way?

Phil Parkin SSC Guru Points: 246985 More actions · Answer 6

var05 (5/29/2013)
Ya you are right... List of ids to be pulled from one table and pass it to a query as a filter...
The problem is both the tables dont reside in the same server or database.. From the dynamic table I collect the list of ids using execute sql task and hold it in a variable and trying to pass the variable in the olde db source
Any better way?

OK - now I understand.

Here are a couple of ideas:

1) Build a physical table in SQL to hold the Ids from the other server.

When the package runs, this table is truncated and then repopulated.

Then your OLEDB source query includes a JOIN to this table.

I'd use this method if lots of Ids are involved.

2) On a smaller scale (if there are not too many Ids). Put your Ids into a package string variable in this format (use FOR XML PATH etc):

(1,2,3,4,5,......) (user:Ids)

Build your query as an Expression in another string variable (SQLFiltered):

SQLFiltered = "select c1, c2

from t1

where t1.id in " + user:Ids

And then use SQLFiltered as your OLEDB source, with SQL Command from Variable as your Data Access mode.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

var05 Hall of Fame Points: 3011 More actions · Answer 7

var05

Hall of Fame

Points: 3011

May 29, 2013 at 9:03 am

#1619346

Alright thanks...will try out:)