"Hey, can you remove John Smtih's email from the ETL process notification and put Julie Jones?" My coworker asked as he caught me in the hall.
While developing the dozens of ETL processes for our COVID response, I had added a Send Mail feature for both success and failure, so the team knew, immediately if the jobs succeeded or failed.
In the height of pandemic response, tidiness wasn't our top priority. Come to think of it, neither were family time or sleep. Our operational tempo was such that I had hard coded the email addresses. After all, I was changing the SSIS packages several times a month, at least , as the fluid requirements changed.
Now, as the pandemic's intensity waned, I decided that NOW was the the time to do it right.
As any programmer knows, a professional doesn't hard code items that change frequently. The way we use SSIS, if something changes, we're usually remapping fields, changing logic, or changing database connections. So we've not had much call for creating parameterized variables in SSIS.
Here's How I pulled it off.
- First, I created variables to hold the parameters.
- Right click in a blank portion of the control flow window, and select variables
- I created variables to hold the parameters I wanted to pass. In my example, I am passing the To Email Address, Subject and body
- I select the data type as String.
- Note: I provided default values, so that when the package is deployed, I don't have to mess with it.
- Second, I configured my Send Mail Task to use the variables.
- First I edit the Send Mail Task
- I go to Expressions
- I add the three variables to the three properties I want to configure
- then I save and build the SSIS and deploy
Critical Note: Copy your Variable Names. You'll need them later. SSMS does not populate them for you.
- Next, create your SQL Agent Job in SSMS
- When defining the Step correlated to your SSIS package, go to "Set Values" tab
- Under Properties, put the variable name in the Property Path column, and the value you wish the variable to be set to in the Value column.
- Note, formatting of the variable name is as follows
- \package.Variables[Variable Name].Value
And you're done!
