tim laqua dot com Thoughts and Code from Tim Laqua


Adding Windows Event Log Logging To Existing SSIS Packages via C#

While working with some SSIS logging, it occured to me that there's really no good way to globally apply the same logging to all packages on your server. For our production SSIS server, it would make sense to log all OnError events to the Application Event log. Then, whatever event log monitoring app you use can notify you of the package error (with details!). Thus, this app was born - an app to remove pre-existing LogProviders and add in the intended LogProvider and options.

This is really just an example of the implementation that worked for my purposes - I'm sure some of you would prefer to log elsewhere. This should be enough to get you going. Please drop a line in the Comments if you post alternative approaches.


SSISForcedLogging.Console.exe "Z:\SSIS Packages"
SSISForcedLogging.Console.Exe "Z:\SSIS Packages\Package1.dtsx"

Customer Service at the Golf Course (the customer is NOT always right)

In most businesses, the name of the game is giving the customer what they want when they want it. Putting the right opportunities in front of them at the right time. At the golf course (the elitist ones), the game changes quite a bit. Take the Pro Shop for example - everything is overpriced, at least 20% above what you could get it for at TGW or GolfGalaxy. Why? Because if you're in need of something before a round and you remember just as you're standing in the pro shop, you're certainly not going to hop in your car and head over to GolfGalaxy - you just buy it there, you're trapped more or less. Don't even bother trying to work with the pro shop guys on the price either, they could care less. Which brings me to my second point - the staff.


SSAS Cube Action: Cells Target type, URL Action type Example

Originally, we were charged with figuring out how to display SSAS cube measure descriptions via ToolTip in Excel 2007. If that's your plan, forget it - after some reading up on the interwebs, it appears that Excel doesn't even request the Description property. Additionally, if you want to add a description to Calculated Members, you have to hack it in (yuck).

So we went with a simple, albeit relatively crude (but effective), alternative - implementing a URL action for Cells so users can easily link out to a definition of the measure they're looking at.

Create a new action in your cube (Open up the cube definition, Actions tab) and configure similar to this:

Name: View Member Definition
Action Target
    Target Type: Cells
    Target object: All cells
Action Content
    Type: URL
    Action expression: "http://i.domain.com/doc/Defs.aspx#" 
                       + [Measures].CurrentMember.Name
Additional Properties
    Invocation: Interactive
    Description: View Member Definition
    "View Definition Of " + [Measures].CurrentMember.Name + "..."
    Caption is MDX: True

When you're finished, it should look something like this:

For a possible way to implement the aforementioned Definitions.aspx, check out http://timlaqua.com/2009/03/scrolling-to-and-highlighting-anchor-target-via-javascript/ - which describes an early endpoint we used for this project.


Scrolling to and Highlighting Anchor Target via JavaScript

I implemented a SSAS cube action to link to a SharePoint page (URL Action) with the name of the measure contained in the cell the user fired the action from. The theory here is to have a page that contains a list of definitions for all measures (both real and calculated) in the cube. As we were flushing out this implementation, it was suggested that the page should scroll to the specified measure and highlight it in some way. The implementation of this using the CSS :target pseudo class is pretty straightforward - however we're a Microsoft shop and we absolutely have to support Internet Explorer 7 and Internet Explorer 8, so that's out.


MySQL Error 1292 – Incorrect datetime value (Daylight Savings Time / DST)

So, you have a bunch of errors from some application between 2:00 AM and 3:00 AM on the day that Daylight Savings Time occured, huh? This year, it was between 2009-03-08 02:00:00 and 2009-03-08 02:59:59 - and we had one of those applications. As Urchin 6 was trying to parse IIS logs from 2:00 AM to 3:00 AM this past Sunday, it failed and continued to fail over and over again with the error "Incorrect datetime value" when it tried to insert the non-existant date in to the MySQL 5 ( to be exact) database. MySQL was configured with a timezone of SYSTEM:

SELECT @@time_zone

Running under Windows Server 2003 Web Edition, MySQL used the correct timezone and appropriately skipped ahead at 2AM to 3AM - those times don't exist here. Unfortunately, one of the IIS servers wasn't configured to adjust the time for Daylight Savings and made a big pile of log entries between 2:00 AM and 3:00 AM (again, a non-existant time).

ERROR: (...) Failed to query the database
DETAIL: Incorrect datetime value: '2009-03-08 02:56:15'
        for column '...' at row 1

First, properly configure IIS so it records time the same way the database expects it to. The only way to get around this situation where you have essentially corrupt IIS log files is to fix the time entries in the log file and associate them with a valid time (i.e. an hour before or an hour after). Technically, you also need to shift all of the subsequent logs forward one hour as well, as IIS was recording the wrong time (-1 hour). Then let 'er rip.


Notepad++ User Defined Language file for MDX Syntax Highlighting

Following up on my previous post for GeSHi MDX Syntax Highlighting, here is a version for Notepad++. The only real issue I ran in to here is that the Nodepad++ User Language Definition system only allows two sets of boundary open/boundry close characters. One has to be square brackets - [...] - for MDX, and the other here would be a string delimiter. This means only one string delimiter can be used at a time (because the square brackets took up the first slot) and I went with single quotes. If you use double-quotes in your MDX rather than single quotes, you may want to switch the Delimiters element from:

            <Keywords name="Delimiters">[&apos;0]&apos;0</Keywords>


            <Keywords name="Delimiters">[&quot;0]&quot;0</Keywords>

If you don't have any existing user defined languages, you can simply download the following file and put it in the appropriate location. The appropriate location will vary depending on if you're using %APPDATA% or not. If you are using %APPDATA%, the location will be %APPDATA%\Notepad++

If you're not using %APPDATA%, the location is the Program Files folder for Notepad++ - usually something like C:\Program Files\Notepad++.

Download: userDefineLang.xml

If you already have existing user defined languages, you will need to edit your current userDefineLang.xml file and add the following UserLang node to your file (NodepadPlus is the root node - you only want to add in the UserLang node):

    <UserLang name="MDX" ext="mdx">
            <Global caseIgnored="yes" />
            <TreatAsSymbol comment="no" commentLine="no" />
            <Prefix words1="yes" words2="yes" words3="yes" words4="yes" />
            <Keywords name="Delimiters">[&apos;0]&apos;0</Keywords>
            <Keywords name="Folder+">SELECT</Keywords>
            <Keywords name="Folder-">FROM</Keywords>
            <Keywords name="Operators">- ( ) * , . / : ; { | } + &lt; = &gt;</Keywords>
            <Keywords name="Comment">1/* 2*/ 0// 0--</Keywords>
            <Keywords name="Words1">WHERE COLUMNS ROWS NON EMPTY AS ON CALCULATE CASE EXISTING FREEZE IF SCOPE ALTER CUBE CREATE ACTION CELL CALCULATION GLOBAL WITH MEMBER SESSION SET SUBCUBE DROP SUBCUBE REFRESH CALL CLEAR CALCULATIONS DRILLTHROUGH UPDATE SetToArray This Leaves Current Item Root StrToTuple Error Hierarchy Dimension Dimensions Level Levels IsAncestor IsEmpty IsGeneration IsLeaf IsSibling CalculationPassValue CoalesceEmpty Generate IIf LookupCube MemberToStr Name Properties SetToStr TupleToStr UniqueName UserName</Keywords>
            <Keywords name="Words2">Aggregate Avg CalculationCurrentPass CalculationPassValue CoalesceEmpty Correlation Count Covariance CovarianceN DistinctCount IIf LinRegIntercept LinRegPoint LinRegR2 LinRegSlope LookupCube Max Median Min Ordinal Predict Rank RollupChildren Stddev StddevP StrToValue Sum Value Var Variance VarianceP VarP&#x0009;&#x0009;</Keywords>
            <Keywords name="Words3">AllCalculatedMembers AllMembers Ancestors Ascendants Axis BottomCount BottomPercent BottomSum Children Crossjoin CurrentOrdinal Descendants Distinct DrilldownLevel DrilldownLevelBottom DrilldownLevelTop DrilldownMember DrilldownMemberBottom DrilldownMemberTop DrillupLevel DrillupMember Except Exists Extract Filter Generate Head Hierarchize Intersect LastPeriods Members Mtd NameToSet NonEmptyCrossjoin Order PeriodsToDate Qtd Siblings StripCalculatedMembers StrToSet Subset Tail ToggleDrillState TopCount TopPercent TopSum Union Unorder VisualTotals Wtd Ytd</Keywords>
            <Keywords name="Words4">Ancestor ClosingPeriod Cousin CurrentMember DataMember DefaultMember FirstChild FirstSibling Item Lag LastChild LastSibling Lead LinkMember NextMember OpeningPeriod ParallelPeriod Parent PrevMember StrToMember UnknownMember ValidMeasure</Keywords>
            <WordsStyle name="DEFAULT" styleID="11" fgColor="000000" bgColor="FFFFFF" fontName="Courier New" fontStyle="0" />
            <WordsStyle name="FOLDEROPEN" styleID="12" fgColor="0000FF" bgColor="FFFFFF" fontName="Courier New" fontStyle="1" />
            <WordsStyle name="FOLDERCLOSE" styleID="13" fgColor="0000FF" bgColor="FFFFFF" fontName="Courier New" fontStyle="1" />
            <WordsStyle name="KEYWORD1" styleID="5" fgColor="0000FF" bgColor="FFFFFF" fontName="Courier New" fontStyle="1" />
            <WordsStyle name="KEYWORD2" styleID="6" fgColor="FF0000" bgColor="FFFFFF" fontName="" fontStyle="0" />
            <WordsStyle name="KEYWORD3" styleID="7" fgColor="804000" bgColor="FFFFFF" fontName="" fontStyle="0" />
            <WordsStyle name="KEYWORD4" styleID="8" fgColor="804000" bgColor="FFFFFF" fontName="" fontStyle="0" />
            <WordsStyle name="COMMENT" styleID="1" fgColor="000000" bgColor="FFFFFF" fontName="" fontStyle="0" />
            <WordsStyle name="COMMENT LINE" styleID="2" fgColor="000000" bgColor="FFFFFF" fontName="" fontStyle="0" />
            <WordsStyle name="NUMBER" styleID="4" fgColor="800040" bgColor="FFFFFF" fontName="" fontStyle="1" />
            <WordsStyle name="OPERATOR" styleID="10" fgColor="000000" bgColor="FFFFFF" fontName="Courier New" fontStyle="1" />
            <WordsStyle name="DELIMINER1" styleID="14" fgColor="000000" bgColor="FFFFFF" fontName="" fontStyle="0" />
            <WordsStyle name="DELIMINER2" styleID="15" fgColor="6C6C6C" bgColor="FFFFFF" fontName="Courier New" fontStyle="1" />
            <WordsStyle name="DELIMINER3" styleID="16" fgColor="000000" bgColor="FFFFFF" fontName="" fontStyle="0" />

GeSHi Language file for MDX Syntax Highlighting

For years now, it's bothered me that there was no syntax highlighting for MDX in GeSHi - so I put a language file together for MDX. To use this file: download it, unzip it, and put the mdx.php file in to your GeSHi languages folder (for WordPress wp-syntax, this is themes/wp-syntax/geshi/geshi) - it's the folder with all the languagename.php files in it.

Let me know if it doesn't catch something or if you have suggestions for how to highlight keywords differently.

Download: geshi-mdx.zip

You can also copy and paste the following in to a new file and name it mdx.php - then place it in the appropriate folder:


SQL Server Analysis Services Command (XMLA) Agent Job Step Reports Success On Command Failure

I ran in to this the other day, did some googling, and really did not like what I saw for workarounds. In SQL Server 2005, when an XMLA job step fails (returns an Exception node in the XML response), the job step still reports success (because it's defining success as "did I get a response") - this has been fixed in SQL Server 2008. Common workarounds are using ascmd.exe or SSIS to handle the XMLA commands (ish - both of those solutions add a lot of complexity for a simple problem). So, I came up with a workaround that checks the text of the previous job step for the substring "<Exception ". It's been working thus far, with no issues.

After each XMLA command step, insert a T-SQL step to verify that the XMLA command step succeeded:

SET @JobName = ‘Name Of Job This Step Belongs to’
DECLARE @Message VARCHAR(1024)
SELECT TOP 1 @Message = CAST([message] AS VARCHAR(1024))
FROM msdb.dbo.sysjobhistory a
INNER JOIN msdb.dbo.sysjobs b
ON a.job_id = b.job_id AND b.[NAME] = @JobName
ORDER BY run_date DESC, run_time DESC, step_id DESC
IF @Message LIKE%<Exception %RAISERROR (@Message, 17, 1)

UPDATE (2009-04-03): Added , step_id DESC to ORDER BY clause - when the XMLA job fails instantly (say you tried to process a nonexistant partition), run_time doesn't have enough granularity to sort properly.

Once your done, your job steps will look something like this: