I have 2 Execute SQL tasks - No data flow task. On Execute SQL task 1 I have query to pull firstname, lastname from contact table select firstname, lastname FROM [AdventureWorks].[Person].[Contact] On Execute SQL task 2 I want to insert those values to newly created table using parameters. ( i have 2 variables declared at package level as firstname, lastname) insert into cnt(firstname,lastname) values (?,?) My question 1. Is it possible to pass values multiple times (or values) using variables. 2. What parameter Direction type & Result Set I need to use in Exec SQL task 1 and 2.

Save the result of the first execute sql task into an object variable and use a for each loop container to iterate trough the values saved in object variable and save the individual values (firstname and lastname) in string variables (varfname,varlname). Inside foreach loop have another execute sql task with insert script and pass the parameters with direction as input , datatype as varchar and prameter name as 0 and 1. This approach would iterate through all the values and insert one row at a time in the destination table. Now, the question is why can't you use a data flow task (instead of execute sql tasks) with OLEDB Source pointing to source database and OLEDB Destination pointing to destinaiton database?Nitesh Rai- Please mark the post as answered if it answers your question

Thanks for your attention and suggestion. I am trying to learn by doing this way to learn about Execute SQL task and passing parameter back and forth. I am beginner to SSIS. I have done what you suggested. to keep it simple I kept top 10 rows at 1st Execute SQL Task. With no errors SSIS package run successfully but no data in destination where I could see 10 empty rows updated with package run. Trying to digg whats wrong. Any help would be highly appreciated. Thanks, Ela

you should use a foreach loop to loop through this object variable, look at Nitesh posts which described this completely. But I have Nitesh question too, why you don't use mere a data flow with an oledb source and oledb destination? this is extremely simpler than what you try to do here!http://www.rad.pasfu.com

Thanks for your attention and suggestion. I am trying to learn by doing this way to learn about Execute SQL task and passing parameter back and forth. I am beginner to SSIS. I have done what you suggested. to keep it simple I kept top 10 rows at 1st Execute SQL Task. With no errors SSIS package run successfully but no data in destination where I could see 10 empty rows updated with package run. Trying to digg whats wrong. Any help would be highly appreciated. Thanks, Ela I have used for each loop container as suggested by Nitesh Rai.

That's OK. and how did you set Variable mappings tab of foreach loop container? did you set variables with indexes there? like this: variablename index --------------------------------- FirstNameVariable 0 LastNameVariable 1http://www.rad.pasfu.com

Wow Great. I had my object variable there...Once I updated with first name and last name variable mapping. Now rows got update correctly. You guys rock ! Thanks Nitesh Rai and Reza Raad !