Welcome, Guest
Username: Password: Remember me
  • Page:
  • 1
  • 2

TOPIC: How send files as attachments from MS SQL-Table

How send files as attachments from MS SQL-Table 3 years 11 months ago #14731

  • grek
  • Topic Author
  • Offline
  • Fresh Boarder
  • Fresh Boarder
  • Posts: 19
  • Thank you received: 0
Hello!

We have:
1. Windows Server 2008 R2 x64
2. Advanced ETL Processor Ent (trial)
3. MS SQL Server 2014
4. TestDB on SQL Server
5. Table "Files" (id (int), FilesName (nvarchar(MAX)), File (varbinary(MAX)), msgTo (nvarchar(20))) in the table.

We tried to realize 3 steps:
1. Connect to DB >> Table
2. Convert "File" to attachment
3. Send e-mail with attachment

Step 1 and 3 are OK. We can connect to DB, can load data from SQL-table, can send e-mail. But we cannot attach file from sql-table during transformation.
If we save file from sql-table to local drive using SQL management studio query - everything is OK. We can save and open file thуn.

Can you help us to convert file from sql-table, attach them to e-mail?

Thanks.

Please Log in or Create an account to join the conversation.

How send files as attachments from MS SQL-Table 3 years 11 months ago #14741

  • admin
  • admin's Avatar
  • Offline
  • Moderator
  • Moderator
  • Deeds not Words
  • Posts: 7743
  • Karma: 33
  • Thank you received: 453
We will create a working example for you
Mike

Please Log in or Create an account to join the conversation.

How send files as attachments from MS SQL-Table 3 years 11 months ago #14744

  • grek
  • Topic Author
  • Offline
  • Fresh Boarder
  • Fresh Boarder
  • Posts: 19
  • Thank you received: 0
Thanks! I'll be waiting...

Please Log in or Create an account to join the conversation.

How send files as attachments from MS SQL-Table 3 years 11 months ago #14758

  • admin
  • admin's Avatar
  • Offline
  • Moderator
  • Moderator
  • Deeds not Words
  • Posts: 7743
  • Karma: 33
  • Thank you received: 453
OK here is our example
It is not the most elegant solution but it works ,

In order to make it work we have to save the file to the disk first

Please note that the blob field must be the LAST in query, otherwise the reader will fail


Here we are using two transformers

Fist one saves content of the blob field into variable

Second one saves it into the file


Actual calculation


It is possible to make this example simpler but we would need to extend the functionality of our software

I hope this information is useful for you
Mike

Please Log in or Create an account to join the conversation.

How send files as attachments from MS SQL-Table 3 years 11 months ago #14765

  • grek
  • Topic Author
  • Offline
  • Fresh Boarder
  • Fresh Boarder
  • Posts: 19
  • Thank you received: 0
Hi!

It's work! Thanks!
But one question - how can I send more then one attachment in one e-mail? For example, I have several records in the SQL-table with one recipient e-mail. And I'd like to send them only one e-mail with multiple attachments.
And how can I delete attached files from file system (c:\temp\1\... in example)?

Thanks

Please Log in or Create an account to join the conversation.

How send files as attachments from MS SQL-Table 3 years 11 months ago #14766

Excellent

1) Sending multiple files.
Try using semicolon as delimiter:
file1.txt;file2.txt;file3.txt

2) Deleting files.
I assume you will be running your transformation from the package so you can use file action to delete the files.
www.etl-tools.com/wiki/vimpe:packages:file_operation_action
Peter Jonson
Support Analist

Please Log in or Create an account to join the conversation.

  • Page:
  • 1
  • 2

We have 152 guests and 2 members online