Book – ‘What’s New in SQL Server 2012’ Published

 SQL Server  Comments Off on Book – ‘What’s New in SQL Server 2012’ Published
Jan 072013

Waaaaay back in 2012, I had the great pleasure of being a technical reviewer of another book, What’s New in SQL Server 2012, authored by Rachel Clements (@RachelClements) and Jon Reade (@JonReade) and published by Packt Publishing.

Together, these two authors put together a very nice desk reference of the new features introduced in SQL Server 2012, with topics ranging from an overview of the various SQL Server editions to new functions, such as EOMONTH (yep, there’s a new “End of Month” datetime function).

I highly recommend this book for any SQL Server user who is new to SQL Server 2012.

Obtain your copy here:

Jan 072013

If you are on SQL Server 2012 SP1 (11.0.3000) and are experiencing higher than normal CPU loads on your machine, please take a look at the Windows Application log for the following:

  • Warning / Event ID 1004 / MsiInstaller / Detection of product ‘{A7037EB2-F953-4B12-B843-195F4D988DA1}’, feature ‘SQL_Tools_ANS’, component ‘{0CECE655-2A0F-4593-AF4B-EFC31D622982}’ failed. The resource ” does not exist.
  • Warning / Event ID 1001 / MsiInstaller / Detection of product ‘{A7037EB2-F953-4B12-B843-195F4D988DA1}’, feature ‘SQL_Tools_ANS’ failed during request for component ‘{6E985C15-8B6D-413D-B456-4F624D9C11C2}’
  • Information / Event ID 1040 / MsiInstaller / Beginning a Windows Installer transaction: {A7037EB2-F953-4B12-B843-195F4D988DA1}. Client Process Id: 16708.
  • Information / Event ID 11724 / MsiInstaller / Product: SQL Server 2012 Management Studio — Install started.
  • Information / Event ID 10000 / RestartManager / Starting session 0 – 2013-01-07T12:40:46.549958800Z.
  • Information / Event ID 11728 / MsiInstaller / Product: SQL Server 2012 Management Studio — Configuration completed successfully.
  • Information / Event ID 1035 / MsiInstaller / Windows Installer reconfigured the product. Product Name: SQL Server 2012 Management Studio. Product Version: 11.1.3000.0. Product Language: 1033. Manufacturer: Microsoft Corporation. Reconfiguration success or error status: 0.
  • Information / Event ID 1042 / MsiInstaller / Ending a Windows Installer transaction: {A7037EB2-F953-4B12-B843-195F4D988DA1}. Client Process Id: 16708.
  • Information / Event ID 10001 / RestartManager / Ending session 0 started 2013-01-07T12:40:46.549958800Z.

If you have the above symptoms, please look at this non-security hotfix KB download: Do not install if you have already applied any post SP1 hotfixes, such as SP1 CU1. You can also view the SQL Server Connect bug here:

Installing that fix will bring your SQL version to 11.0.3128, fix the problem with the MSI installer, and it should drop your CPU consumption a bit as well.

Omaha SQL Server User Group Meeting – 5 Years!

 SQL Server User Group  Comments Off on Omaha SQL Server User Group Meeting – 5 Years!
Dec 032012

While I have not been active in the Omaha SQL Server User Group (SSUG) for all of its five years, I am proud to be part of an ever-growing SQL Community in the Omaha area. Please be sure to join me on Wednesday, December 5th, at Farm Credit Services America for our last 2012 SSUG meeting.

To celebrate the five year anniversary of the Omaha SSUG, the topic of the evening will be … YOURS! John & Naras are bringing back the “Choose Your Own Adventure” agenda, where you get to bring your own questions, problems, fodder, tips, and best practices to be discussed by all attendees. If you haven’t been to one of these sessions before, or are feeling as if you just can’t quite find the answer to your questions online, be sure to take part in this meeting. They are extremely informational and beneficial to the SQL Community.

I will be in attendance, and if there are any questions that need to be raised to Microsoft, I will do my best to ensure the appropriate people see it.

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 ( 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.


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 = N'SQLSaturday165_SSISDBInternals' -- replace with your project name
  JOIN catalog.folders AS f
    ON p.folder_id = f.folder_id
   AND = N'SQLSaturday165_SSISDBInternals' -- replace with the folder name that contains your project
 ORDER BY ov.created_time DESC;

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.


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.

Speaking at SQL Saturday #165, Lincoln, NE

 Uncategorized  Comments Off on Speaking at SQL Saturday #165, Lincoln, NE
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!