Send Mail task SSIS

Svlss
Svlss used Ask the Experts™
on
I am tryign to send emails to diffrent people
I created a ssis package
I am using Execute SQL Task
I set Result Set to Full result set
In SQL Statement i wrote a query which extracts list of emails ID's to which i have to send emails
In result set tab i gave name 0 and Created a vraible of type OBJECT  and assigned the variable name
then i am using a ForEach Loop container
in for each loop container in collection Tab is selected Enumarator as Foreach ADO Enumerator
Under Enumarator Configuration i am using Email Variable
And I selected Rows in the first table under Enumaration mode
Under variable Mappings tab is selected Email variable and Index as 0
Inside the Foreach Loop container i placed a send mail Task
After setting up a SMTP connection Manager
I Gave subject and message
in expression tab i am trying to assgin Toline property to emil variable. but i am gettign the error
is it because i selected variable as object?
How can i fix this?
error.bmp
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Christopher GordonSenior Developer Analyst

Commented:
What datatype is User::Email?  You should be assigning a string variable to the ToLine property of a SendMailTask. When pulling the emails from the database can you assign them to a string variable instead of Object?

Author

Commented:
If i assign it to a string then below error occurs. there is no error in query. If i select it as object it works fine
[Execute SQL Task] Error: Executing the query "select email from dbo.itemdetails  where stage in ( '2','3')" failed with the following error: "The type of the value being assigned to variable "User::Variable1" 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.

Open in new window

Christopher GordonSenior Developer Analyst

Commented:
How are you using the EXECUTE SQL  task to assign the value?  If so, what property do you have for the ResultSet?  Is it set to single row?

In the parameter mapping section of SSIS, are you specifing the correct parameter name and data type?
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
i am not using any parameter in parameter mapping. Do i have to use? if yes how do i use
SQLTask.bmp
Christopher GordonSenior Developer Analyst

Commented:
Yes, if your SQL Statement is returning an e-mail address, do the following.

1. Make sure you have a variable in your SSIS package called "Email" or something like that.
2. Your SQL Statement should include a single row return set that contains a field called "EmailAddress" or something like that.
3. Set the Result Set to "Single Row"
4. Switch to "Result Set"
5  Enter the field name of the email field from the sqlstatement specified initial screen.
6. Select your variable name

This will associate the value of a SQL Server Result to a variable which you can then use as value in your sendmail task.

Author

Commented:
As you suggested  i created a variable called emails type string
and set result set to single row
In for loop editor i selected Foreach ADO enumarator
and for object source Variable i selected the Variable Emails

But it  gives me the below error
Error: Variable "User::Emails" does not contain a valid data object
Christopher GordonSenior Developer Analyst

Commented:
Where is the error occuring?  Is your code getting past the "Execute SQL" task?  If so, can we verify the value of the email variable that you created.  

If the "Execute SQL" task is functioning, would you create a script task immediately after it that simply pops up a messagebox with the value of your  email variable?

Let me know if you need help on how to set that up.  

Author

Commented:
in script main task code i wrote the below code
and in read variables i gave User::Emails

Then i get the below popup window
Public Sub Main()
		'
        MsgBox(Dts.Variables.Item("emails").Value)

		Dts.TaskResult = Dts.Results.Success

Open in new window

error.bmp
Christopher GordonSenior Developer Analyst

Commented:
Calling Variable Names in a script task is case sensitive.  Would you double check that the case and spelling of your variable name?  Thanks!

Author

Commented:
Every thing looks good. The variable exists and i checked the case aswell.
Form Execution plan i am gettign the below error

Error: The script threw an exception: The element cannot be found in a collection.
This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.
Christopher GordonSenior Developer Analyst

Commented:
Did you specify the variable "Emails" as a ReadOnly variable in the Script Task?  It would be on the "Script" tab within the "Script Task Editor"  All variables need to be specified here first before they can be used within the script.  So if you select "Emails" as a ReadOnlyVariable you should be able to access it with the following code

Note C# code:
System.Windows.Forms.MessageBox.Show(Dts.Variables["Emails"].Value.ToString());

Author

Commented:
Sorry it was my fault as you said it was case sensitive issue. I corrected ti and ran the container and i gave emailID and every thing looks good now. As norw test is confirmed what do we do in actual container?
Christopher GordonSenior Developer Analyst

Commented:
OK, great.  Now we know that your Execute SQL task is correctly receiving an Email Address from SQL Server.  

BTW, when you say "emailID" you're referring to the entire email address, is that correct?  

We should now be able to use the value of the Email variable in our SendMailTask.  Again, that Email variable should be of type String, not Object.  

Return to your send mail task and set the "ToLine" property of the send mail task to the "Emails" variable you' ve created.

Run it and let me know what the next message is that we'll need to tackle :)

Author

Commented:
I tried that but its giving the below error

Error: Variable "User::Emails" does not contain a valid data object

Please look at the attachment that shows all the steps i followed
workflow.doc.docx
Christopher GordonSenior Developer Analyst

Commented:
Probably a scope issue.  Make sure your variable "User:Emails" is being declare in the scope of the package.  This will make sure that your SendMailTask will have access to it.
Christopher GordonSenior Developer Analyst

Commented:
To be more specific, make sure "User:Emails" is being declared in the scope of the Loop (assuming your send mail task is in your loop).  You can verify this in the variables window.  If it's not set to the scope of the loop...
1. delete the parameter
2. Select the Loop Task by clicking on it.
3. Return to the variable window
4. Create new variable (new button is near the top)
5. Name the variable Emails
6. Verify the scope is that of your Loop Task (which should occur automatically)
7. Set DataType to string.

Author

Commented:
But i f i declare Emails variable on loop how can i assign it to execute SQL task.  Does it have to be declared on sequence container?
Christopher GordonSenior Developer Analyst

Commented:
If the email address is being set outside the loop for use in the loop, then just set the scope at the package level.  Follow the same steps, but instead of clicking on the loop task in step2, click on the gray/tan area (don't select a control) in SSIS  in order to make the scope at the package level.  

Author

Commented:
Because i am passig values from SQL task to Variable and SQL task is int he container i set the Variable Scope to Container but still i am getting the same error
Package.bmp
Senior Developer Analyst
Commented:
Whoops.  Sorry, looks like i sent you down the wrong path initially.  Here's what we need to do...

1. Create a variable called Emails with scope in SSIS package of type Object.
2. Create a variable called Email with scope in SSIS package of type String.
3. In your Execute SQL Task.
a. Change your Result Set back to "Full Result Set"
b. On Result Set Tab, Set Result Name to "0" and Variable Name to your "Emails" object (plural)
c. Save Changes (Hit OK)
4. Open properties within the Foreach Loop Container
a. Verify on the collection tab that the ADO object source variable is "User:Emails"
b. Select "Rows In First Table" option
c. Select "Variable Mappings" tab
d. Set "User:Email" variable to Index 0 (assuming email address is first column of result set)
5. Make sure your Send Mail Tasks is set to the "Email" string variable.

So in short, your Emails object variablecontains the result of the stored proccedure.
The Email string variable contains the current instance of the email in the for each loop.

Author

Commented:
Dude u r Awsome. This time it worked... Thanks for your help and patience
Christopher GordonSenior Developer Analyst

Commented:
Thanks 4 your patience as well, I'm glad we got it!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial