SSIS Email attachment without saving attachment to file system

Question

I have an SSIS package that generates a text file, then at the the email task get the attachment and attaches to the email.

However, with our move to SQL2012 the folder location where the text file was temporarily created is not accessible. Is it possible to build an in-memory attachment that doesn't require write access to the file system and then attach it to the email message ?

Do you have the ability to create and clean up a temp "working" directory. You would be able to create your file in this directory and delete it after processing. Creating the file in a memory stream is possible but if that is the case you might as well turn it into a console app as its basically going to be one big script task. I know you were asking for a non write permission answer but I would have to assume that your server allows a certain level of write permission or at least the ability to create folders for processing in the temp directories — SFrejofsky
The user who kicks off the process should not have access to the folder. This is to prevent them from making changes to the attachments. But because they are running the process they need read, write and delete access to the files in the folder. Attempts to set this up using "execute as" and a different account has had us running around in circles with permission. How do we let them read write and delete from a folder but also stop them from getting to that folder through some other means? — ErickTreetops
There are two possible solutions the first suggested by SFrejofsky. Is to start a process within a code script, using an account which has permissions to read/write to the required folder. Possible, not a simple process. This is different from running the SSIS package as another account. Another solution depending on the size of the text is it embed the text into an email as part of the email text? see mssqltips.com/sqlservertip/1753/… — SQLBobScot
Its a little more work but you could also create your memory stream of the file store it as a byte array in an object variable and then serialize it and right the output in a later step in the ssis — SFrejofsky

Vikram D Vikram D · Accepted Answer · 2017-07-02T14:17:21

Not Possible. You can create the files, mail them and clean up the folder if you don't want to keep them.

SSIS Email attachment without saving attachment to file system

1 Answers