A new blog

An ex colleague and close mate has started a new blog which I have recently added to my Google reader. You can find it here. His opinions and humour definitely require a read!

Stored Procedure - Parameter sniffing

I spent a fair amount of time yesterday on a stored procedure which was performing very badly. Once I had done all the usual checks I decided to run the SQL that was contained in the sp and was very confused to see that it was a lot quicker. The sp was taking over 3 minutes to execute while the SQL query was taking 2 seconds.

I ended up finding out why from this link and this one.

Lets hope this parameter sniffing issue doesn't catch you out for as long as it did me.

Happy deving!

The RWC Final

It is a huge weekend for South African rugby. Lets hope the guys can hold it together and take the cup home. Go Bokke!

SSIS : Running a package through a SQL Agent Job

On the project I am currently working on, I have set up a few of my SSIS packages to execute under a global executor job executed by the SQL Agent. The other evening at closing time a colleague of mine needed to execute this job. He used remote desktop to get onto the relevant server and manually started the job. He then logged off from the server, packed up and went home, happy in the knowledge that the job would be run by the time we returned in the morning. However in the morning we were greeted with a failed job and the following message "The job was stopped prior to completion by (Unknown) "

After investigating further I found the following explanation :

http://support.microsoft.com/kb/922527

A bit of an interesting one. Definitely worth knowing if you are like me and run batch jobs at night and hope to have them completed in the morning.

SSIS : Package Configurations

There are many advantages to setting up configurations for your SSIS packages. If you have configuration values set up throughout your packages you can avoid unnecessary retesting and deployment processes later on. It also makes the process of redeploying to multiple environments (Dev, UAT, SysTest, and Prod) a lot easier.

On a recent project my colleagues and I decided that it would be very useful to implement package configurations in our SSIS packages. After a little bit of investigation we realised that the out-of-the-box package configuration although useful would not provide us with what we required.

Our requirements were as follows:

• Multiple filtering options - Because a package can be used in multiple systems and multiple environments (Development, UAT, Production)
• System and Package variable - Some variables (e.g. warehouse connection string) maybe system wide variables whereas some may be unique for a specific package.
• Encryption of Sensitive Information- Some of the variables may contain sensitive information that we wanted to have encrypted in the database.
• Config Audit Log - An audit log of what configuration values were during the time the package executed.
• Config Audit Trail - An audit trail of who has changed the config values
• Simple implementation - an easy way to implement the config process throughout all existing and future packages.

Database
I am sure most SSIS implementation use an Audit database of some kind to capture package and task execution logs. (See a future blog on SSIS logging) This is a good place to store your configuration information as it goes hand-in-hand with your logging information.

As we are storing this information in a database which can be moved between servers with varying users it is advisable to have its connection string being set up using the out of the box SSIS configuration functionality.

The database contains configuration tables i.e. PackageConfig, PackageAudit, PackageConfigLog. The PackageConfig table contains information uniquely defining each package through it’s package GUID and each of the package variables, variable types and variable values along with a flag whether the value should be encrypted or not. You should also include multiple columns for filtering your configuration values by i.e. System, Environment and Warehouse Process etc. The PackageAudit table contains all the necessary columns from the PackageConfig table that need to be audited as well a column for the user who made the change and the time of the change. This table has a trigger that will add a new row to the audit table whenever the main table is added to or changed. The PackageConfigLog will contain the necessary columns from the PackageConfig table that will allow the administrator to see which configuration values were used during each execution. Trust me, without this table you can be testing and get unusual results, take many hours hunting down what you might have done wrong and in the end find out that one of you colleagues change a config value that was vital to your test. If you had checked this table first, life could have been a lot less painful.

SSIS
Inside you package you will need a task to overwrite the variable values as defined in the package. You may want to write a custom task for this or just use a script task. As this is a task that will be used in all you packages I would suggest using the custom task options as changes to the script once it has been implemented in 100s of packages can be a real nightmare.

I have included a simplified version of the code below

' open connection
oSQLConn = New SqlConnection(strConnectionString) : oSQLConn.Open()
oSQLComm = New SqlCommand("uspGetConfigInfo", oSQLConn)
oSQLComm.CommandType = CommandType.StoredProcedure 

' Add parameters to SQL command
Call ParametersGetConfig() 

' Execute and return dataset
oSQLReader = oSQLComm.ExecuteReader() 

' Loop through reader
While oSQLReader.Read
If Dts.VariableDispenser.Contains(oSQLReader.GetString(0).ToString) Then
' Lock variable for write
Dts.VariableDispenser.LockOneForWrite(oSQLReader.GetString(0).ToString, oVariables) 

' Check variable is the correct type
If oVariables.Item(oSQLReader.GetString(0).ToString).DataType.ToString = oSQLReader.GetString(2) Then
oVariables.Item(oSQLReader.GetString(0).ToString).Value = Convert.ChangeType(oSQLReader.GetValue(1), _
oVariables.Item(oSQLReader.GetString(0).ToString).DataType) 

' Report information
Dts.Events.FireInformation(0, strComponent, "The variable - " & oSQLReader.GetString(0).ToString & " has been updated.", _
String.Empty, 0, True) 

Else ' Report incorrect data type
Dts.Events.FireWarning(0, strComponent, "The variable - " & oSQLReader.GetString(0).ToString & " has a different data type then " & _
"that defined in the config database.", String.Empty, 0)
End If
Else ' Report missing variable
Dts.Events.FireWarning(0, strComponent, "The variable - " & oSQLReader.GetString(0).ToString & " is missing.", String.Empty, 0)
End If
End While 

' Close SQL Data Reader
oSQLReader.Close() 

A stored procedure (uspGetConfigInfo) will get the variable information from the table discussed earlier and find the relevant variables, verify that the variable type are correct and then replace the value in the package. The change will be reported to the package through the component event namespace and can be captured by your chosen logging mechanism in the OnInformation event handler. The config log information could be implemented using a similar process in which the valid variables (with the correct data type) are written away to the PackageConfigLog table.

Encryption:
Using SQL Server encryption you can encrypt variable values in your PackageConfig table and decrypt them in your GetConfigValues stored procedure. Ensure that these values are not written to the package config log as that would obviously ruin the whole purpose of the encryption process.

Hall shines in Barbados

What an amazing spell of bowling from Andrew Hall Yesterday! The most underrated player in South African Cricket showing his stuff. Good on you mate, good on you!

It has been a while!

Wow, it has been a long time since I lasted blogged. Apologies for that but I have been on holiday in Cape town. While sitting on the beach getting a healthy dose of sun, sea and sand the geek in me was thinking what my next few blogs should involve.

After a second or two of thought I decided that the folowing would be nice to share :

1) SSIS : Package Configurations
2) SSIS : Package and Task logging
3) SSIS : Creating and processing Analysis Services partitions
4) SSMS : A sample SSMS addin

So while I get hard to work on these I hope you will occassionally check in to see my progress.

SSMS: T-SQL Intellisense Addins (Feedback)

After trying SQLAssist for a short while now, I have decided to removed it from my machine. I found it nice to work with but a lot of the time it would hang SSMS for few seconds. This afternoon while writing a query I hit Ctrl-F and it froze SSMS. Very annoying when you are in the middle of some hardcore development.

SQL Prompt is now priced at $195 which to me seems a little excessive. Oh well will have to carry on using my work around. I will place it here soon when I have created an installation package for it. Don't panic, my slap together version will definitely be free.

SSIS : Writing to the event log

I recently had a discussion with a collegue of mine on how to write information to the event log. If you have tried this before you will see that the DTS.Event.FireError will write an error to the eventlog but the other events (Onwarning, OnInformation) won't. This can be a bit of a headache if you have any application that uses the Eventlog for notifications. To get over this hurdle I would suggest you add a script component to the event you are trying to capture and add the following code to it.

 

Imports System, System.Diagnostics, System.Data, System.Math, Microsoft.SqlServer.Dts.Runtime

' Must Include System.Diagnostics

Public Class ScriptMain

Public Sub Main()

     Try

         ' Write whatever message you want. You may need to investigate what eventID you will use

         EventLog.WriteEntry("Your Source", "Warning the database has gone off line", EventLogEntryType.Warning, 1001, 0)

        ' Report Error

        Dts.TaskResult = Dts.Results.Success

        Catch ex As DtsException

              Dts.Events.FireError(CInt(ex.ErrorCode), Dts.Variables("System::TaskName").Value.ToString, ex.Message.ToString, String.Empty, 0)

        End Try

End Sub

End Class

 

Note: You will need to add the the System.Diagnostics namespace to make this work.

SSMS: T-SQL Intellisense Addins

I have had my eye on a few of the T-SQL Intellisense applications that have shown up since microsoft excluded intellisense from the T-SQL part of the Texteditor in SSMS after beta 1 of Sql Server 2005. I even went so far as to having a bash at creating one of my own (I will include my own attempts in a future blog - for anyone who might be interested). I have had a look at SQLPrompt, albeit version 2 (I hear a version 3 is due for release soon) and I found it too annoying to work with on an everyday basis. The biggest problem was that it kept on trying to authenticate which drove me a little crazy. Yesterday I decided to download the beta of SQLAssist (SSMS) and have been suitably impressed so far. One of my colleagues thinks it is "The Nuts" but I am not prone to handing out such glowing compliments until I have had a better look at it. So I will be working with it for the next week or so and will report back on what I think and maybe include some of the features I have found to be very useful.