SSIS Talk

When using package configurations in SSIS (pre-SQL 2012 or SQL 2012 with package deployment models), you may run into the following errors:
The configuration entry, "????????????????????????????", has an incorrect format because it does not begin with the package delimiter. Prepend "\package" to the package path.

The package path referenced an object that cannot be found: "????????????????????????????". This occurs when an attempt is made to resolve a package path to an object that cannot be found.

Since the characters will inevitably be munged when displaying this page, here is an image of the actual text:

If you encounter these errors and you are using SQL Server-based configurations (using the dbo.[SSIS Configurations] table), double-check that the data types of all columns in the config table are NVARCHAR, not VARCHAR. If they are not NVARCHAR, set them back accordingly. Occasionally you may need to modify this table to extend the max length of some of the columns and when that happens someone may inadvertently change the data type to VARCHAR.

Fellow MVP Jonathan Kehayias has a post on scripting out the Database Mail configuration with PowerShell and SMO. While this solution looks perfect, there is a bug in the Mail.Script() output, not mentioned in Jonathan’s post except for one reader’s comment, that does not script out the SMTP server name and port number. (Note, that is not a bug in Jonathan’s code; it is a bug in how Microsoft coded the API.)

This post is to give an example of how I worked around that problem so that I could script out a SQL instance’s mail config and apply it on a new instance as needed. Quite simply, I added a ForEach loop over each account in the Mail object and printed its configured mail server name and port. I just plugged those in as parameters to msdb.dbo.sysmail_update_account_sp. I also added a few more enhancements to Jonathan’s script – resizing the width of the output and adding appropriate sp_configure statements to get things rolling on the new instance. Below is the code for SQL 2005+:

#Original code from Jonathan Kehayias, http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/08/23/scripting-database-mail-configuration-with-powershell-and-smo.aspx

[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo");

$Host.UI.RawUI.BufferSize = New-Object Management.Automation.Host.Size (500, 25);

"sp_configure 'show advanced options',1
RECONFIGURE
GO
sp_configure 'Database Mail XPs',1
RECONFIGURE 
GO
"

#Set the server to script from 
$ServerName = "SQLServerMachine";

#Get a server object which corresponds to the default instance 
$srv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server $ServerName

#Script Database Mail configuration from the server 
$srv.Mail.Script();

ForEach ($account in $srv.Mail.Accounts) {
	$AccountName = $account.Name
	$MailServerName = $account.MailServers[0].Name;
	$MailServerPort = $account.MailServers[0].Port;
	"EXEC msdb.dbo.sysmail_update_account_sp @account_name = N'$AccountName',";
		"	@mailserver_name = N'$MailServerName',";
		"	@port = N'$MailServerPort'";
}

This will give you output such as the following:

sp_configure 'show advanced options',1
RECONFIGURE
GO
sp_configure 'Database Mail XPs',1
RECONFIGURE 
GO

EXEC msdb.dbo.sysmail_configure_sp @parameter_name=N'AccountRetryAttempts', @parameter_value=N'1', @description=N'Number of retry attempts for a mail server'
EXEC msdb.dbo.sysmail_configure_sp @parameter_name=N'AccountRetryDelay', @parameter_value=N'60', @description=N'Delay between each retry attempt to mail server'
EXEC msdb.dbo.sysmail_configure_sp @parameter_name=N'DatabaseMailExeMinimumLifeTime', @parameter_value=N'600', @description=N'Minimum process lifetime in seconds'
EXEC msdb.dbo.sysmail_configure_sp @parameter_name=N'DefaultAttachmentEncoding', @parameter_value=N'MIME', @description=N'Default attachment encoding'
EXEC msdb.dbo.sysmail_configure_sp @parameter_name=N'LoggingLevel', @parameter_value=N'2', @description=N'Database Mail logging level: normal - 1, extended - 2 (default), verbose - 3'
EXEC msdb.dbo.sysmail_configure_sp @parameter_name=N'MaxFileSize', @parameter_value=N'1000000', @description=N'Default maximum file size'
EXEC msdb.dbo.sysmail_configure_sp @parameter_name=N'ProhibitedExtensions', @parameter_value=N'exe,dll,vbs,js', @description=N'Extensions not allowed in outgoing mails'
EXEC msdb.dbo.sysmail_add_account_sp @account_name=N'DBMailAccount', 
		@email_address=N'sqlops@ssistalk.com', 
		@display_name=N'SQL Operations'
EXEC msdb.dbo.sysmail_add_profile_sp @profile_name=N'DataManagement'
EXEC msdb.dbo.sysmail_add_profileaccount_sp @profile_name=N'DataManagement', @account_name=N'DBMailAccount', @sequence_number=1
EXEC msdb.dbo.sysmail_update_account_sp @account_name = N'DBMailAccount',
	@mailserver_name = N'mail.ssistalk.com',
	@port = N'25'