SSIS 2012 Deployment Frustrations?

I went to deploy a large SSIS project recently and received this error from the Deployment Wizard:

Failed to deploy project. For more information, query the operation_messages view for the operation identifier ’219′. (Microsoft SQL Server, Error: 27203)

Hoping that I would find out more I queried the catalog.operation_messages table in the SSISDB database.

The message column reads as follows:

Failed to deploy the project. Fix the problems and try again later.:Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
The statement has been terminated.

As the person deploying the project, I can’t “help” the Deployment Wizard with tweaking any queries, but I could see what it was doing using SQL Profiler.  The query that caught my attention is the following:

exec [internal].[sync_parameter_versions] @project_id=2,@object_version_lsn=10

Notice the number of reads(1.7M) and the duration(30 seconds).  I acquired the query plan for this stored procedure and found that it would run much faster if the following two indexes are applied to the SSISDB database.

USE [SSISDB]

GO

CREATE NONCLUSTERED INDEX [ix1_internal_object_parameters_inc] ON [internal].[object_parameters]

(

        [project_id] ASC ,

        [project_version_lsn] ASC

)

INCLUDE (      [parameter_id],

        [object_type],

        [object_name],

        [parameter_name],

        [parameter_data_type],

        [required],

        [sensitive]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF , ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [ix2_internal_object_parameters_inc] ON [internal].[object_parameters]

(

        [project_id] ASC ,

        [project_version_lsn] ASC ,

        [object_type] ASC ,

        [object_name] ASC ,

        [parameter_data_type] ASC ,

        [required] ASC ,

        [sensitive] ASC

)

INCLUDE (      [parameter_name],

        [default_value],

        [sensitive_default_value],

        [value_type],

        [value_set],

        [referenced_variable_name]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF , ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

After these indexes were applied the deployment wizard had no more issues.

Webpage cache warmer with Powershell

Have you ever needed to cache web pages after a web server reboot?  Here is a great little PowerShell script that can be added to the start-up script of a server.  This is also great for getting SharePoint pages cached after a reboot.

##############################################################

##############################################################

##############################################################

#

#     Open a list of webpages to warm the web server cache

#

##############################################################

# Created Date: 8/6/2012

# Created By: Jim Bennett

##############################################################

##############################################################

##############################################################

#Variables

#Enter a comma separated list of webpages to hit

$WebpageList = "http://www.google.com","http://www.capstonebi.com"

$WebpageList | ForEach-Object {

      Write-Host "Opening page: $_" -ForegroundColor Yellow -nonewline

      $temp = [System.Math]::Round((Measure-Command {(new-object net.webclient).DownloadString($_)}).totalmilliseconds)

      Write-Host " (Opened in $temp milliseconds)" -ForegroundColor Yellow

}

MERGE Statement MATCHED clause headache relief

Have you ever been using a MERGE statement and when you get the MATCHED clause you think to yourself, “This is easy, I’ll just script out an UPDATE statement and do a quick replace”? Only when you get around to it doing the quick replace it isn’t so quick.

A friend of mine(SB) once said “I work hard at working smart”. It is a great motto to work by especially when working in a technical role. So in the spirit of this motto, I decided to find a smarter way to do a quick replace for a MERGE statement’s MATCHED clause.

So here is our scenario: I’ve got a table with about 200 columns in it. I need to update about 180 of them from my staging table to my dimension table. When I script out an update statement for my table and put the column list in my MERGE statement I get something like this:

WHEN MATCHED THEN
UPDATE SET
[RegistrationDate] = <RegistrationDate, int,>
,[RegistrationDateKey] = <RegistrationDateKey, int,>
,[CreationDate] = <CreationDate, datetime,>
,[CreationDateKey] = <CreationDateKey, int,>
,[FirstName] = <FirstName, varchar(100),>
,[MiddleName] = <MiddleName, varchar(100),>
,[LastName] = <LastName, varchar(100),>
,[AddressLine1] = <AddressLine1, varchar(100),>
,[AddressLine2] = <AddressLine2, varchar(100),>
,[City] = <City, varchar(100),>
,[State] = <State, varchar(5),>
,[PostalCode] = <PostalCode, varchar(100),>
,[EMail] = <EMail, varchar(200),>
,[PhoneNumber] = <PhoneNumber, varchar(100),>
,[CustomDateTimeValueLastChangedDateTime] =
<CustomDateTimeValueLastChangedDateTime, datetime,>

,[CustomDateTimeName1] = <CustomDateTimeName1, varchar(100),>
,[CustomDateTimeValue1] = <CustomDateTimeValue1, datetime,>
,[CustomDateTimeName2] = <CustomDateTimeName2, varchar(100),>
,[CustomDateTimeValue2] = <CustomDateTimeValue2, datetime,>
,[CustomDateTimeName3] = <CustomDateTimeName3, varchar(100),>
,[CustomDateTimeValue3] = <CustomDateTimeValue3, datetime,>
,[CustomDateTimeName4] = <CustomDateTimeName4, varchar(100),>
,[CustomDateTimeValue4] = <CustomDateTimeValue4, datetime,>
,[CustomDateTimeName5] = <CustomDateTimeName5, varchar(100),>
......

Only I need it to look like this:

WHEN MATCHED THEN
UPDATE SET
[RegistrationDate] = Source.[RegistrationDate]
,[RegistrationDateKey] = Source.[RegistrationDateKey]
,[CreationDate] = Source.[CreationDate]
,[CreationDateKey] = Source.[CreationDateKey]
,[FirstName] = Source.[FirstName]
,[MiddleName] = Source.[MiddleName]
,[LastName] = Source.[LastName]
,[AddressLine1] = Source.[AddressLine1]
,[AddressLine2] = Source.[AddressLine2]
,[City] = Source.[City]
,[State] = Source.[State]
,[PostalCode] = Source.[PostalCode]
,[EMail] = Source.[EMail]
,[PhoneNumber] = Source.[PhoneNumber]
,[CustomDateTimeValueLastChangedDateTime] = Source.[CustomDateTimeValueLastChangedDateTime]
,[CustomDateTimeName1] = Source.[CustomDateTimeName1]
,[CustomDateTimeValue1] = Source.[CustomDateTimeValue1]
,[CustomDateTimeName2] = Source.[CustomDateTimeName2]
,[CustomDateTimeValue2] = Source.[CustomDateTimeValue2]
,[CustomDateTimeName3] = Source.[CustomDateTimeName3]
,[CustomDateTimeValue3] = Source.[CustomDateTimeValue3]
,[CustomDateTimeName4] = Source.[CustomDateTimeName4]
,[CustomDateTimeValue4] = Source.[CustomDateTimeValue4]
,[CustomDateTimeName5] = Source.[CustomDateTimeName5]

.....

I’ve been using regular expressions for a long time, but I recently discovered a little known feature called replacement expressions. Basically, it allows the replace to use a portion of what it found and put it back. This is great news!
So here is what I came up with:
In my Find What box I use the following: {\[[:a]+\]} = \<[:a, ()]+\>
In my Replace with box I use the following: \1 = Source.\1

I run my replace all and I instantly have the columns with the syntax I need. I hope this comes in handy for you; I know it sure came in handy for me.