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.