May 29, 2013 at 6:13 am
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!
May 29, 2013 at 6:19 am
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,
May 29, 2013 at 6:30 am
var05 (5/29/2013)
Hi AllI 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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 29, 2013 at 7:13 am
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?
May 29, 2013 at 7:21 am
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 29, 2013 at 7:29 am
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?
May 29, 2013 at 7:50 am
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 29, 2013 at 9:03 am
Alright thanks...will try out:)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply