0

i have this powershell script to send emails that set the variable from SSIS packages, but when i add multiple recipients, only the last one gets the mail from SSIS package(i.e. 3 in total) but all recipients receive mail from controlM and ControlM Shout(i.e. 2 in total). I still can't figure it out. Thank you

A. This is where i give name of multiple recipients say file1:

Set-Variable -option Readonly -Force -Name Email_List -Value "abc@gmail.com;def@gmail.com;ghi@gmail.com"

B. This is the powershell script in file2:

[string[]]$SendMail = $Email_List.split(';')
$strcmd += " /SET \Package.Variables[User::ToLine].properties[Value];""" + $SendMail+""" "

C. Here ToLine is the variable from SSIS package In SSIS package Send Mail Task has Expressions as:

ToLine = @[User::ToLine]

((I have tried providing mail in format: (mail1,mail2,mail3), "mail1","mail2","mail3" also in case of powershell

$Email_List.split(;)|%{"<$_>"}

and using join";"))

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
Tirtha Shrestha
  • 1
  • It really depends the format that package expects to see for multiple emails. You know you don't need SSIS to send an email, there are many other simpler options. – Nick.Mc Jun 27 '23 at 05:35
  • whenever i run the script providing mail1;mail2;mail3 it says the option /SET is not valid. I also tried using /VAR and directly by /COMMAND. But it says those options not valid. Similarly, when i try running the script using <>,^,~ the script runs but only last recipients get mail Reason for why using SSIS to send as email is we are running multiple SSIS package at once through ControlM using segments to run parallelly(its the main scope of our task) so. – Tirtha Shrestha Jun 27 '23 at 07:38
  • OK so the native SSIS sendmail task requires semicolon seperated. Have you actually inspected what is generated by your powershell script in file 2 (B)? it looks like it's only adding one email there. Lastly, since your argument contains semicolons, you need to use this syntax https://stackoverflow.com/a/43524740/1690193. It seems like you don't even need script file 2 (B) because the value is already semicolon seperated as required. – Nick.Mc Jun 28 '23 at 02:55
  • when i use email_list.split(;) i get email as email1[whitespace]email2 email3 as you mentioned if i provide email in format: email1;email2;email3 powershell throws error as: Argument ""\Package.Variables[User::ToLine].Value;mail1;mail2;mail3"" for option "set" is not valid. – Tirtha Shrestha Jun 28 '23 at 07:33
  • Please edit your question and post the full `DTEXEC` cmdline that your scripts are generating. i.e. print out the value of `$strcmd`. That's the only way to work this out. You need to work out how to escape `;` values for `DTExec` or you need to use some other acceptable seperator and add a step to replace it inside your SSIS package. Are you able to change the SSIS package? – Nick.Mc Jun 29 '23 at 03:41
  • yes i did changes in SSIS packages in such a way that the powershell script sends value to ToLine(variable of SSIS) in format email1 email2 email3 by replacing whitespace to ':' using expression: replace(@user::ToLine," ",";") Now all the recipients are getting mail thanks @Nick.McDermaid for your suggestions I am also wondering if we can do some changes in powershell script without changes in SSIS packages(because we have 100s of SSIS packages which seems to be difficult to change manually fyi: we are not generating these packages from parser) – Tirtha Shrestha Jun 29 '23 at 05:45
  • Are you saying you have this email functionality in 100s of packages? You have some substantial design issues there. As I said you need to print out `$strcmd` and take the time to work out the exactly correct syntax to allow `DTEXEC` to accept parameters that contain `;`. There will be some combination of `"` that lets it work (as per the SO link I posrted). You need to sit down and work that out. Then you need to work out how to produce that from your code. – Nick.Mc Jun 29 '23 at 06:26

0 Answers0