Post reply

Setting task options/properties from package variables

  • June 5, 2008 at 2:39 am

    #125797

    Hi,

    I'm trying to do something that should be simple. I have an Execute SQL task that populates a package variable: User::Email

    I want to use the value stored in User::Email as the option/property of a Send Mail Task.

    I set the To property of the Send Mail Task to User::Email but at run time got the error:

    [Send Mail Task] Error: An error occurred with the following error message: "The specified e-mail address is currently not supported.".

    Can variables be used to populate properties at run time? An example or link to a resource would be most appreciated.

    Allen

  • June 5, 2008 at 3:14 am

    #824682

    Doh. I dont think it can be done. I think only some properties can come from variables.

    Anybody know of a workaround? Possibly using the DTS object??

    Allen

  • June 5, 2008 at 4:24 am

    #824694

    Uhmmmm, something is not kosher on your side.

    I had an outstanding task which pretty much involved using package configurations for the SMTP server connection string, the from address as well as the to address.

    This is what I did:

    a) I had a SMTP connection, and I added the SMTP connection to the package configurations (just call me pedantic, I created a SSIS_SMTP configurations table)

    b) I created a variable called varFromAddress. I assigned this an email address (for arguments sake AdminAlerts@sqlservercentral.com). Type STRING

    c) I created a variable valled varToAddress. I assigned this an email address (for arguments sake JoeSoap@sqlservercentral.com). Type STRING

    d) I added the Fromvariable to the send mail task, under the expressions FromLine and the ToVariable under the expressions ToLine.

    e) I added these to my SSIS_SMTP configurations table as well

    Its very easy to ensure that you configured this correctly. Close the package and re-open it. It should read your variables, and not complain.

    I deployed and ran this successfully. It mailed me

    Good luck on fixing your problem

    ~PD

  • June 5, 2008 at 7:00 am

    #824766

    Hi PD,

    Thanks for your help. I think I now understand that _some_ properties can come directly from a variable. But that all properties for a task can come from an expression and that expression can reference a variable.

    Allen

  • June 5, 2008 at 7:05 am

    #824772

    I didnt realise that you could set some properties directly from a variable.

    I thought that you had to tie the expression to a variable, with the expression containing the properties.

    Another way would be through package configurations, where you set the properties of just about anything that moves.

  • June 5, 2008 at 9:13 am

    #824876

    Yes _some_ properties seem to allow direct setting from a variable.

    E.G on the Send Mail Task the MessageSource property can come from

    Variable, Direct Input or File Connection depending on the setting of the MessageSourceType property.

    However I have noticed I have to prepopulate to User::Email variable with a dummy email address 'default@default.com' otherwise the package validation fails because the Expresion evaluates to Null/Blank.

  • June 5, 2008 at 9:17 am

    #824879

    There is a way to disable package validation, just dont know how.

    If you manage to figure that one out, please post.

    But if you use SSIS configurations, the variable will be populated with your value when the package loads

  • June 5, 2008 at 9:44 am

    #824910

    Thanks PD - I'm not up to the chapter on Configurations yet...

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply