Oct 122012
 

If you attended my SQL Saturday 165 presentation in Lincoln, NE, on October 6th, I have uploaded the materials from that presentation. If you did not attend, please download, review the PowerPoint slides, and contact me with any feedback/questions you may have.

You can view the materials either through the SQL Saturday 165 Website (http://www.sqlsaturday.com/165/eventhome.aspx) or through this Website via the Speaking tab at the top of the page.

Oct 122012
 

New in the 2012 version of SSIS is a feature that lets us keep a user-configurable amount of deployed project versions in the catalog. The problem, and the point of this blog post, is that you cannot export an older version of a project without reverting back to that version. This may not be possible in your environment and this post is an effort to provide you with another option.

First, this is what I am referring to:
SSIS 2012 - Catalog Project Versions

When you right-click on a project under “Integration Services Catalogs” in SSMS, you can choose to view its versions. From this window you can revert back to an older version while still retaining the latest version deployed.

If you want to export projects, you can do so by right-clicking on the project and selecting “Export…” In this window, just point it to an appropriate location. In my case, I saved it to Documents\SQLSaturday165. The resulting file you produce is an .ispac (Integration Services deployment package). A .ispac file is just a .zip. Rename it to use .zip instead of .ispac if you want to extract a single package out of a project.

If you want to export an older version of the project, you have two options – set the older version of the project to be the current version via the Project Versions window, or you can use an SSIS package to explicitly export the desired project version. I will demonstrate the SSIS package solution. The full sample and download links are at the end of the post.

Step One
Create a global, temporary table (##) on the SQL instance that has the SSISDB catalog.

CREATE TABLE ##projectBLOB (val VARBINARY(MAX));

Step Two
Open up the ExportProject.dtsx package and modify the OLE DB Source in the data flow to use the values from the following query:

SELECT ov.object_version_lsn AS project_version_lsn,
       ov.object_id AS project_id,
       *
  FROM internal.object_versions AS ov
  JOIN catalog.projects AS p
    ON ov.object_id = p.project_id
   AND p.name = N'SQLSaturday165_SSISDBInternals' -- replace with your project name
  JOIN catalog.folders AS f
    ON p.folder_id = f.folder_id
   AND f.name = N'SQLSaturday165_SSISDBInternals' -- replace with the folder name that contains your project
 ORDER BY ov.created_time DESC;
GO

Step Three
Edit the derived column component to set the path that you want to put the exported project in. Remember that in the derived column component you need to escape slashes. (“C:\Temp” should be written as “C:\\Temp”)

SSIS 2012 Catalog Derived Column Changes

Step Four
Change the SSISDB connection manager object to point to your SQL instance containing the SSISDB catalog.

SSIS 2012 Catalog SSISDB Connection Manager Changes

Step Five
Execute the package.

Step Six
Confirm your project was exported to the path entered in the derived column component. You should have a .ispac file with the name of your project in that location. From this point, you can double-click on it to launch the deployment wizard, or you can rename the file so that it has a .zip extention. If you choose to rename it to .zip, you will be able to individually pull out the packages.

SSIS 2012 Catalog Project ispac file

The resulting .ispac output file from the export process.

SSIS 2012 Catalog .zip file

Renaming the .ispac file to .zip allows you to inspect its contents.

Just a word of warning – THIS SSIS APPROACH IS NOT A SUPPORTED METHOD OF EXPORTING PROJECTS FROM THE CATALOG. YOU NEED TO BE A MEMBER OF SYSADMIN OR THE SSIS_ADMIN DATABASE ROLE. YOU TAKE SOLE RESPONSIBILITY FOR RUNNING THIS PACKAGE IN YOUR ENVIRONMENT. I AM NOT RESPONSIBLE FOR ANY DAMAGE THAT IS A RESULT OF RUNNING THIS PACKAGE. THIS IS AN UNSUPPORTED TECHNIQUE THAT USES INTERNAL, NON-DOCUMENTED PROCEDURES FOR EXPORTING PROJECTS.

With that said, I am merely posting this technique for educational purposes. The supported method for exporting an older version of a project is to revert to the older version and exporting it using the GUI in SSMS and then reverting back to the correct, current project.

Oct 082012
 

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:
SSIS Incorrect Configuration Error

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.

Sep 282012
 

Don’t miss out on your opportunity for a day of free SQL Server training! Come join us in Lincoln, NE, on Saturday, October 6th starting at 8:30 AM. The event will be held in Avery Hall on the University of Nebraska-Lincoln campus. There are 6 tracks with each having 5 sessions. That’s 30 FREE sessions to choose from, with industry experts and emerging leaders speaking about their passion! Don’t miss out!

I will be doing a presentation on the internals of the SSIS Catalog introduced in SQL Server 2012. For those that haven’t been much involved in the new catalog subsystem of SSIS, please plan on attending as I will be giving an overview and then going into some of the procedures, processes, and logging that are possible with this new framework. See you there!

http://www.sqlsaturday.com/165/eventhome.aspx

Sep 282012
 

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'
Jul 242012
 

In the new SSIS 2012 catalog’s project deployment model, by default if you execute a package with the Execute Package control flow task, or through the new API’s start_execution, the call will be made asynchronously.

In order to call the package synchronously, and therefore keep the caller waiting until the package finishes, you have to set a parameter value to true. This parameter, SYNCHRONIZED, is very, very sparsely documented on the ‘Net, but you can see it here assuming you’ve already executed some packages in your catalog:

SELECT DISTINCT parameter_name
  FROM SSISDB.catalog.execution_parameter_values  
 WHERE object_type = 50 -- 50 == execution parameter

To call it, simply call catalog.set_execution_parameter_values before calling catalog.start_execution and set the SYNCHRONIZED parameter to true (1). Like so:

EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
        @execution_id,  -- execution_id from catalog.create_execution
        @object_type=50, 
        @parameter_name=N'SYNCHRONIZED', 
        @parameter_value= 1; -- turn on synchronized execution

While I’m here, the other execution parameters are as follows:

  • DUMP_ON_EVENT
  • DUMP_EVENT_CODE
  • LOGGING_LEVEL
  • CALLER_INFO
  • DUMP_ON_ERROR

DUMP_ON_EVENT – Boolean – 0* | 1 – Used with DUMP_EVENT_CODE, this signals to the SSIS engine to create a dump when the specified DUMP_EVENT_CODEs are encountered.

DUMP_EVENT_CODE – String – “”* – Used with DUMP_ON_EVENT, a semi-colon delimited list of event codes that will generate dump files if encountered. If we wanted to capture login failures and unable to connect event codes, specify respectively: 0x80040E4D;0×80004005.

LOGGING_LEVEL – Integer – 0 | 1* | 2 | 3 – Sets the logging level for this execution. The values represent no logging, basic, performance, and verbose levels respectively.

CALLER_INFO – String – “”* – Used to pass-in additional information on the caller. Helpful when viewing the overview of an execution in the built-in reporting provided with the catalog.

DUMP_ON_ERROR – Boolean – 0* | 1 – If an error is encountered, create a dump.

* – default value

See below for a complete example of the above parameters.

DECLARE @execution_id BIGINT = 0;

-- Create a package execution
EXEC [SSISDB].[catalog].[create_execution] 
        @package_name=N'Package.dtsx', 
        @execution_id=@execution_id OUTPUT, 
        @folder_name=N'PhilsTest', 
        @project_name=N'Demo', 
        @use32bitruntime=False;

EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
        @execution_id,  
        @object_type=50, 
        @parameter_name=N'SYNCHRONIZED', 
        @parameter_value=1; -- true

-- Set our package parameters
EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
        @execution_id,  
        @object_type=50, 
        @parameter_name=N'DUMP_ON_EVENT', 
        @parameter_value=1; -- true

EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
        @execution_id,  
        @object_type=50, 
        @parameter_name=N'DUMP_EVENT_CODE', 
        @parameter_value=N'0x80040E4D;0x80004005';

EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
        @execution_id,  
        @object_type=50, 
        @parameter_name=N'LOGGING_LEVEL', 
        @parameter_value= 1; -- Basic

EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
        @execution_id,  
        @object_type=50, 
        @parameter_name=N'CALLER_INFO', 
        @parameter_value= N'Phil''s Demo';

EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
        @execution_id,  
        @object_type=50, 
        @parameter_name=N'DUMP_ON_ERROR', 
        @parameter_value=1; -- true

-- Start the package
EXEC [SSISDB].[catalog].[start_execution] 
        @execution_id;

To confirm that they were accepted, here is a screenshot from the execution overview report:
SSIS Catalog Execution Parameters

Jul 172012
 

On Saturday, October 6th, 2012, please plan on attending SQL Saturday #165 in Lincoln, NE, at the University of Nebraska-Lincoln’s main campus. This SQL Saturday is shaping up to be a great event with several high-caliber speakers from around the Omaha/Lincoln area, as well as some from surrounding states. The event is hosted by the Lincoln SQL Server User Group.

These events help build the SQL community by providing free* SQL Server education (not all topics are technical!) and by facilitating great networking opportunities with fellow industry peers. I will be there. Will you join me?

* – A $10 fee is requested if you’d like the event to provide lunch for you. You are otherwise welcome to skip that benefit and make plans for your own lunch.