How to save an attached file in a Microsoft SQL Server Database
Email Parser can save attached files from an incoming email to a given folder with the “Save attachments” action. Setting this up is quite easy and straightforward as it only requires to enter the folder where the attachments will be saved. But in the case you want to save the file contents to a database this requires a bit of more work. This blog post shows how it can be done in a Microsoft SQL server database.
First, let’s see how the overall configuration should look on the left panel. Then we will explain all the details of each involved step:
First we have to create a database table where the files are stored. We have used SQL Server Management Studio to send this SQL command to the server:
CREATE TABLE [dbo].[Attachments]( [Id] [int] IDENTITY(1,1) NOT NULL, [FileData] [varbinary](max) NULL, [FileName] [ntext] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
It contains three columns: The first one is an integer that identifies the given file, this is automatically generated by the database, the second one contains the file contents and the third one the name of the file. Note that different attachments (from different emails) can have the same file name.
Now we have the database table set up. Let’s take a look at the first process where we define some useful values that will be used later:
The field sql_server_connection_string contains the connection string used to connect to the SQL server. And the pdf_file_path is where the attachments will be saved before being inserted in the database.
The next item is the one that actually gets the file from the incoming email. It does not require any further explanations as it is quite easy to understand to set up:
Then it goes a “Repeat for each field value” process. This item will run the next items for each value it finds on a given field. In this example, the field “Attachment”, which is created by the previous item, contains one value for each attachment found in the email. That field value will be the path where it has been saved:
And finally, the item that gives the name to this blog post. Here we will use the following SQL sentence to insert the attached file contents to the database table:
INSERT INTO Attachments(FileName,FileData) SELECT '<%Attachment%>',* FROM OPENROWSET(BULK N'<%Attachment%>', SINGLE_BLOB) AS import
An importan detail you have to be aware of is that the path of the attachment must be visible from the SQL server machine. For example if the attachment path is:
C:\Users\Carlos\Desktop\Attachments\thefile.pdf
The SQL server must be able to retrieve that file using that path. In this case SQL server and Email Parser were running in the same machine but if they are running on separate machines it is better to save the attachments to a network share, for example:
\\SQL-SERVER-PC\Attachments\thefile.pdfThis way the saved attached files will be visible from both sides with the same path.