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.

SSIS : Creating an error output from a script component

I recently had the need to create an error output from a script component and thought I would create a blog to explain how easily it can be done.

I have created a simple data flow with a OLEDB source that uses SalesAmount and DiscountAmount from the AdventureWorksDW FactResellerSales Table as source inputs. I choice these columns as Discount has values with zeros in it and I wanted to force a divide by zero error. The data flows into the Script component, it then divides the two columns and redirects the errors to the Error Rowcount destination. The non error rows are directed to the valid rows Rowcount destination.



When you first create a script component as a transformation, it defaults to having a single input and single output. For this example I have added an extra script output (ErrorRows) and renamed the existing script output to ValidRows. I added an Outputcolumn to the ErrorRows script output called ErrorDescription. I then changed the SynchronousInput property of both script outputs to the script input's ID property. I also changed the two script output ExclusionGroup properties to be 1.



The rest is made easy with the following code:

Imports System, System.Data, System.Math, Microsoft.SqlServer.Dts.Pipeline.Wrapper, Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

    Inherits UserComponent

    Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)

        Try

            Row.SalesWithoutDiscount = CDec(Row.SalesAmount) / CDec(Row.DiscountAmount)

            ' Redirect rows toward valid row output

            Row.DirectRowToValidRows()

        Catch ex As Exception

            ' Capture error description

            Row.ErrorDescription = ex.Message.ToString

            ' Redirect rows toward error row output

            Row.DirectRowToErrorRows()

        End Try

    End Sub

End Class

The Data Viewers show the progress when the package is executed. The errors have been redirected to the Error Rows Row Count Destination. The ErrorDescription output column has been populated with the Error Message - "Attempted to Divide By Zero." as expected.

This can be greatly extended to have multpile output all being directed to the destinations of your choice. I, however, always include one output that will deal with any error rows that may result from the script component.

Will Vaughan Make a Difference?

The Question has to be: Will Michael Vaughan make any difference to England's current run of bad results. Vaughans personal form in ODIs is not great, averaging a mere 28.36 and Englands form in ODIs over the last year has been pretty pathetic. So I predict that he won't make any difference for the one day series in Aus and the world cup i.e. no semi for England. However the Test scene is definitely his saving grace. England play West Indies and then India at home. Both teams are known to be bad tourists especially in England and they are going to make Vaughan's year.

SSIS: Event Firing within Script Tasks

When using the Script component is SSIS, I have noticed that alot of people don't take advantage of the IDTSComponentEvents Interface in the DTS namespace. This interface allows for event firing. All the existing tasks use this interface to fire the events required during their execution. Standard events include OnError, OnWarning, OnInformation and OnProgress. The Script Task can use this interface to easily portray to the Progress Tab, SSIS Logging or the EventHandler exactly what is happening in the script. I include it in all my script tasks for error handling but it can be used alot more extensively depending on the complexity. For instances showing the progress as you loop through items in a collection.

The first thing to do when setting this up is to add the System variable TaskName to the ReadOnly variables collection list.

The Code will look as follows :

 

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

Public Class ScriptMain

    Public Sub Main()

        Dim SubComponent As String = String.Empty

        Try
            ' Get local instances of variables collection
            Dim oVars As Variables = Dts.Variables

            ' Get Task name
            SubComponent = oVars.Item("System::TaskName").Value.ToString

            ' Loop abb 10 times
            For i As Integer = 1 To 10
                Dts.Events.FireProgress("My Message : Wow look at me go!", i * 10, 0, 0, SubComponent, True)
            Next

            ' Express loop success
            Dts.Events.FireInformation(0, SubComponent, "Looping Succeeded", String.Empty, 0, True)

            ' Show Error
            Dts.Events.FireError(0, SubComponent, "This would be my error!", String.Empty, 0)

            Dts.TaskResult = Dts.Results.Success
        Catch exDTS As DtsException
            Dts.Events.FireError(CInt(exDTS.ErrorCode), SubComponent, exDTS.Message.ToString, String.Empty, 0)
        Catch ex As Exception
            Dts.Events.FireError(0, SubComponent, ex.Message.ToString, String.Empty, 0)
        End Try

    End Sub

End Class

SSIS shows the progress as :


These events can also be captured by SSIS logging or the SSIS event handler.