SSIS Package to generate MailProfile script.
There is no provision to generate script for mail profile in sql server. but i have one script that can be used to create mail profile, mail account and map them together.
I am using this script whenever i needed it, letter i have an idea to start scripting automatically of mail profile and i have created one package that genrates all three scripts and store it in one sql file. this is very helpfull for disaster recovery planning for script backup.
Step1 : To create package you need one Data Flow Task, One Flat File Connection, One OLEDB Connection, and one variable
Please see in figure.
Step2 : Drag one OLE DB Source and one Flat File Destination. Right click on Data Flow Task –> Edit
Step3 : Right click on OLEDB Source Editor — > Edit
Select your local machine connection.
Select SQL Command in Data Access mode.
Paste Below Code.
Select '--Create Account' AS CMD Union All SELECT 'EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = ''' + m.name + ''', @description = ''' + m.description + ''', @email_address = ''' + m.email_address + ''', @replyto_address = ''' + m.replyto_address + ''', @display_name = ''' + m.display_name + ''', @mailserver_name = ''' + s.servername + ''', @mailserver_type = ''' + s.servertype + ''', @port = ''' + cast(s.port as nvarchar) + ''', @username = ''' + isnull(c.credential_identity,0) + ''', @password = ''x'', @use_default_credentials = 0, @enable_ssl = 0' AS CMD From msdb.dbo.sysmail_account m LEFT OUTER JOIN msdb.dbo.sysmail_server s ON m.account_id = s.account_id LEFT OUTER JOIN master.sys.credentials c ON s.credential_id = c.credential_id Union All Select '--Create Profile' AS CMD Union All SELECT ' EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = ''' + name + ''', @description = ''' + isnull(description,'NULL')+ ''' ' AS CMD from msdb.dbo.sysmail_profile Union All Select '--Create Link For Profile to Account' AS CMD Union All Select ' EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = '''+ a.name +''', @account_name = ''' + b.name + ''', @sequence_number = 1 ' as CMD from msdb.dbo.sysmail_profile as a join msdb.dbo.sysmail_account as b on a.name = b.name
Step4 : Click on Columns left side on OLE DB Source editor, configure column and click ok.
Step5 : Right Click on Flat File Destination and select “Primary_Location” to flat file connection manager.
Click on Mappings to map variable.
Step6 : Now to make folder dynamic – select Primary_location from connection manager and hit F4.
Click on Expression button.
Copy below code to Expression.
@[User::FolderName] + "<a href="file://mailprofile/">\\MailProfile</a>_" + (DT_WSTR,4)YEAR(GETDATE()) + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2) + ".sql"
Click OK and execute the package it will creates script to “D:\ScriptBackup\MailProfile\MailProfile_YYYYMMDD.Sql” this file contains three different bunch of script. Fist to create Mail Account Second to create Mail Profile. Third to map Mail Account to Mail Profile.
Regards,
Nirav Gajjar