March 19, 2009 at 5:44 am
I have a table called 'CentralData' with a column called 'Message'
The Data in this table is updated daily via a SQL Job (running an SSIS package).
What I want to do is send an Email if the words "News Client" appear in the 'Message' column.
1. Can this be done either through Database Mail (SP)
2. Within an SSIS package
March 19, 2009 at 11:52 am
You can use an SSIS package for this.
Frist create a variable to store a result set in.
Then you would want to create an 'Execute SQL Task' that will contain the query below and store the result set in the variable just created.
SELECT message
FROM CentralData
WHERE message LIKE '%News Client%'
Then using a 'Foreach Loop Container', you can traverse the rows in the result set and compose the email using a 'Script Task' and send the email using a 'Send Email Task'.
If you want wach row in the result set to be a seperate email, put the 'Send Email Task' within the Foreach Loop container. If all the results are to be sent in one email, put the 'Send Email Task' outside the Foreach Loop Container.
I hope this helps.
March 20, 2009 at 4:30 am
Thanks,
I am a bit new to SSIS but I have managed to do:
- create a variable
- create an 'Execute SQL Task' that will contain the query below
SELECT message
FROM CentralData
WHERE message LIKE '%News Client%'
NEED HELP HERE not sure how to do this: store the result set in the variable just created
Iv never used this for loop please help here:
Then using a 'Foreach Loop Container', you can traverse the rows in the result set and compose the email using a 'Script Task' and send the email using a 'Send Email Task'.
And I only want to send one email with all the outs puts,
THANKS AGAIN for your time
March 20, 2009 at 7:31 am
SQL TASK
OK, first make sure the variable you created is of type 'object'.
Then, when you open your SQL task, click on 'General' and set the ResultSet to 'Full result set'.
Now click on 'Result Set' in the left window. Click 'Add'.
For 'Result Name' enter '0' and then select your variable from the Variable name list.
click OK, and now your SQL task is complete.
**create 2 more variables that you will be using soon. make them both string varibales. One variable will store a single message, the other variable will store the body of the email message**
Foreash Loop Container
Open the foreach loop container. Click on 'collection'.
In the 'Enumerator' field select 'foreach ADO enumerator'
in the ADO object resource variable field, select your result set variable.
Click on 'Variable Mappings'
Select the string variable you just created (to store a single message) and assign it to Index '0'.
click OK, your done setting up the Foreach loop container.
Script Task
Drag a script task inside of the Foreach Loop Container
Open the script task. Click on 'Script'.
In ReadOnlyVariables enter the name of the variable you used above to store a single message.
In ReadWriteVariables enter the name of the variable you created to store the body of your email.
click Design Script. this will open a VB editor.
Your VB will look like the following (where i have emailbodyVariable you should put the name of the variable that stores the emailbody and where i have messageVariable you should put the name of the variable that stores the individual messages:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
'
Dts.Variables(" emailBodyVariable ").Value = Dts.Variables(" emailBodyVariable ").Value.ToString & Dts.Variables(" messageVariable ").Value.ToString & vbCrLf & vbCrLf
'
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
close the VB window.
click ok. You're now done with the script class.
Send Email Task
Put a Send Email Task outside of the Foreach Loop container
Open the Send Email Task. click on Mail.
Enter the mail info needed.
Set the MessageSourceType to 'Variable'
Set the MessageSource to the emailBodyVariable you created above.
Please let me know if you need more info 🙂
-Kim
March 23, 2009 at 2:44 am
Thanks very much for the detailed guide!!!
Error
Error: 0xC001F009 at Package4: The type of the value being
assigned to variable "User::Date" differs from the current variable type. Variables may not change type during execution. Variable
types are strict, except for variables of type Object.
Error: 0xC002F210 at Execute SQL Task, Execute SQL Task:
Executing the query "SELECT message
FROM Jobs_failed
WHERE message LIKE '%fail%'" failed with the following error: "The
type of the value being assigned to variable "User::Date" differs
from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of
type Object.
". Possible failure reasons: Problems with the query, "ResultSet"
property not set correctly, parameters not set correctly, or
connection not established correctly.
Task failed: Execute SQL Task
SSIS package "Package4.dtsx" finished: Success.
Plus, please can you confirm how the "Foreach Loop", "SQL Task" and "SendMail" are linked together.
Thanks again for your time
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply