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