Did you make sure the DBMail is configured/enabled and you have permissions for the account you are executing the package with to use it? If yes: Still need the SSIS expression where you assign the variables. Include the mapping picture.Arthur My Blog

I would load yor base HTML into a String variable, let's call it User::HTML_base, and put in some placeholders for parameters. For example "For questions, please contact: <paramater>" would be changed to "For questions, please contact: <!--Contact-->", etc. Now lets assume you have a variable named User::Contact. Add another variable called User::HTML_output and set its EvaluateAsExpression to True. Then in the Expression Builder, put in something like this: REPLACE([User::HTML_Body], "<!--Contact-->", [User::Contact]) You may need to nest the REPLACE statements, one level for each parameter. Click the Evaluate botton, then copy and past the results into an html editor to verify syntax. Now inside the loop, you can map parameters in the sp_sned_dbmail procedure to your Variables: EXEC sp_send_dbMail @body = ?, @Recipients = ?, etc.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Todd - The replace method you propose above - do I need to include any @ symbols - I'm getting all kinds of errors when I try to build that component KDW

Hve you tried executing the sp_sned_dbmail right from SQL Server Management Studio?Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Todd - Can I do 2 REPLACE expressions in onee variable? so REPLACE([User::HTML_Body], "<!--Contact-->", [User::Contact]), REPLACE([User::HTML_Body], "<!--ContactMail-->", [User::ContactMail])KDW

No, you need to nest the REPLACE statements like this: REPLACE ( REPLACE ( [User::HTML_Body],"<!--Contact-->", [User::Contact] ), "<!--ContactMail-->", [User::ContactMail] ) Start with the first REPLACE and verify it works. Then for each additional 'element' you are going to substitute, add a " REPLACE ( " at the front of your existing expression, and a " , <!--Element Name-->", [User::ElementName] )" at the end. Test after each change and save your changes as you go. Also make sure that at design time, that your variable have valid values so that the Expression evaluator is not trying to deal with NULLs. Hope this helps.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

If you were to execute this stored procedure from within SQL Server Management Studio (and I HIGHLY SUGGEST YOU TRY IT BEFORE MOVING ON), you would issue a statement like the following: EXEC msdb.dbo.sp_send_dbmail @recipients = 'joe.blow@anywhare.com', @subject = 'something', @body = 'something else' The @ symbols denote parameters for the stored procedure. For each paramter, you are assigning a literal value, enclosed in single quote. Get that to work in SQL Management Studio. Then COPY and PASTE it into an Execute SQL Task in Integration Services. Make sure it still works. Now, replace a literal value with a question mark, such as like this: EXEC msdb.dbo.sp_send_dbmail @recipients = ?, @subject = 'something', @body = 'something else' That ? represents a parameter that your statement needs, and will be passed to the @recipients paramter that the stored procedure needs. So, to make that happen, create a Variable of type String and assign it a value. Next go to the Parameter Mapping tab of the Task and ADD a new parameter. Select the one you created from the list of available ones. Set the Data Type as VARCHAR and the parameter name as 0. Here's how it all flows: The parameter supplies its value to the ? in the statement (there's only one, right?). That in turn gets assigned to the @recipients parameter of the stored procedure. The phrase @recipients = ? is what connects the two. If you remove the @ from it, you have invalid syntax.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

The previous sql task writes the variables that feed the vairables which are mapped so it's like this Cust_email = 0, CC_Email = 1 CONTACT_EMAIL = 2 Subject = 3 Body_HTML = 4 FilePathName = 5 These are in the chronological order of the paramaters above The subject and email body are variable expressions as Todd described how to do but I have taken everything out and tested just recipients and setting myself as a recipient that comes from a table and it still does not work... - i get the same error as above any hints?KDW

yes - but without paramters/attachments - it works greatKDW

Todd - I'm using SQL2K5 and I do not see where you can evaluate a variable as an expression KDW

Click on a Variable. Then look at the Properties window (usually located on the right side, below the Solution Explorer window). Look for a property called EvaluateAsExpression and set it to True. Below that is a property called Expression. CLick the elipsis (...) at the right of the value field to get into the Expression editor.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

My best suggestion at this point is this: Start with new clean SSIS Package and add a single SQL Task. Set it up to execute the sp and supply it literal values for all of the required parameters. EXEC msdb.dbo.sp_send_dbmail @recipients = 'joe.blow@anywhare.com', @subject = ... Test that. If it succeeds, move on. Then replace one literal with a variable" EXEC msdb.dbo.sp_send_dbmail @recipients = ?, @subject = ... and set up a Variable to use and map it in the task. Test it. Keep adding variables until the whole thing works with all variables.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

I am trying to use this msdb.dbo.sp_send_dbmail to send html formatted emails with attachments in the foreach loop i need to send in my package variables as paramaters to the exec for recipients, attachments. I also need to use variables embedded in the email for contact names, emails and then a corporate logo --here is what I've gotten so far but when i try to map my paramaters it does not work... EXEC msdb.dbo.sp_send_dbmail @recipients= '<variable_name>', @subject = 'Work Order List + <customer_VariableName>', @body = '<html> <body> Attached is the product update. This report highlights price changes. </br></br> For questions, please contact: <paramater> </br></br> Regards, </br> The McKesson Generic Rx Team </br> </br></br> <img src="http://www.company.com/img/logo_main.jpg"></img> </br></br> </br></br> Confidentiality Notice: blah blah blah </body> </html>', @body_format = 'HTML' @file_attachment = '<variable name>' @Reply_To '<variable>'; KDW

Error message when I tried...I'm really close - i can feel it? Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "EXEC Exec sp_send_dbmail @recipients = ?,@copy_recipients = ?,@reply_to = ?,@subject = ?,@body = ?,@file_attachments = ? ,@body_format = 'HTML'" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. KDW

"EXEC Exec ..." ?!? Make sure that either a) the connection manager you use points to the msdb database or b) you specify three part name in the EXEC statement: EXEC msdb.dbo.sp_send_dbmail @recipients = ? ... Also check your Variable Mappings. They should be named 0 through 6 (because you have 7 parameters). And watch the data types.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Can we see your Expression?Arthur My Blog

do i have to map the html - it is explicit....KDW

Everything is all strings, char, and varchar [Execute SQL Task] Error: Executing the query "EXEC msdb.sp_send_dbmail @recipients = ?, @copy_recipients = ?, @reply_to = ?, @subject = ?, @body = ?, @file_attachments = ? , " failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.KDW