SSIS Expression two filepath filename

Question

SSIS Expression two filepath filename

ringovski

Hall of Fame

Points: 3521
More actions
January 9, 2013 at 9:28 pm

#268604

Hi I am trying to email two document using variables, I can get one sent using the expression building in a send email task but how i do add a second one.
e.g
@[User::POSQLFilePath] + "\\" + @[User::POSQLFile] + @[User::PoFlatFilePath] + "\\" + @[User::PoFlatFile]
Thanks

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

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

Marbo SSC-Addicted Points: 460 More actions · Answer 1

Hi ringovski,

I don't fully understand what your point is but isn't it simple to use a second send email task with probably a second variable or expression.

Or create a task to fill a new variable some kind of list datatype of this variable should be object, with multiple rows(destinations or content)

Then you create a for each loop, to loop over these rows into your original variable to send multiple variable emails.

I hope this helps a bit.

Marbo.

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 2

Marbo (1/10/2013)
Hi ringovski,
I don't fully understand what your point is but isn't it simple to use a second send email task with probably a second variable or expression.
Or create a task to fill a new variable some kind of list datatype of this variable should be object, with multiple rows(destinations or content)
Then you create a for each loop, to loop over these rows into your original variable to send multiple variable emails.
I hope this helps a bit.
Marbo.

I beleive what hes trying to do is attach two documents to the same email rather than sending two different emails, and as Far as Im aware theres no way to do this in an email task, as the Attachements field isnt a collection and just accepts a single string (please correct me if im wrong).

_________________________________________________________________________
SSC Guide to Posting and Best Practices

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

One alternative is to use the Execute SQL task instead to run sp_send_dbmail, which allows multiple attachments.

John

ringovski Hall of Fame Points: 3521 More actions · Answer 4

Jason-299789 (1/10/2013)
Marbo (1/10/2013)
Hi ringovski,
I don't fully understand what your point is but isn't it simple to use a second send email task with probably a second variable or expression.
Or create a task to fill a new variable some kind of list datatype of this variable should be object, with multiple rows(destinations or content)
Then you create a for each loop, to loop over these rows into your original variable to send multiple variable emails.
I hope this helps a bit.
Marbo.
I beleive what hes trying to do is attach two documents to the same email rather than sending two different emails, and as Far as Im aware theres no way to do this in an email task, as the Attachements field isnt a collection and just accepts a single string (please correct me if im wrong).

Yeah that's it eaxactly, I will have a look at the stored procedure sp_send_mail

ringovski Hall of Fame Points: 3521 More actions · Answer 5

What I am trying to do is email from SSIS one dynamically changing text file and one excel file at the same time.

In my SSIS package I have declared four variables:

@PoFlatFile, string, values =*.txt

@PoFlatFilePAth, string, values = d:\xxxx\yyyy\abcd

@POSQLFile, string, values = sql.xlsx

@POSELFilePath, string, values = D:\ssss\cvcd\aaaa

My flat file connection uses the expression 'connection string' @PoFlatFile. Then I have data flow task to load the SQL file from the table. Then to a ForEach Loop container, to try find the dynamic flat file.

Loop:

Folder:d:\xxxx\yyyy\abcd (same @PoFlatFilePath)

Files: *.txt

Retrieve file name: full qualified

Variable Mappings: @PoFlatFile

Within Loop there is a execute sql task.

SET NOCOUNT ON

EXEC msdb.dbo.sp_send_dbmail

@recipients='harry.jones@something.com',

@subject ='Data Check' ,

@body = 'See attachments',

@body_format = 'HTML',

@file_attachments ='@POFlatFile;@POSQLFile'

So you can see I am trying to attach two variables for each file and email them. I was originally trying to use the expression builder in a send email task, but apparently you can't have two attachments in a expression.

Can anyone tell help me with this send dbmail or suggest another way to do it?

Thanks

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

You're right - you can't do it like that. Here's what I would do. It's from memory, so forgive me if the details aren't 100% accurate.

(1) Create a new SSIS variable called AttachmentString

(2) Set the Evaluate As Expression property of the variable to True

(3) Build an expression to evaluate the variable, something like this:

@[User::PoFlatFilePAth] + "\" + @[User::PoFlatFile] + ";" + @[User::PoSELFilePAth] + "\" + @[User::PoSQLFile]

(4) Use this variable as the final parameter for sp_send_dbmail

John