CODEWITHSUNDEEP
ssissendmailattachment
alee
alee

Reputation: 21

Attaching file with SSIS by matching filename with value in SQL table

I have a SQL table which structures as

Serial  EMAIL
543     [email protected]
789     [email protected]
485     [email protected]
654     [email protected]

and then I have pdf attachments in a folder where filenames are like

Happy 543.pdf
Happy 789.pdf
Happy 485.pdf
Happy 654.pdf

I want to match each serial number with filename's last 3 characters and then email that particular pdf to corresponding email address.

I have done extracting email address from table storing in a table and then using that email address with SENDMAIL task TOline expression property. Email goes according to plan. But when it comes to attach file when I dont understand how would i attach corresponding file with email. Someone please help it would be grateful

Upvotes: 0

Views: 2360

Answers (1)

billinkc
billinkc

Reputation: 61221

I assume your package looks something like this

enter image description here

Create variables

The use of Variables help separate the dabblers from the professionals. This is true because of the simple fact you can put a breakpoint on a package to inspect a variable. The same cannot be said of inspecting the expression on a task.

enter image description here As you can see, I have a few variables created.

  • CurrentID, String, populated from my foreach loop
  • Recipient, String, populated from my foreach loop
  • FolderInput, String, base folder where files will exist
  • ResultSet, Object, populated in the Execute SQL Task, shredded in Foreach loop
  • CurrentFile, String, defines the full path to a file. This is an Expression of @[User::FolderInput] + "\\Happy " + (DT_WSTR, 3) @[User::CurrentID]+ ".pdf"

As the Foreach loop enumerates, the value of CurrentID and Recipient change. By virtue of CurrentID changing, my mapping will be updated for each person so when the send mail task fires, it sends the correct file along.

Not addressed - dynamic file names and subfolders. The expression on CurrentFile assumes the file will exist and it will always be named Happy NNN.pdf where NNN is the CurrentID value. If you could have Hap 123.pdf, Joyous 123.pdf, Subfolder\Happy 123.pdf etc, then you're going to have do work, probably in a script task, to derive the value of the file name. That's a separate SO question.

Express yourself

On the Send Mail Task, the third tab exposes the Expressions for this task. This is the most powerful option on every Task in your SSIS toolkit. Here we will specify the recipient is the variable @[User::Recipient] and the file we are sending is [@User::CurrentFile]

enter image description here

One, fairly important thing to note here. If the file does not exist, the Send Mail Task will fail.

Upvotes: 2

Related Questions