Sending attachments in an email is a common and regular use case for almost all integrations, such as for notification or status monitoring purposes. It is sometimes crucial to have an email notification to inform us of the successful or failed job status, including any error details that may have occurred during the process. With that being said, we have seen many cases where the summary report is required after each ETL job run. In such situations, the email notification will need to have attached the generated reports, which is the core requirement we are addressing in this blog post. To fulfill this particular requirement, we suggest you use KingswaySoft's SMTP Task, shipped in our SSIS Productivity Pack, which offers those essential email features along with the email attachment capabilities. In order to work with the dynamic nature of the ETL process, you could leverage the parameterization capabilities included in the task component to send email attachments with different file names each time.
There are also cases where the number of attached files can even be different, which would create extra challenges in SSIS ETL parameterization. In such scenarios, just parameterizing the component using an SSIS expression might not be enough, and you may also need to dynamically create the SSIS expression (which is in JSON format) to handle the necessary process. This is where KingswaySoft's SSIS Productivity Pack comes in handy, with its full suite of various transformation and additional SSIS components that can easily help you achieve such a complex ETL process. In this blog post, we will be showing you a demonstration of how to accomplish the above scenarios with ease. The components that we will use for the use case include:
- Premium File System Source Component
- JSON Merge Component
- Premium Derived Column Component
- SMTP Task
Please note that the above components are all available for download as part of our SSIS Productivity Pack.
To implement this package, we will first use a Data Flow task to create a JSON document containing all the details about the email attachment before having the document written to an SSIS variable. The ETL package would contain an SMTP Task that is connected right after the first data flow task. While walking you through the design, we will show you how to parameterize the SMTP Task component to send multiple attachments in one email. The overall control flow design would look like something below:
Filtering and fetching the attachment files
As a first step, we need to retrieve the required files based on a particular set of conditions. For this example, let's say you would like to get all the files with a .txt extension from a specific folder. To do this, in the Premium File System Source component, set the Source Item Path as a folder location, and choose the Selected Level (Files only) as the Item Selection Mode. Then, provide FileExtention EQUALS .txt as the filtering conditions in the Filter Parameters. Please note that depending on whether you wish to read recursively, you can change the Item Selection Mode as required.
The next step would be to create a JSON structure. This is what we will provide as the SSIS expression for parameterizing the SMTP Task discussed below. In order to know more about how the parameterization of properties works in our components, please refer to the blog post below:
Based on the details above, we need an expression for the SMTP Task. For creating the JSON format, it is required to pass two fields FullPath and NameWithoutExtension to the original expression, as a repeated structure. The JSON for sending multiple files as Attachment in the SMTP Task component is as shown below (Syntax example):
[ { "name": "FileName1", "path": "FilePath1" }, { "name": "FileName1", "path": "FilePath2" }, { "name": "FileName1", "path": "FilePath3" } ]
Building the JSON structure
We will use a JSON Merge component to build the JSON format dynamically. Now that you have the JSON Syntax from the above, you can save it in a notepad (as a .JSON file). And then, open the JSON Merge component, and on the Document Designer page, click on Import and choose JSON (Local File).
On the Columns page, map the Name and Path with the values retrieved from the Premium File System Source component in the upstream data flow.
Once you have done that, on the Output & Error Handling page, you can set a name for the output column or leave it as default (MergedJson). This column will contain the merged JSON structure, as per the document designer, at runtime.
Writing JSON expression to a Variable
Now is the time to write the merged JSON value into a Variable with which you would parameterize the SMTP Task Component Attachments property. For this, you can use a Premium Derived column in the downstream data flow, and the function WriteValueToVariable(), using Replace function to add "\\" for the FullPath.
WriteValueToVariable(@[User::test1],Replace((DT_WSTR, 1000)[MergedJson],"\\","\\\\"))
Configuring the SMTP Task
In order to send an email with multiple Attachment files, you need to configure the SMTP Task, which is available in the Control Flow SSIS toolbox. After dragging and dropping the SMTP Task, connect the above data flow ("Save JSON structure in a variable") to it. Now, in the SMTP Task component, you could configure and set the connection manager, and provide the necessary information, such as SenderName, SenderAddress, Reply To Address, and Recipients on the General page. To learn more about the component and its configuration, please refer to our Online Help Manual below.
And on the content page, set the subject and the Body as required. In the Attachment page, as we mentioned before, you should parameterize it with the variable that would contain the merged JSON value. And to do that, you could click the "fx" button in the component, select the property Attachments from the list of properties, and then click on the ellipses next to the expression. On the new page, drag and drop the variable from the previous data flow and click on the Evaluate Expression to ensure that the value is set correctly. Please note that to pass SSIS validation, you would need to provide a valid JSON expression in the variable at design time. Then, this would be replaced by the actual value from the previous data flow at runtime.
Conclusion
This way, you can easily use KingswaySoft components to dynamically create and assign expressions to simplify such complex SSIS designs. The use case above is just an example, and the potential for using such logic proves they can be powerful tools in many integration scenarios. We hope this has helped.