Solved

ssis 2005 passing variable to ToLine

Posted on 2009-04-09
15
949 Views
Last Modified: 2013-11-10
I am able to read an oracle table.
I can pass the result set to a for loop .
I can select each column in the for loop and diplay it in a script
dts.variables("strEmail").Value.ToString.

My sad state or despair:
After my script to display a message box, I have a send email control flow it works just fine if I hard code the ToLine.  
I can also use the message type and message source to pass in a variable for the message body.  NO PROBLEM, the emails work with ToLine hard coded.  
Here is the issue.  I want to be able to send (reminder) emails to a variable number of users (changes month to month) in the ToLine of the Send Email task in ssis 2005.  This will not work so far.  It seems the ToLine does not except a variable.  

My variable is User::strEmail.  Please note this variable does have the email address because if I use it in the body of the message and hard code the ToLine it works!!  I can see the email passed in as a vaiable in the BODY of the message for multiple messages sent to the same HARD CODED ToLine address.  I want to set the ToLine to this value.

I have tried in my ssis control flow send email task ToLine:
dts.variables("strEmail").Value.ToString
@[dts.variables("strEmail").Value.ToString]
User::strEmail
strEmail
@[User::strEmail]

I tried setting the Expression (in ssis Send Email Task) ToLine to dts.variables("strEmail").Value.ToString or User::strEmail. Using the expressions opton to set ToLine to a variable will not except it says evaluate to null.  

Is it possible to set ToLine in the Send Email task to a passed in variable?  Has any one got it to work? And naturally I may be doing something stupid, so forgive me if I am.
0
Comment
Question by:nutrino149
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
15 Comments
 
LVL 17

Expert Comment

by:HoggZilla
ID: 24108485
You can do this, you just need to use an Expression to define the ToLine value. See picture below.
ToLine-Expression.bmp
0
 

Author Comment

by:nutrino149
ID: 24110093
I have done this.
I have set ToLine drop down list select to
@[User::strEmail] selected from variables, typing.
The User::streMail works fine in the body of the email.

This is the error message when I try to run the job:
Error at Send Mail Task [Send Mail Task]: No recipient is specified.

0
 

Author Comment

by:nutrino149
ID: 24110204
I had a type-0 my email should read  all selected from drop down list and point and click "no typing".
I have done this.
I have set ToLine drop down list select to
@[User::strEmail] selected from variables, no typing.
The User::streMail works fine in the body of the email.

This is the error message when I try to run the job:
Error at Send Mail Task [Send Mail Task]: No recipient is specified.
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 17

Expert Comment

by:HoggZilla
ID: 24110213
Ahh, no value in the variable?  Put in a script task that includes a msgbox. Display the value fo the variable before the sendmail task.
0
 

Author Comment

by:nutrino149
ID: 24110269
I did that before I posted the message.
Sorry, but I am able to easily put the variable in the body of the email as a passed in variable.  I cannot get it to accept it as a ToLine variable, no idea why?  So yes I can my send email with a script message box displaying the variable.  I can send emails with the variale in the body of the message as I stated from the start.  I just cannot get it to be accepted as a ToLine variable.

0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 24110647
I am sorry if I am missing this, but are you using the Expressions tab to set the ToLine property? Not typing the variable into the ToLine field on the Mail tab.
0
 

Author Comment

by:nutrino149
ID: 24110810
I absolutely used the expression tab to set the ToLine property. Does the variable run for you in the ToLine if you execute the code?  

0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 24111155
Does the variable run for you in the ToLine if you execute the code?  
Yes. If the variable is the Expression, the Expression will evaluate when the SendMail task is executed. If the expressions returns a value then it will be in the ToLine - everytime.
0
 

Author Comment

by:nutrino149
ID: 24111182
Then there is something wrong on my end for I did set it in the expression.  I will delete and retry again.
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 24111289
Take out anything private and put the package out here. I will take a look.
0
 

Author Comment

by:nutrino149
ID: 24115017
I will after get one other item off my desk.
0
 

Author Comment

by:nutrino149
ID: 24164642
I have revisited this issue today, I began putting together some screen shots.  I will post the screen shots later tomorrow.
0
 

Author Comment

by:nutrino149
ID: 24313659
I apologize for this long delay.  I was pulled off on another project and had not had a chance to debug further.
0
 

Accepted Solution

by:
ee_auto earned 0 total points
ID: 26405870
Question PAQ'd, 500 points not refunded, and stored in the solution database.
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question