asked
SSIS SendHTML Mail as attachtment
Hi dear Community, I kind of desperate, please could anyone help me? I am very new into SSIS. My task is to send the records from a SQL table as an HTML email attachment using SSIS ? Following steps so far: In the SSIS package I have placed the following tasks: Execute SQL task, Foreach loop container, Script task within the Foreach loop container and Send Email task. I put the result set from SQL get data in a systemobject Variable ( NewVariableOject) ![alt text][1] In the next step I have configured the Foreach Loop Container and set the needed variable mappings. ![alt text][2] [1]: /storage/temp/3540-1.png [2]: /storage/temp/3541-2.png Further, this is my code in Skripttask public void Main() { Variables varCollection = null; string header = string.Empty; string message = string.Empty; Dts.VariableDispenser.LockForWrite("User::EmailMessage"); Dts.VariableDispenser.LockForWrite("User::LogID"); Dts.VariableDispenser.LockForWrite("User::PackageName"); Dts.VariableDispenser.LockForWrite("User::ComponentName"); Dts.VariableDispenser.LockForWrite("User::StartTime"); Dts.VariableDispenser.LockForWrite("User::Status"); Dts.VariableDispenser.GetVariables(ref varCollection); //Set the header message for the query result if (varCollection["User::EmailMessage"].Value == string.Empty) { header = "Execute SQL task output sent using Send Email Task in SSIS:\n\n"; header += string.Format("{0}\t{1}\t{2}\t{3}\n\n", "LogID", "Package Name","Start Time", "Status", "Component Name"); varCollection["User::EmailMessage"].Value = header; } //Format the query result with tab delimiters message = string.Format("{0} \t{1}\t{2}\t{3}\n\n", varCollection["User::LogID"].Value, varCollection["User::PackageName"].Value, varCollection["User::ComponentName"].Value, varCollection["User::StartTime"].Value, varCollection["User::Status"].Value); varCollection["User::EmailMessage"].Value = varCollection["User::EmailMessage"].Value + message; Dts.TaskResult = (int)ScriptResults.Success; } As a last step I send the E-Mail via Send Mail Task and it really works. However, I need the results as a table in an attachment in the mail. What do I need to change in my Visual C# 2012 Code? Please explain for beginners ;-) I am using Visual Studio 2014. Please help me out. Thanks in advance
If you want to send your SQL results as a table in an attachment in the email. I would suggest to keep it simple as below- 1. Use a "Data Flow Task" to extract the SQL request and place them in a file. Use the OLEDB Source connector for your SQL query and the Excel Destination connection to put them in a .xls file. (If you want the file as .csv or .txt - Use a Flatfile Destination) . If you don't know how to perform this step, check the internet for help. 2. Once the file is ready, use the "Send mail task" to attach the created file in the "Attachements" section and use it to send your email to desired recipients.
There is no "Send HTML Mail" task in SSIS. You will need to wield the .Net power via a "Script Task" to achieve the same.
Thanks, I am already doing it this way. Think I have unnecessarily complicated things :-) Just to be sure: there isn't any way to send a html mail via SSIS? I know there is a way via
asp.net , however, not in SSIS?
17 People are following this question.
