July 23, 2012 at 3:17 am
Hi,
How to pass value to variable Of OLEDB Source of data Flow Task.
I am using SQL Command As Data Access Mode.In that I written an Dynamic Query.
And also I had Taken data Flow Task in a LoopContainer .
On Every iteration of loop I want to pass the variable value to data flow task.
Please suggest me how to do this.
Thanks in Advance !
July 23, 2012 at 6:10 am
Are you trying to have dynamic columns in your data flow?
If not, what is the purpose of the variable?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 22, 2012 at 1:23 pm
there are basically two ways to do this :
1)
Change data access mode of the OLEDB Source to SQLCommand and write a query like :
select * from your_table where col1_value = ?
and then map the '?' to a package variable through parameters on the Parameters Tab on the OLEDBSource Editor. This variable will ofcourse change its value as you have it configured in the foreach loop container.
2)
Set up a variable called(say) 'sql_query'
Change the DataAccessMode of your OLEDB Source to 'SQL Command from variable'. Choose the variable 'sql_query' to drive this command.
Set the value of the variable through an expression which basically makes use of the variable you had set up in the loop container.
This way, in every iteration of the loop, the loop variable will get a new value and correspondingly change the value of the 'sql_query' variable which in turn will be executed by the OLEDB Source. Your OLEDb Source inside a dataflow inside a foreach container is dynamic.
Hope this helps.
August 22, 2012 at 1:27 pm
avdhut.k (7/23/2012)
Hi,How to pass value to variable Of OLEDB Source of data Flow Task.
I am using SQL Command As Data Access Mode.In that I written an Dynamic Query.
Are you trying to change the server or database its aimed at? That's via an expression set to the variable which you adjust in your loop.
If you're trying to adjust a parameter IN the SQL statement, you use ? as a placeholder and then set the parameters themselves via the interface.
Can you be a bit more explicit as to your expectations? I'm afraid either English is your second language or I haven't had enough coffee today, so the more clearly you can explain your desired result the more likely we will be to be able to help you with correct explanations.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply