November 18, 2010 at 11:37 am
I am new to SSIS. I have a package where in my control flow page i have an execute sql task points to
data flow task.
In data flow task i have one source and one destinations.
In source i have written a query like: select * from year where month =@month
Now i want this parameter @month paramaters value to be passed from some other source task pointing
destination or from execute sql task where i can use query like: select currentmonth from months.
like in sql stored procedure i can use paramater and pass values how can i make a generic block(i don't know technical name of these rectangles) in design view so that i can be linked to other blocks.
point is that i want one block to pass data to other and the other to use that value.
and yes i dont want to make this through script but i want to make all this by design .
November 18, 2010 at 11:41 am
It depends. Are you trying to loop through values to assign, or just assign a value once?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 18, 2010 at 12:13 pm
If you want to store values in a variable with an Execute SQL Task, do the following:
* create a SSIS variable
* write your query in a Execute SQL Task. Make sure it returns only one value.
* On the General Pane, configure the resultset to Single Row.
* On the Result Set pane, map the result to your variable.
* If you want to use this variable somewhere else in an Execute SQL Task), use the parameter mapping pane.
* Somewhat trickier: if you want to use the variable in your OLE DB Source. Create a string variable and set EvaluateAsExpression to true. Write your SQL query for the source in the expression for this variable and include your variable there. (beware, there is a limit of 4000 characters). Use this string variable in your OLE DB Source by choosing "SQL Command from Variable).
If you need help with this, let me know.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 19, 2010 at 3:11 am
yes the solution is satisfactory but i want something different. i have an execute sql task and
in that i have used a query which gives me single value. i want this
value to be stored in a varible and then in data flow task i have
oledb source inside it and i want the output of execute sql task which is in variable
to be passed as parameter in the qeury statement of oledb source like we use in
stored procedure .
e.g. if i have a variable in execute sql task named: "currmonth", then i want this
value to be passed in the query of oledb source in data flow task .
something like"select * from table where month=@currmonth"( this is the
way we use parameter and pass values in and out) here @currmonth is the variable declared in
execute sql task and this variable contains the value which we get from the query statement from
execute sql task.. How can i achive this task of sending the
ouput of execute task to oledb source and then end value of oledb source to oledb destination.
November 19, 2010 at 3:17 am
You can't. That was what I was trying to explain.
You can't use variables/parameters directly in your OLE DB Source. You need to create a variable, construct your SQL statement there and then reference that variable in your OLE DB Source.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 19, 2010 at 3:39 am
Ya that's what the above solution gives.
In your dataflow task's oledb source select the data access mode as sql comman and
add the sql Command text and where ever you want to use the variable as a parameter add a "?". so when you click the parameter button then you will ghave the options to select the parameter. here you can select the Parameter as your Month (Which comes from EST).
Look at my attachments
November 19, 2010 at 3:57 am
sharath.chalamgari (11/19/2010)
Ya that's what the above solution gives.In your dataflow task's oledb source select the data access mode as sql comman and
add the sql Command text and where ever you want to use the variable as a parameter add a "?". so when you click the parameter button then you will ghave the options to select the parameter. here you can select the Parameter as your Month (Which comes from EST).
Dammit. You are absolutely right 🙂
I checked yesterday to see if the OLE DB Source could accept parameters. I looked to the left and I saw no parameter mappings pane, so I thought: no parameters. Obviously I forgot to check the right of the editor :blush:
My excuses to the original poster. Parameters are indeed possible in the OLE DB Source, see the explanation of Sharath.
I'd still prefer my method though, as using parameters in the OLE DB Source are subject to the SQL dialect used in the OLE DB source (meaning, it can change if you are using Oracle instead of SQL Server for example). With the SQL Command from variable approach, you are always safe. See the following url: http://sqldb.wordpress.com/2006/05/29/ssis-using-dynamic-sql-in-an-ole-db-source-component/
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 19, 2010 at 9:43 am
You could actually do it one of three ways. The first way would be like above where you are setting parameters and then using ? in place of your variables. Note that you must have the ? syntax in the order in which the variables are passed.
The second way you could do it would be to put the entire query in a variable and have your parameter variable contained within the query variable. This is limited to 4000 characters, however.
The third way would be to click on the properties of the data flow task from the control flow and use and set your query there with your parameter variable.
November 19, 2010 at 11:10 am
tmitchelar (11/19/2010)
The third way would be to click on the properties of the data flow task from the control flow and use and set your query there with your parameter variable.
Could you explain this a little bit more? You've sparked my interest 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 19, 2010 at 11:17 am
da-zero (11/19/2010)
tmitchelar (11/19/2010)
The third way would be to click on the properties of the data flow task from the control flow and use and set your query there with your parameter variable.Could you explain this a little bit more? You've sparked my interest 🙂
I'll try to explain this a little further tonight when I'm back on my laptop with BIDS installed. As of now I'm going on memory alone so bear with me!
If you single click on a data flow and then go to the properties and you will find a place where you can input a query as a type of expression. Here you could basically formulate the query you want and have it get set there. I've only had to use this option on a handful of occasions - typically when the source DB is a non-typical source like mySQL. Can't remember the exact reasons, but this was the only way I could get my parameters inside my OLE DB to work successfully.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply