An idea or two...

I'm back!

2011-12-29T12:02:00.002Z

Due to technical difficulties (on my other blog) I have decided to move back here again.

I know I haven't been the most frequent blogger but I'm hoping the new year brings with it the new desire to blog with greater regularity.

I will also be moving the blogs from the other site back to this one...

See you soon!

SQL PASS 2011

2011-10-18T14:48:00.000Z

After a long flight back from what might be the furthest distance from South Africa, I am back in JHB from a great SQL PASS in Seattle.

This was my first PASS and I though the whole experience was top notch. The colleagues I went with, the old colleagues I met up with there as well as a pretty well organised event made it all a very enjoyable experience.

The slight downer would have to be the badly organised schedule with key sessions conflicting and the poorly planned room allocation. Key speakers with their key topics were often placed in smaller rooms which were then closed minutes before the start due to vast numbers and demand. This actually caused us to miss Marco Russo’s “Vertipaq vs OLAP: Change your data modelling approach”, which was very disappointing.

But besides that, I managed to attend some really good sessions. I will be going through these along with some of my personal findings with regard Denali (now called SQL Server 2012) in my next few blog posts.

Please pop in and have a read when they are available.

SSIS Data Cleansing – Regular Expressions

2011-05-26T07:52:00.001Z

Check out my latest post about data cleansing with regular expressions...

Blog moved!

2011-05-16T10:13:00.002Z

Hi,

Please be aware that I have moved my blog to TwentyOneTen.

Colin.

New Blog

2011-05-10T13:00:00.002Z

Please find my latest blog post here!

Analysis Services Partition Creation in SSIS

2009-05-08T15:34:00.001Z

On my return from a short holiday last week (South Africa had two public holidays in one week – Monday and Friday so taking 3 days off gave me the whole week ; ) ) I found an email from a blog reader (Ashkan) asking me to add a blog about SSAS partition creation in SSIS. I had promised that I would in my previous blog so I thought I better get to work.

So here it is…

For this example, I am going to discuss how I usually set up SSAS partitioning on a new cube. In most cases, this is at the stage when the warehouse is fully populated with history data and I need to create a large number of SSAS partitions to represent all this data. So what we need to start is a cube that has been deployed to a SSAS server. (This example will add partitions to the an existing cube on a server not partitions (metadata) to a cube in an Analysis Services project.) This deployed cube will have at least one measure group in it and just the default partition. What I would then need to do, is change the default partition’s query definition to point to my first data set, usually my first month in my warehouse (assuming we are partitioning by month) and then use the following code to create all the partitions that I need.

For this example, I am going to use the sample SSAS database Adventure Works and more specifically the default partition, Internet_Sales which is part of the Measure Group, Internet Sales in the cube, Adventure Works DW.

This partition’s source is set to table binding and is mapped to a specific source table in the cube’s DSV. For this example, I want to partition the measure group against a single fact table aiming each partition at a specific month in that table. Therefore I need to change the partition to query binding and set the source value to:

SELECT fis.[ProductKey],fis.[OrderDateKey],fis.[DueDateKey],fis.[ShipDateKey],fis.[CustomerKey],fis.[PromotionKey],fis.[CurrencyKey],fis.[SalesTerritoryKey],fis.[SalesOrderNumber],fis.[SalesOrderLineNumber],fis.[RevisionNumber],fis.[OrderQuantity],fis.[UnitPrice],fis.[ExtendedAmount],fis.[UnitPriceDiscountPct],fis.[DiscountAmount],fis.[ProductStandardCost],fis.[TotalProductCost],fis.[SalesAmount],fis.[TaxAmt],fis.[Freight],fis.[CarrierTrackingNumber],fis.[CustomerPONumber],
CONVERT ( CHAR ( 10 ), SalesOrderNumber ) + 'Line ' + CONVERT ( CHAR ( 4 ), SalesOrderLineNumber ) AS [SalesOrderDesc]
FROM [dbo].[FactInternetSales] fis
INNER JOIN dbo.DimTime dt
ON fis.OrderDateKey = dt.TimeKey
WHERE Cast(Convert(varchar(6), FullDateAlternateKey, 112) as int) = 200107

(I realise that this is not the best SQL query in the world but it illustrates the point)

The partition Internet_Sales should then be renamed to something that makes more sense. I usually name partitions according to the measure group to which they belong and the specific month they are assigned to. e.g. InternetSales200107

Once this has been done we have a complete partition that we can use to clone more… and you thought cloning was illegal! ; )

In my previous blog (Analysis Services Partition Processing), I went into a lot of detail explaining the script task and how I prefer to capture the XMLA and executing it against the server using an Analysis Services DDL Task. This SSIS package is very similar and therefore I am not going to repeat all that detail here but rather provide the code that is different and follow up with an explanation.

The Script Task’s code would look like this:

Imports System, System.Data, System.Math, Microsoft.SqlServer.Dts.Runtime
Imports AMO = Microsoft.AnalysisServices
Imports Microsoft.AnalysisServices.QueryBinding

Public Class ScriptMain

    Public Sub Main()

        Dim amoServer As AMO.Server, amoMeasureGroup As AMO.MeasureGroup
        Dim amoPartition As AMO.Partition, oVariables As Variables = Dts.Variables
        Dim strXMLAScript As String, strTaskName As String
        Dim amoQueryBinding As AMO.QueryBinding, amoNewPartition As AMO.Partition

        Try
            Dts.VariableDispenser.LockOneForRead("System::TaskName", oVariables)
            strTaskName = oVariables.Item("System::TaskName").Value.ToString

            amoServer = New AMO.Server()
            amoServer.Connect("Data Source=LOCALHOST;Initial Catalog=Adventure Works DW;" & _
                "Provider=MSOLAP.3;Integrated Security=SSPI;Impersonation Level=Impersonate;")
            amoMeasureGroup = amoServer.Databases.FindByName(amoServer.ConnectionInfo.Catalog _
                .ToString).Cubes.FindByName("Adventure Works").MeasureGroups. _
                FindByName("Internet Sales")

            amoServer.CaptureXml = True

            ' Get first partition
            amoPartition = amoMeasureGroup.Partitions(0)

            ' Loop through number of partitions needed
            For iMonth As Integer = 200108 To 200112

                amoNewPartition = amoPartition.Clone()
                amoNewPartition.ID = "InternetSales " & iMonth
                amoNewPartition.Name = "InternetSales " & iMonth
                amoNewPartition.Slice = "" ' Add as needed

                ' Create new query binding
                amoQueryBinding = New AMO.QueryBinding

                ' Set new query binding
                amoQueryBinding.QueryDefinition = GetQueryBindingText(iMonth.ToString)
                amoQueryBinding.DataSourceID = amoPartition.DataSource.ID
                amoNewPartition.Source = amoQueryBinding

                ' Add to measure group and update parition
                amoMeasureGroup.Partitions.Add(amoNewPartition)
                amoNewPartition.Update()

                ' Report progress
                Dts.Events.FireInformation(0, strTaskName, "Added partition - " & _
                    amoNewPartition.Name & " to create partition list.", String.Empty, _
                    0, True)

            Next

            ' Set in transaction and in parallel properties and capture
            strXMLAScript = amoServer.ConcatenateCaptureLog(True, False)

            ' Write to package variable
            Dts.VariableDispenser.LockOneForWrite("User::strXMLAScript", oVariables)
            oVariables.Item("User::strXMLAScript").Value = strXMLAScript

            Dts.TaskResult = Dts.Results.Success
        Catch ex As Exception
            Dts.Events.FireError(0, strTaskName, ex.Message, String.Empty, 0)
        End Try

    End Sub

    Private Function GetQueryBindingText(ByVal strMonth As String) As String

        Return "SELECT fis.[ProductKey],fis.[OrderDateKey],fis.[DueDateKey],fis.[ShipDateKey]," & _
            "fis.[CustomerKey],fis.[PromotionKey],fis.[CurrencyKey],fis.[SalesTerritoryKey]," & _
            "fis.[SalesOrderNumber], fis.[SalesOrderLineNumber], fis.[RevisionNumber]," & _
            "fis.[OrderQuantity],fis.[UnitPrice],fis.[ExtendedAmount]," & _
            "fis.[UnitPriceDiscountPct], " & _
            "fis.[DiscountAmount], fis.[ProductStandardCost], fis.[TotalProductCost], " & _
            "fis.[SalesAmount], fis.[TaxAmt], fis.[Freight], fis.[CarrierTrackingNumber], " & _
            "fis.[CustomerPONumber], " & _
            "CONVERT ( CHAR ( 10 ), SalesOrderNumber )  + 'Line '  + CONVERT ( CHAR ( 4 ), " & _
            "SalesOrderLineNumber ) AS [SalesOrderDesc] " & _
            "FROM [dbo].[FactInternetSales] fis " & _
            "INNER JOIN dbo.DimTime dt " & _
         "ON fis.OrderDateKey = dt.TimeKey " & _
            "WHERE Cast(Convert(varchar(6), FullDateAlternateKey, 112) as int) = " & strMonth

    End Function

End Class

This code will loop through a list of your choice (I have simply chosen to loop through 5 months to prove the concept) creating new partitions. This is done by cloning the existing one (the one we edited earlier) and then overwriting the properties that need to be changed. These properties include the partition ID, Name and the query binding. If the cube is using a slicer then you could overwrite this property too. I haven’t included it in this example.

Please notice that I needed to change the parallel argument in the ConcatenateCaptureLog method to false from the example in my previous blog.

strXMLAScript = amoServer.ConcatenateCaptureLog(True, False)

Once this is complete, you can execute the package and it will create all the partitions you would like (or at least according to your loop). Or create new partitions (by making a few alterations) as and when they are needed at the end of your ETL but before a dimension and measure group OLAP process is done. This obviously will need to be approved by your SSAS DBA. I have on the odd occasion found that creating SSAS objects like partitions dynamically through code in a scheduled job, without letting your DBA know, can get you into a little bit of hot water…

Hope you find this helpful!

Happy Deving!

(I have added a zip file to SkyDrive with the sample package. It can be found here).

A very good cause!

2009-03-23T13:15:00.001Z

Please help Sutha out, if you can. It really is a worthy cause!

Please visit this blog of Mark’s for further details.

Thanks!

Exporting to a text file from Reporting Services

2009-02-20T12:39:00.001Z

(Personal Reference)

A client recently asked me how one could add an export to text file in Reporting Services. I won’t go through the business reasons for this but this is how I suggested they go about it.

Add the following text:

<Extension Name="TXT" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
    <OverrideNames>
        <Name Language="en-US">TXT (Pipe Delimited Text File)</Name>
    </OverrideNames>
    <Configuration>
        <DeviceInfo>
            <FieldDelimiter>|</FieldDelimiter>
            <Extension>TXT</Extension>
            <Encoding>ASCII</Encoding>
        <NoHeader>true</NoHeader>
        </DeviceInfo>
    </Configuration>
</Extension>

to the SSRS config file, rsreportserver.config, usually located in:

C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer. The text will need to be included with the other renders in that file between the render tags.

Happy Deving!

Blogging about blogging…

2009-02-20T08:30:00.001Z

Having recently decided to start blogging again I was on a hunt for a decent blog stat counter. I have used StatCounter in the past but it only allows the last 100 hits (without shelling out fun tokens) to be saved. This was not going to be sufficient to allow for analysis. I was told by a mate to try out Google Analytics and I must say I am very impressed! It allows for all types of analysis and reporting. Go check it out if you are blogging. It is really amazing!

Analysis Services Partition Processing

2009-02-17T19:03:00.004Z

Recently, while trawling the internet, I noticed that there are not a lot of SSIS examples detailing how to process Analysis Services cubes, especially if you are using Analysis Services partitioning. So I thought I would add an approach I have been using over the last few years.

While reading this you are probably wondering what all the fuss is about. Why not just use the Analysis Services Processing Task? Well, I guess this is definitely an option when processing dimensions and full cubes or measure groups with small data sets. This task is very useful and does provide a logging framework with a lot of detail. However, it is not dynamic. If the partitioning strategy defined is by month for example and the requirement is to process just those partitions with new data, then a more dynamic approach may be needed.

This is the scenario for which I am aiming this blog. This approach does add more code and therefore possibly more maintenance but may be a necessity when encountering larger data sets.

So if we look at the example mentioned above where we are partitioning by month. We could have a measure group with n monthly partitions and a need to process the last two as they could have new data. In this case, we may need a new Analysis Services partition or two which would represent these new months. (I am not going to add the code that creates new partitions in this blog to avoid complexity. I will save this for another blog!)

In this approach I am going to create the relevant XMLA scripts and then use the SSIS Analysis Services Execute DDL Task to execute them. I have seen many people attempt this approach by creating their own XMLA scripts. However, anyone who has tried this before will agree that the string manipulation required can be a real headache. So having a bit of a development background myself I prefer to allow the AMO (Analysis Management Objects) object model to do the hard work for me.

So onto the SSIS package…

First I declare a package variable: strXMLAScript (string)

Then add a script task and add the following code:

Imports System, System.Data, System.Math, Microsoft.SqlServer.Dts.Runtime
Imports AMO = Microsoft.AnalysisServices, Microsoft.SqlServer.Management
Imports Microsoft.AnalysisServices.QueryBinding

Note: You will need to add the relevant AMO reference to the script task before being able to use it. However, when adding a reference the relevant component i.e. Analysis Management Objects may not be available. This can be made available by coping the Microsoft.AnalysisServices.dll from “\Program Files\Microsoft SQL Server\90\sdk\Assemblies\Microsoft.AnalysisServices.DLL” into the relevant framework folder version e.g. “\windows\Microsoft.NET\Framework\v2.0.50727”.

Then declare script variables and set the task variable for event firing:

Dim amoServer As AMO.Server, amoMeasureGroup As AMO.MeasureGroup, amoPartition As AMO.Partition
Dim strXMLAScript As String, strTaskName As String, oVariables As Variables = Dts.Variables

Dts.VariableDispenser.LockOneForRead("System::TaskName", oVariables)
strTaskName = oVariables.Item("System::TaskName").Value.ToString

Then initialized the relevant Analysis Services objects:

amoServer = New AMO.Server()
amoServer.Connect("Data Source=LOCALHOST;Initial Catalog=Adventure Works DW;Provider=MSOLAP.3;Integrated Security=SSPI;Impersonation Level=Impersonate;")
amoMeasureGroup = amoServer.Databases.FindByName(amoServer.ConnectionInfo.Catalog.ToString).Cubes.FindByName("Adventure Works").MeasureGroups.FindByName("Reseller Sales")

Then set the server property CaptureXML. (This will make sure that all actions executed against the server during this session will not be implemented but rather that the XMLA for each action is made available.)

amoServer.CaptureXml = True

Once this is done the search criteria for finding the correct partitions will need to be implemented. This will obviously be site specific but I usually name the partitions by measure group and then yyyyMM. So the relevant partitions I am looking for are ResellerSales200901, ResellerSales200902.

' Loop through measure groups partitions
For Each amoPartition In amoMeasureGroup.Partitions
    Select Case amoPartition.Name.ToUpper
        Case amoMeasureGroup.Name.ToUpper & " " & DateAdd(DateInterval.Month, -1, Now.Date).ToString("yyyyMM"), _
            amoMeasureGroup.Name.ToUpper & " " & Now.Date.ToString("yyyyMM")
            amoPartition.Process(AMO.ProcessType.ProcessFull) ' Could be any process Type required

            ' Report the partions that have been added to the process list
            Dts.Events.FireInformation(0, strTaskName, "Added partition - " & amoPartition.Name & _
                " to process list.", String.Empty, 0, True)
    End Select
Next

Note: Even when the loop is complete, no processing has been done. Only the XMLA script has been captured. Also note that I usually add an information event in the loop so I can easily highlight in my logging framework the partitions that have been added to the process list. I have found this information very useful when debugging.

We are now able to capture the XMLA script that the AMO object model has generated and set the ProcessInTransaction and ProcessInParallel properties as required. The package variable (strXMLAScript) is then populated with the XMLA string .

' Set in transaction and in parallel properties and capture
strXMLAScript = amoServer.ConcatenateCaptureLog(True, True)

' Write to package variable
Dts.VariableDispenser.LockOneForWrite("User::strXMLAString", oVariables)
oVariables.Item("User::strXMLAString").Value = strXMLAScript

The AMO server object does allow for the execution on the XMLA script once it has all been captured. This can be done with the following method:

amoServer.ExecuteCaptureLog(True, True)

However, this will require that you write a lot more code to capture all the possible errors and/or warnings that may occur. I prefer to allow the Analysis Services Execute DDL Task to do all this work for me. It also makes sure that all the relevant events that may need to be captured i.e. Onprogress, OnInformation, OnWarning and OnError are easily captured by your logging framework.

So all that is needed now is the Analysis Services Execute DDL Task source property is set to the variable strXMLA and we are ready to process.

The following shows the execution results with all the possible events that could be captured by your logging framework:

This is clearly a very simple example but can easily extend to accommodate for most, if not all, of your partition processing needs.

Hope you find this helpful!

Happy Deving!

(I have added a zip file to SkyDrive with the sample package and xmla to add the required partitions to the Adventure Works DW Cube. It can be found here).

A new blog

2009-02-03T08:35:00.005Z

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

2008-08-21T11:23:00.004Z

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

2007-10-19T12:58:00.000Z

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

2007-07-27T07:50:00.000Z

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

2007-04-25T09:51:00.000Z

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

2007-04-18T09:54:00.000Z

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!

2007-03-29T10:02:00.000Z

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)

2007-02-12T17:31:00.000Z

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

2007-02-08T13:42:00.001Z

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

2007-01-11T14:38:00.000Z

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

2007-01-10T17:33:00.000Z

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?

2007-01-08T21:40:00.000Z

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

2007-01-08T17:38:00.000Z

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.

SSIS Macros - Part 3 : Get Task Event Handlers

2006-12-16T14:29:00.000Z

Anyone who has been using SSIS for a reasonable amount of time will probably be very familiar with SSIS event handlers. I have found this to be a very useful feature and it always one to avoid the spider web nightmare that can occur when you are trying to capture each task's error and end up having a failure constraint coming from each of them.

However when using events one has to be careful at which level you handle them. If you use the OnError event at the package level and the task level, and the task fails, you will notice that the event will fire twice due to the fact that events bubble up from each task to their container. This can easily be overcome by setting the task system variable propogate in the event handler to false. However whenever you are developing a complex task or have need to edit one, it is difficult without a lot of clicking to find out which tasks have event handler, which have progate set to false and which are being handled at the container or package level.

Once again, I thought this would be an ideal time to write a quick macro that would allow me to see the event handler information for more complex packages.

Here is what is displayed by the macro I use:

Below is a sample of the code I used to get this information:

        Dim oSSISApp As New SSIS.Application, oPackage As SSIS.Package, oTaskHost As SSIS.TaskHost
        Dim oExec As SSIS.Executable, oEventHandler As SSIS.DtsEventHandler, oContainer As Object, i As Integer, j As Integer

        Try
            ' Get package object
            oPackage = oSSISApp.LoadPackage(DTE.ActiveDocument.FullName, Nothing)
            frm.gbPackage.Text = oPackage.Name.ToString

            ' Loop through events in the package
            For Each oEventHandler In oPackage.EventHandlers
                frm.lvwEventHandler.Items.Add(frm.lvwEventHandler.Items.Count + 1)

                i = frm.lvwEventHandler.Items.Count - 1
                frm.lvwEventHandler.Items(i).SubItems.Add(oPackage.GetType.Name.ToString)
                frm.lvwEventHandler.Items(i).SubItems.Add(oPackage.Name.ToString)
                frm.lvwEventHandler.Items(i).SubItems.Add(oPackage.EventHandlers.Count.ToString)
                frm.lvwEventHandler.Items(i).SubItems.Add(oEventHandler.Name.ToString)
                frm.lvwEventHandler.Items(i).SubItems.Add(oEventHandler.Variables.Item("Propagate").Value.ToString)
            Next

            ' Loop through executables in package
            For Each oExec In oPackage.Executables
                ' Check that the executable is a task
                If oExec.GetType.Name = "TaskHost" Then

                    ' Set the SSIS type
                    oTaskHost = CType(oExec, SSIS.TaskHost)

                    ' If no events
                    If oTaskHost.EventHandlers.Count = 0 Then
                        frm.lvwEventHandler.Items.Add(frm.lvwEventHandler.Items.Count + 1)

                        i = frm.lvwEventHandler.Items.Count - 1
                        frm.lvwEventHandler.Items(i).SubItems.Add(oExec.GetType.Name.ToString)
                        frm.lvwEventHandler.Items(i).SubItems.Add(oTaskHost.Name.ToString)
                        frm.lvwEventHandler.Items(i).SubItems.Add(oTaskHost.EventHandlers.Count.ToString)
                    End If

                    ' Loop through event handlers
                    For Each oEventHandler In oTaskHost.EventHandlers
                        frm.lvwEventHandler.Items.Add(frm.lvwEventHandler.Items.Count + 1)

                        j = frm.lvwEventHandler.Items.Count - 1
                        frm.lvwEventHandler.Items(j).SubItems.Add(oExec.GetType.Name.ToString)
                        frm.lvwEventHandler.Items(j).SubItems.Add(oTaskHost.Name.ToString)
                        frm.lvwEventHandler.Items(j).SubItems.Add(oTaskHost.EventHandlers.Count.ToString)
                        frm.lvwEventHandler.Items(j).SubItems.Add(oEventHandler.Name.ToString)
                        frm.lvwEventHandler.Items(j).SubItems.Add(oEventHandler.Variables.Item("Propagate").Value.ToString)
                    Next
                End If
            Next
        Catch ex As Exception

        End Try

    End Sub

I hope some will find benefit from this, I definitely have.

SSIS Macros - Part 2 : Get Tasks Without Descriptions

2006-12-14T14:11:00.000Z

When using SSIS logging or the SSIS Event Handlers to get which tasks have fired and what their results were, I have found that it is very handy to have a good description for each task that is being used in each package.

The few SSIS documenting tools I have seen all take advantage of these descriptions and therefore the benefits of using them greatly outway the effort it takes to define them. However, once your package becomes more complex it can be tedous to have to go to each task click on it, then find the description property in the properties window to see if you remembered to edit the description or not. To ensure that I didn't pull anymore hair out of my head due to this this mundane process, I slapped together a simple macro that will run through a package and see if the Task and Component Descriptions are blank or are still set to the the default.

Here is a result of the macro :

Below is a sample of the code I used to get this information:

    Private Sub GetObjectDescriptions(ByVal frm As frmObjectDescriptions)

        Dim oSSISApp As New SSIS.Application, oPackage As SSIS.Package, oTaskHost As SSIS.TaskHost
        Dim oExec As SSIS.Executable, oEventHandler As SSIS.DtsEventHandler
        Dim oMainPipe As MainPipe

        Try
            ' Get package object
            oPackage = oSSISApp.LoadPackage(DTE.ActiveDocument.FullName, Nothing)

            frm.gbPackage.Text = oPackage.Name

            'Loop through executables in package
            For Each oExec In oPackage.Executables
                If oExec.GetType.Name = "TaskHost" Then

                    ' Set the SSIS type
                    oTaskHost = CType(oExec, SSIS.TaskHost)
                    Dim oNode As TreeNode = frm.tvwObjects.Nodes.Add("Task - " & oTaskHost.Name.ToString & " - " & _
                    CheckTaskDesc(oSSISApp, oTaskHost.Description.ToString))

                    ' TODO : Find out how to check if it is a data flow or not
                    Try
                        oMainPipe = CType(oTaskHost.InnerObject, MainPipe)

                        ' Loop through components
                        For i As Integer = 0 To oMainPipe.ComponentMetaDataCollection.Count - 1
                            oNode.Nodes.Add("Dataflow - " & oMainPipe.ComponentMetaDataCollection.Item(i).Name.ToString & " - " & _
                            CheckPipeCompDesc(oSSISApp, oMainPipe.ComponentMetaDataCollection.Item(i).Description.ToString))
                        Next
                    Catch ex As Exception

                    End Try
                End If
            Next

        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString)
        Finally

        End Try

    End Sub

If you are like me and have seen the benefit of having these descriptions defined and want to avoid the headache this process can cause I would recommend take a bit of time to set this up. Your receding hairline will thank you as mine did.

Ashes - 3rd Test (Perth) : Day 1 Prediction

2006-12-14T14:05:00.000Z

Well I guess that answers the Monty question. A very confincing argument for a tight test match. I, however, believe it doesn't matter, unless England score 550 in the first innings the Aussies will still take this one with a fiver from Warnie in the 2nd innings!

SSIS Macros - Part 1 : Reset Task IDs

2006-12-14T13:26:00.000Z

As a first blog and seeing that I have found myself doing alot of just SSIS development on my current project, I thought I would share a few nice-to-have visual studio 2005 macros I have been using that have made my SSIS development alot easier.

The macro for this post involves resetting Task/Container Guids in SSIS packages. For those who don't know, each Task or Container in an SSIS package will have an ID propery. This ID property is a unique GUID which uniquely identifies the object. So if you are using SSIS logging or some kind of logging through the event handlers you find that using these GUIDs can be very helpful in logging and auditing the tasks in all your packages.

However, one of the problems that may arise by using these IDs is that if you copy and paste a task into a different container (Loop or Sequence) or package the Task ID will remain the same as the source one that you copied. Therefore it will not uniquely identity that task in the package (if it exists in different containers) or in the packages of the SSIS project. This can be a real headache when you are trying to find which task caused an error or executed for a certain period of time.

If you are copying and pasting packages the same problem will occur. However, it is easy to overcome by using "Generate New ID" through the ID property of the package. This is not as easy when dealing with tasks.

The macro I have included in this post will reset all the task GUIDs in a package.
All it does is use the SSIS object model to loop through all the tasks inside a given package, find the tasks ID and then do a find and replace in the packages XML.

Note: Be aware that the macro will save the package after the IDs have been reset, so do not run it if you do not want other changes you have made to the package to be saved.

I have included sample code that will help you get started writing your own macros. This code will only reset IDs for tasks, so you will need to add your own code for containers.

Note: The object model sees each container (sequence and loop) or package as having a collection of tasks. Therefore if you want to reset all the guids you will have to write code to loop through the package, the containers and all nested containers.

Source Code:

Sub ResetGuids()

        Dim oApp As New SSIS.Application
        Dim oExec As SSIS.Executable, taskHost As SSIS.TaskHost
        Dim aszTaskID(0) As String, gdNewGuid As Guid

        Try
            ' Get package path
            Dim oPackage As SSIS.Package = oApp.LoadPackage(DTE.ActiveDocument.FullName, Nothing)

            ' Loop through tasks
            For Each oExec In oPackage.Executables
                taskHost = CType(oExec, SSIS.TaskHost)

                ' Store task Guids in an array
                ' Set value and grow array size
                aszTaskID(aszTaskID.GetUpperBound(0)) = taskHost.ID
                ReDim Preserve aszTaskID(aszTaskID.GetUpperBound(0) + 1)
            Next

            ' Edit package XML 
            DTE.ExecuteCommand("View.ViewCode")

            ' Loop through task GUIDS in the array
            For i As Integer = 0 To aszTaskID.GetUpperBound(0) - 1

                DTE.Find.FindWhat = aszTaskID(i).ToString
                DTE.Find.ReplaceWith = "{" & System.Guid.NewGuid.ToString.ToUpper & "}"

                ' Set find criteria
                DTE.Find.Target = vsFindTarget.vsFindTargetCurrentDocument
                DTE.Find.MatchCase = False
                DTE.Find.MatchWholeWord = True
                DTE.Find.MatchInHiddenText = False
                DTE.Find.Action = vsFindAction.vsFindActionReplaceAll

                If (DTE.Find.Execute() = vsFindResult.vsFindResultNotFound) Then
                    MsgBox("No values were found")
                End If
            Next

            DTE.ActiveWindow.Close(vsSaveChanges.vsSaveChangesNo)
            DTE.ActiveDocument.Save()
        Catch ex As Exception
            MsgBox(ex.Message.ToString)
        End Try

    End Sub

-- Updated on the 16 Feb 2007

The previous sample only looped through tasks in the package i.e. not tasks in sequence or loop containers. Here is a sample that will capture all IDs for all tasks and containers. This sample will recursively loop through all the package objects.

Public Sub ResetsEachExecutablesGuid()

        Dim oApp As New SSIS.Application, aszTaskID(0) As String
        Dim oExec As SSIS.Executable, taskHost As SSIS.TaskHost
        Dim gdNewGuid As Guid, SSISObject As System.Object

        Try
            ' Get package path
            Dim oPackage As SSIS.Package = oApp.LoadPackage(DTE.ActiveDocument.FullName, Nothing)

            ' Loop through tasks
            For Each oExec In oPackage.Executables
                SSISObject = oExec
                If oExec.GetType.Name.ToLower <> "taskhost" Then
                    Call LoopThroughContainer(SSISObject, aszTaskID)
                End If

                ' Capture Task IDs
                aszTaskID(aszTaskID.GetUpperBound(0)) = SSISObject.ID
                ReDim Preserve aszTaskID(aszTaskID.GetUpperBound(0) + 1)
            Next

            ' Edit package XML 
            DTE.ExecuteCommand("View.ViewCode")

            ' Loop through task ids captured
            For i As Integer = 0 To aszTaskID.GetUpperBound(0) - 1

                DTE.Find.FindWhat = aszTaskID(i).ToString
                DTE.Find.ReplaceWith = "{" & System.Guid.NewGuid.ToString.ToUpper & "}"

                ' Set find criteria
                DTE.Find.Target = vsFindTarget.vsFindTargetCurrentDocument
                DTE.Find.MatchCase = False
                DTE.Find.MatchWholeWord = True
                DTE.Find.MatchInHiddenText = False
                DTE.Find.Action = vsFindAction.vsFindActionReplaceAll

                If (DTE.Find.Execute() = vsFindResult.vsFindResultNotFound) Then
                    MsgBox("No values were found")
                End If
            Next

            DTE.ActiveWindow.Close(vsSaveChanges.vsSaveChangesNo)
            DTE.ActiveDocument.Save()
        Catch ex As Exception
            MsgBox(ex.Message.ToString)
        End Try

    End Sub

    Private Function LoopThroughContainer(ByVal oContainer As System.Object, ByVal aszTaskID() As String)

        Dim oExec As SSIS.Executable, SSISObject As System.Object

        For Each oExec In oContainer.Executables
            SSISObject = oExec
            If SSISObject.GetType.Name.ToLower <> "taskhost" Then
                Call LoopThroughContainer(SSISObject, aszTaskID)
            End If

            ' Capture Task IDs
            aszTaskID(aszTaskID.GetUpperBound(0)) = SSISObject.ID
            ReDim Preserve aszTaskID(aszTaskID.GetUpperBound(0) + 1)
        Next

    End Function