I am using execute sql task to execute Store Procedures inside For each loop container and I am getting result set of three columns.Storeprocedure contain below select statement. Select SequenceNr,AlternateTtl, TtlType from mytable where wid = @p_wid and SeqenceNr >1 orderby SeqenceNr I know how to set fullresultset property and store that in Object variable but here I want each and every column value individually,store them in variables and use that individual value for creation of record in text file.Can someone help on that? Thanks for your help..Masti

Hi, how many rows returned by running this query? if just single row returned, set ResultSet property as "single row" and then in result set tab , set each variable name with result name for example: variablename result name ---------------------------------------- User::SequenceNr SequenceNr User::AlternateTtl AlternateTtl User::TtlType TtlType Note that you should create there 3 variables with appropriate datatypes before this.http://www.rad.pasfu.com

No, It does not return single row.It varies from 0 to 10 rows.Thts why I can not use Single row property.Can i use script task,I am not sure about it?Is there any other thing? Thanks 4 help..Masti

THIS MAY NOT BE YOUR ANSWER Because your result can be Dynamic, my suggestion use a SQL table and loop through the records one by one, i am sure that you wont be using all 10 variables (or maybe records) at the same time, anyways working dynamicly is hard, but once you get to know what you are looking for its easy. 1- so the return can be more that pne record, if its fixed as you mentioned 0-10 records make 10 variable and populate them , but if it can go over that , you have to make loops to loops through the records and save them in a variable one by one, ie you may have a formula of A + B / C * D so you will have to loop through the records 4 times (A - D ) and make the formula as a STRING formula (A + B / C * D ) and do the calculation to the final result. Please provide more examples , so that we can give you a better suggestionSincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

No, It does not return single row.It varies from 0 to 10 rows.Thts why I can not use Single row property.Can i use script task,I am not sure about it?Is there any other thing? Thanks 4 help.. Masti you can do this with an object type variable and a FOREACH loop, I mean,set result set as fullresultset and fill result in object variable and in a foreach loop , loop through the object variable, I think you know how to do it.http://www.rad.pasfu.com

I already treid it But it only stores the last row value from the result set but i want to store each and every row and column value.So it did not work good because i am getting mulitple rows with multiple column values.This sql task is already in for each loop container.So,when it passing the first value of @p_wid,it does not return any value for all three columns.Now when again passing another value of @p_wid in the sp,it returns 3 rows for all three columns. Select SequenceNr,AlternateTtl, TtlType Resultset: from mytable SequenceNr,AlternateTtl,TtlType where wid = 107744 and SeqenceNr >1 orderby SeqenceNr Select SequenceNr,AlternateTtl, TtlType Resultset: from mytable SequenceNr, AlternateTtl, TtlType where wid = 107745 1 abc AT and SeqenceNr >1 2 xyz AT orderby SeqenceNr 3 def AT I want to store each column value means For SequenceNr 1,AlternateTtl is abc and TtlType is AT.Same for all of them.can someone help on it?? Thanks 4 your all help.Masti

No, that wasn't what I meant. try this: set ResultSet property to FullResultSet then in result set tab, set variable name and result name ( result name should be 0 ) Note that you should put JUST SINGLE OBJECT TYPE variable here, let's name it MyQueryResult so your result set should be: variable name result name ----------------------------------------------- User::MyQueryResult 0 now put a foreach loop container after execute sql task, connect green arrow ( precedence constraint ) from execute sql task to this foreach loop, then set foreach loop enumerator as ADO enumerator, and set the User::MyQueryResult there, then in variable mapping tab, you can set variables like this : variablename index ---------------------------------------- User::SequenceNr 0 User::AlternateTtl 1 User::TtlType 2 http://www.rad.pasfu.com

Thanks Thanks Nik,but that is not the case here. 1) My execute sql task is inside the for each loop container.so whatever the input value I am passing in my sp it is coming one by one from upper for loop container.So for 1st input value sp return nothing,for 2nd input value sp return 3 rows for all three columns and ya recordset is not going above 10 rows.Now again for 3rd input value it does not reutrn anything.But when sp return Result set,i want to store Whole result set.so thts why i can not use 10 variable here . Execute sql task | for Eac loop container Execute sql task execute sql task Execute sql task ---this one i am talking about.I want to store all the return values for this task.Hope you are get some idea..Please help meon this.. Thanks 4 ur help..Masti

Execute sql task | for Eac loop container Execute sql task execute sql task Execute sql task ---this one i am talking about.I want to store all the return values for this task.Hope you are get some idea..Please help meon this.. What do you want to do with last execute sql task result? do you want to import them into a destination? if yes, you should use DATA FLOW TASK instead of execute sql task and write your sql statement in an OLEDB Data source.http://www.rad.pasfu.com

thanks reza but i already did it ,but it gives me only last row output from the result set of three rows.I will now try to modify my sp using temp table and inside the sp i will store all the values in temp table. Thanks a lot 4 help..Masti

I used Data flow task But it replaces values and give me only last ouput value..Masti

I used Data flow task But it replaces values and give me only last ouput value.. Masti let me know how did you configured OLEDB Source in data flow task?http://www.rad.pasfu.com

Thanks , Ohh yes..i configured it wrong ..can you please tell me more on that..how to configure it using variableMasti

use an OLEDB Source and set up an OLEDB Connection to appropriate database, then set "data access mode" as sql command then write your query in sql command box, like this: Select SequenceNr , AlternateTtl , TtlType from mytable where wid = ? and SeqenceNr >1 orderby SeqenceNr in this statement , question mark is parameter marker, then click on parameters button and set this parameter value with appropriate variable Note that you should set a default value for that variable. then you can go to columns tab, and see what your output structure looks like then put a destination to any place you need ( flat file, excel , or oledb destination) and set mappings, that's all. http://www.rad.pasfu.com

I can not see any rows return in the flat file destination.I put data flow task inside the for each loop and then i configure oledb source and flat file destination.I put data viewer in data flow.I saw all the value passing from oledb to flat file .The data viewer shows all correct output values but i don't what happen at last..means when it finish debugging and when i open the flat file nothing return there not even last values,it's totally blank.Flat file have only column name and no values whic come from oledb source.. whts the problem here..i cant get it..please help me on this.. Thanks 4 your help..I really appreciate all help..Masti

do you receive any error on flat file destination or other place? or all steps go green? did you set Mapping columns correctly at flat file destination? could you give us a screenshot of your data flow schema?http://www.rad.pasfu.com

Hey thanks for your help..I got the answer..i did silly mistake ...I put chekbox right on overwrite data in file in flat file editor and thts why it overwrite everything.But now everything is good..and i can able to capture all output values. Thanks a lot 4 yourhelp.. I really appreciate your help.. Masti