Nirav's Diary

SSIS Package to generate MailProfile script

By nirav on April 1st, 2013

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