Hi Iam trying to create a package which will primarily needs to insert a record into a table and send an email to the appropriate recipient and this process is a repetetive process based on the number of records that get inserted I am using a foreach loop container in which i placed a execute sql task and gave the sql logic that would run I have inserted a send mail task to which i need to send the email as a parameter from the sql task how do i acheive this functionality Thanks

I think the best approach is to load the records into the Object SSIS type of a variable and then shred it - where you simpy iterate over each record and send emails in a ForEach Loop Component. This is how you "shred": http://www.sqlis.com/post/Shredding-a-Recordset.aspxArthur My Blog

I think the best approach is to load the records into the Object SSIS type of a variable and then shred it - where you simpy iterate over each record and send emails in a ForEach Loop Component. This is how you "shred": http://www.sqlis.com/post/Shredding-a-Recordset.aspx Arthur My Blog looks like this is something that i want to do.I will follow the step by step process to acheive what iam trying to do

please ignore below i got helped by going over it again thanks The first error i got after i configured the recordset is.would you please be able to to tellwhat i might be doing wrong error at data flow task[Recordset[16]].The variable name property is not set to the name of a valid variable.Need a runtime variable to write t

Check if you are following the answers in the Forum URL below Forum AnswerRegards, Dinesh

I have been getting two errors following the link in exact fashion Error1 Error: ForEach Variable Mapping number 1 to variable "User::requestid" cannot be applied. The requestid is mapped to 0 in the destination recordset Error2 Error: The type of the value being assigned to variable "User::requestid" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. created package level variables(2 of the variales are int16 and one is string I used an oledb source.Three columns in my select statement Recordset destination configured Connected the data flow to foreach loop mapped the variables getting error

I guess there two items to look at: the expression used, and/or variable declaration and its scope. You have definitely mapped the return parameters wrongly. Review the package against the example.Arthur My Blog

Return Parameters - can you please elaborate on this I have tried to repeat the example again and again and it does not work the data flow executes ok and it errors out at the foreach loop

See this comprehensive post: http://geekswithblogs.net/stun/archive/2009/03/05/mapping-stored-procedure-parameters-in-ssis-ole-db-source-editor.aspxArthur My Blog

What went wrong? Can you follow http://www.sqlservercentral.com/articles/Integration+Services+%28SSIS%29/64014/ to have the Object variable populated?Arthur My Blog

What went wrong? Can you follow http://www.sqlservercentral.com/articles/Integration+Services+%28SSIS%29/64014/ to have the Object variable populated? Arthur My Blog i followed stp by step and inserted a send mail task instead of script task and get the below error [Send Mail Task] Error: An error occurred with the following error message: "The specified string is not in the form required for an e-mail address.".

It must be a typo or an incorrectly formed expression string Arthur My Blog

It must be a typo or an incorrectly formed expression string Arthur My Blog How would i attach my package so that you can have a look at it.I am just trying and trying but unable to make it work

Use skydrive mate https://skydrive.live.com/http://btsbee.wordpress.com/

Finally i got something to work and receving emails Thank you for all the help and direction

It must be a typo or an incorrectly formed expression string Arthur My Blog Hi I get a new error now which is as frustrating as the other I have in total 3 expressions that i created in the send mail task on three differant mappings In my select statement i am retreiving three columns and storing them into an object. variables expressions email @[User::email] ctname @[User::ctname] reqno @[User::reqno] Get an error whenever i use the third variable reqno. Error Error: ForEach Variable Mapping number 3 to variable "User::reqno" cannot be applied. Can you please direct me to resolve this error

The Index starts from 0. So your index values will be 0 for @[User::email], 1 for @[User::ctname] and 2 for @[User::reqno] http://btsbee.wordpress.com/

The Index starts from 0. So your index values will be 0 for @[User::email], 1 for @[User::ctname] and 2 for @[User::reqno] http://btsbee.wordpress.com/ i have only used 3 mappings 0,1,and 2.Not sure how andy why the 3(third) mapping is coming into play i changed the third column from an int32 to a string just for testing and it is failing with the same error.but when i remove the third mapping column alltogether the package works below is the sql in my exe3cute sql task Select C.Email,RR.ctname,RR.requestid,RR.reqno from Report_request RR Inner Join contact C On RR.requestor = C.contact_id where reqno in ( 148694, 148838, 148848, 148849)

Ok. So this is how your package should look: 1) Execute sql task with the following query Select C.Email,RR.ctname,RR.reqno from Report_request RR Inner Join contact C On RR.requestor = C.contact_id where reqno in ( 148694,148838,148848,148849) General-> ResultSet is set to "Full result set" and Result Set will have a variable mapping of type OBJECT where ResultName is 0. 2) Put in a for each container with Enumerator set as Foreach ADO Enumerator. Set Enumeration mode as "Rows in the first table" leave ADO object source variable as blank. In Variable Mappings specify Index - Variable as 0 - @[User::email], 1 - @[User::ctname] and 2 - @[User::reqno] 3) Put a send mail task within the ForEach container and configure it according to Arthur Z. link http://www.sqlis.com/post/Shredding-a-Recordset.aspx http://btsbee.wordpress.com/

Ok. So this is how your package should look: 1) Execute sql task with the following query Select C.Email,RR.ctname,RR.reqno from Report_request RR Inner Join contact C On RR.requestor = C.contact_id where reqno in ( 148694,148838,148848,148849) General-> ResultSet is set to "Full result set" and Result Set will have a variable mapping of type OBJECT where ResultName is 0. 2) Put in a for each container with Enumerator set as Foreach ADO Enumerator. Set Enumeration mode as "Rows in the first table" leave ADO object source variable as blank. In Variable Mappings specify Index - Variable as 0 - @[User::email], 1 - @[User::ctname] and 2 - @[User::reqno] 3) Put a send mail task within the ForEach container and configure it according to Arthur Z. link http://www.sqlis.com/post/Shredding-a-Recordset.aspx http://btsbee.wordpress.com/ getting the below error Error: Failed to lock variable "" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.". I am guessing that the error is due to leaving ADO object source variable as blank any thoughtsVamsi

Ok. So this is how your package should look: 1) Execute sql task with the following query Select C.Email,RR.ctname,RR.reqno from Report_request RR Inner Join contact C On RR.requestor = C.contact_id where reqno in ( 148694,148838,148848,148849) General-> ResultSet is set to "Full result set" and Result Set will have a variable mapping of type OBJECT where ResultName is 0. 2) Put in a for each container with Enumerator set as Foreach ADO Enumerator. Set Enumeration mode as "Rows in the first table" leave ADO object source variable as blank. In Variable Mappings specify Index - Variable as 0 - @[User::email], 1 - @[User::ctname] and 2 - @[User::reqno] 3) Put a send mail task within the ForEach container and configure it according to Arthur Z. link http://www.sqlis.com/post/Shredding-a-Recordset.aspx http://btsbee.wordpress.com/ If i have the ADO object source variable as an object variable then get the below error Error: ForEach Variable Mapping number 3 to variable "User::reqno" cannot be applied. Error: The type of the value being assigned to variable "User::requestid" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. Thanks Vamsi

Ok. So this is how your package should look: 1) Execute sql task with the following query Select C.Email,RR.ctname,RR.reqno from Report_request RR Inner Join contact C On RR.requestor = C.contact_id where reqno in ( 148694,148838,148848,148849) General-> ResultSet is set to "Full result set" and Result Set will have a variable mapping of type OBJECT where ResultName is 0. 2) Put in a for each container with Enumerator set as Foreach ADO Enumerator. Set Enumeration mode as "Rows in the first table" leave ADO object source variable as blank. In Variable Mappings specify Index - Variable as 0 - @[User::email], 1 - @[User::ctname] and 2 - @[User::reqno] 3) Put a send mail task within the ForEach container and configure it according to Arthur Z. link http://www.sqlis.com/post/Shredding-a-Recordset.aspx http://btsbee.wordpress.com/ The reqno feild is a numeric feild in our sqlserver 2005 database.I can make everythign work but not when i use this reqno in the mapping of the for each loop container. The variable i used for reqno is of datatype int32.Is this the cause of the issue how do i resolve this?Vamsi

My bad on ADO object source variable field. I wanted to say "leave ADO object source variable as object variable". Anyhoo I seem to have get this example to work. The problem is because of the numeric data type and weirdly SSIS is unable to map it to the variable. However this is how you make it work 1) Change the data type of User::reqno to Double 2) Change your query in the execute sql task to cast the req no field to a float value i.e. Select C.Email,RR.ctname,CAST(RR.reqno as float) as RR.reqno from Report_request RR Inner Join contact C On RR.requestor = C.contact_id where reqno in ( 148694,148838,148848,148849) rest all remains the same!http://btsbee.wordpress.com/

My bad on ADO object source variable field. I wanted to say "leave ADO object source variable as object variable". Anyhoo I seem to have get this example to work. The problem is because of the numeric data type and weirdly SSIS is unable to map it to the variable. However this is how you make it work 1) Change the data type of User::reqno to Double 2) Change your query in the execute sql task to cast the req no field to a float value i.e. Select C.Email,RR.ctname,CAST(RR.reqno as float) as RR.reqno from Report_request RR Inner Join contact C On RR.requestor = C.contact_id where reqno in ( 148694,148838,148848,148849) rest all remains the same!http://btsbee.wordpress.com/