none
Storing and retrieving email addresses SQL 2016 SSISDB RRS feed

  • Question

  • Greetings I am converting a number of file based 2008 packages from BIDS to SQL 2016 being stored in SSISDB using SSISDT. I am new to using SSISDB and have started using package and environment variables to replace the use of configuration files and it has worked well so far. We have a lot of reports, and are using variables set in a config file to store the email addresses for sending reports and error notification. When I have a change to an email or recipient I can use a file based edit to make mass changes. Works fine but I am storing the same email multiple times as we don't use email groups for this as we have a number of external email recipients among other reasons. I am wondering what others are using to manage the email addresses for reporting/error notifications using SSISDB since config files are gone?  The situation I am not sure how to handle is when we have someone change jobs here, I have to change Jane.doe@email.com to  John.doe@email.com to direct a number of reports to someone,  and I want to do this without redeployment, hopefully as easily as I have been able to with the config file edit.

    I would like to try to keep the email addresses out of the database. Storing them in variables or parameters store the emails in the database using ssisdb and I am trying to keep any PII out of the database. 

    Thanks

    • Edited by Abeljdang Tuesday, November 26, 2019 2:44 PM further clarify
    Friday, November 15, 2019 9:03 PM

All replies

  • Hi Abeljdang,

    Starting from SSIS 2012 onwards, SSIS supports Project level parameters. All packages in a project can use them for any settings including e-mails.

    You can change their values in SSMS manually or programmatically: catalog.set_object_parameter_value (SSISDB Database)



    Friday, November 15, 2019 9:08 PM
  • Yes I am familiar with them. Was wondering what other options or if that is it. I suppose I can script them all of and store them in a file, mass edit there  and recreate them but again wondered what others are doing as it is not an uncommon task. Thanks for the reply.
    Friday, November 15, 2019 9:11 PM
  • Hi Abeljdang,

    We can use dtexec Utility to execute the package and change the variables or parameters' value.

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, November 18, 2019 5:37 AM
  • Planning to store the packages in SSISDB and run most via sql agent. I believe you are using the file based approach so that won't work. I will look to where these are stored in SSISDB and see if I can come up with an acceptable/easy way to do this.
    Thursday, November 21, 2019 2:07 PM
  • Hi Abeljdang,

    May I know if you have anything to update?

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, November 22, 2019 7:48 AM
  • No, I was looking for other ideas but I am not sure there are really other options. I am new to using SSISDB, not new to SSIS so was wanting other opinions. 
    Friday, November 22, 2019 1:25 PM
  • Hi Abeljdang,

    Please refer to the following links:

      Simple Steps to Creating SSIS Package Configuration File

      Setup Environment Variables in SQL Server Integration Services

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, November 25, 2019 1:36 AM
  • familiar with both of these, my original question indicated we are using config files currently to store the emails. thanks
    Tuesday, November 26, 2019 2:42 PM
  • Hi Abeljdang,

    Please remember to click "Mark as Answer" the responses that resolved your issue.

    This can be beneficial to other community members reading this thread.

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, November 27, 2019 6:39 AM
  • Most certainly will. 
    Wednesday, November 27, 2019 1:33 PM
  • Hi,

    May I know if you run SSIS packages successfully by using config files currently to store the emails?

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, November 28, 2019 9:24 AM
  • Yes the current environment works fine, as I tried to explain initially I am converting the 2008 file based packages to SSISDB. The question is options on storing email addresses and I am trying to find an option that won't store the emails in the database. Currently they are stored in the config file for the packages on the file system along with the other configuration items for the package.
    Friday, November 29, 2019 2:13 PM
  • Hi Abeljdang,

    Please refer to Implementing Foreach Looping Logic in SSIS.

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, December 2, 2019 3:25 AM