Running SSIS Package from SQL Job - with Set Values, a string containing";"
Can anybody help with this problem please?I’m using “set value” in SQL agent to pass in an email address to my SSIS package, if I put in a value of: It works fine, but if I attempt to input multiple email addresses it fails; It doesn’t appear to like the “;”. Any ideas anyone???BoroFC
February 25th, 2010 3:03pm

Its probably because under the covers its calling dtexec and passing those values using the /SET option. If you look at the dtexec utility documentation you'll see that it describes the syntax of /SET as: /Set propertyPath;value As you can see it includes a semi-colon and hence I'm not surprised that a semi-colon in the value that you are trying to pass in causes a problem. As a workaround you could try wrapping your semi-colon delimited list in quotes: ";" I'm confident, but not 100% certain, that that will work! Let me know! -Jamie | @jamiet | About me
Free Windows Admin Tool Kit Click here and download it now
February 25th, 2010 3:18pm

Hi Jamie, many thanks for the response. If I look at my command line table when Email =, it looks like: /SET "\package.Variables[EmailAddress].Value";"" /REPORTING E If I change the value to “;”, it looks like: /SET "\package.Variables[EmailAddress].Value";"\""";"" ""\"" /REPORTING E But the weird thing is, if I close the job properties window and re-open it again, I lose this line from my set values screen all together!!! Help ???BoroFC
February 25th, 2010 3:31pm

Use comma instead.Hope this helps !! Sudeep Raj| My Blog
Free Windows Admin Tool Kit Click here and download it now
February 25th, 2010 3:39pm

Urgh. That doesn't work too well does it? Looks like a bug! If I were you I would ditch the use of the SSIS jobstep and use the command-line job step instead. Here's why: SSIS and SQL Server Agent - Choosing the Right Job Step Type (the third bullet point is the clincher) -Jamie | @jamiet | About me
February 25th, 2010 3:43pm

A comma doesnt work, it only sends to the 1st Email address in the list.BoroFC
Free Windows Admin Tool Kit Click here and download it now
February 25th, 2010 4:11pm

Hello, I can confirm your findings: inside [Send Mail Task] or a variable used in this task in a SSIS package: aaa@aaa.aa; – works in SQL Job, SSIS Package type, [Set Values] tab: aaa@aaa.aa, - job completes ‘ok’, but email sent only to 1st address (aaa@aaa.aa) aaa@aaa.aa; - job fails " aaa@aaa.aa;” - job completes ok, email sent to both addresses seems resolved but works only until you open the job again and the variable disappears! (so you have to enter the variables each time you modify the step) I know 2 workarounds: to use [Operating system (CmdExec))] type instead of SSIS Package type to execute DTEXEC my applied workaround – to use this expression for email To/CC line properties: REPLACE( @[User::SummaryMailToLine], ",", ";" ) (replace , by ; in (passed from SQL job) email addresses) and to use values for email addresses with comma inside sql job: aaa@aaa.aa, rob
February 1st, 2011 9:56am

I've just discovered that neither can you set a value of an email address of the form :- In general there are a lot of difficulties using the 'Set Values' tab of SQL Agent, even under 2008 R2. In addition if you have two email addresses of the above form in the package TO address of an email task ( a period appears before the @ symbol), a erroneous warning regarding an invalid email is shown. When trying to set a semi colon delimited list of two or email addresses of the above form via DTEXEC they must be quoted as in /SET "\Package.Variables[User::emailTo].Value";""";"""
Free Windows Admin Tool Kit Click here and download it now
February 20th, 2011 11:25pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics