BIDS is the acronym we use around the office for Microsoft’s SQL Server Business Intelligence Development Studio.  It’s an invaluable tool for moving data around between, into and out of SQL Server.  It’s extremely powerful and was a major improvement over the old Data Transformation Services bundled with SQL Server 2000.  The problem is, I don’t think it was ever tested by real life users.

Let’s just start with the interface.  The GUI in BIDS is pretty typical of modern Microsoft apps whereby there are multiple floating panels that you can dock anywhere you please.  They’re ok, I only have issues with the pinning when it refuses to auto-hide.  My screen isn’t big enough to have everything open but I would imagine that on a 24″ widescreen pushing 1900+ pixels wide, it would be very functional with most panels always open.  The real problem is in the Control Flow panel.  If you’ve got one lonely little task in that panel, BIDS, for some reason, can’t detect where that task is and have it auto-centered.  Most of the time I’ll open a package and have to scroll around the screen looking for the one task.  But this problem is merely annoying compared to some of the other problems.

One major issue I have is that when you first open a project, BIDS tries to validate every single package in the project.  This can take a very long time, especially if some of the packages rely on connections that aren’t live offline or rely on attached devices.  Those packages will fail validation, pop errors, and waste time - since you may not have been working on those packages anyway.

Another issue I have is with connections in the Connection Manager.  If you rename a connection, and that connection is used in a nested task, chances are pretty good that you’ve now broken that nested task.  BIDS, like most Microsoft products, uses an internal unique ID instead of the names (likely in the name of performance) to actually point to connections.  Unfortunately it doesn’t seem to recurse into all of the tasks to update the ID/names of assigned connections if you change a name - causing hours of frustration as you hunt down why a package that worked before a simple name change won’t work now.

My last complaint, for today, is with the SSIS Import and Export Wizard.  This is the only way to graphically pull or push data from and to your servers.  If you choose to copy a table from one server to another, where the table already exists, you have the option of deleting the existing data.  If you choose to delete existing records, BIDS creates an Execute SQL Task that is supposed to have all of the DELETE statements.  It doesn’t.  What you end up with is a set of GO statements.  That’s it.  One for each table you wanted to overwrite.  It took me three or four tries with primary key violations to realize that nothing was being deleted.  This is consistent and cannot be fixed.  I end up having to do the deletes on my own.  Obviously that’s not a big deal but it’s such a simple bug - did no one try to do one of these in testing?

Ugh.  Any other strange BIDS problems out there?

I talked to my friend Scott for the first time in a long time yesterday.  He and I were like brothers growing up and share like and dislike, almost without exception.  The one major difference between us would probably be our chosen paths.  While he went the photography route (the roundabout way through several stops) I went the web programming route (also the roundabout way).

The most common questions I get asked by people who are really interested in what I do is ‘what are you using to build your websites with’ and ‘what javascript framework  do you use?’  It was in explaining my choices to Scott that I felt like it would be worth explaining them to the world (potential clients may be curious).

The lesser of the two questions is what I’m using to build websites.  The desktop application I’m using is Adobe Dreamweaver but I’m really only using that for the code collapsing, snippets and site organization.  My foundation of choice is WordPress.  I’ve found it to be almost infinitely customizable and flexible enough to cover just about any project.  I found Joomla too big and hit WordPress next.  I’d love to give Drupal a try but I have a strong belief that being really strong with one tool makes you more marketable than being slightly experienced with several.  I have not met a site that WordPress didn’t like.  (But just because WordPress is in my ‘lesser’ category, don’t be fooled into thinking it’s not important.  WordPress is amazing.  It’s only lesser because it’s so easy to use and largely unnoticed by site visitors.)

The bigger impact tool I’m using right now is a javascript framework called MooTools.  Scott had never heard of MooTools and I guess this is where the line between web surfer extraordinaire and web programmer is drawn.  A surfer may know which blogging tool or BB platform he’s looking at but he’s unlikely to know which javascript framework is in use.

MooTools isn’t for javascript n00bs.  You have to be fairly comfortable with coding in general but also with CSS and the DOM.  Once you’re pretty good with those, stepping into MooTools is heaven.  Once I started working with MooTools I realized that there really wasn’t a single thing I couldn’t do with a web page.  It’s that powerful.

Once you’ve decided that MooTools is interesting and deserves looking into further, have a look at Aaron Newton’s Clientside.  All of the things you want to do on a daily basis with MooTools are covered.  Man, it’s almost all done for you!  Aaron’s got a book coming out in August and I can’t wait to get my hands on it.

I used to rely pretty heavily on the official MooTools forum which was shut down when version 1.2 of the framework was released.  I don’t lean quite as heavily on the community now but I invite everyone struggling with a MooTools script to try the unofficial forum at http://mooforum.net.  I cruise it quite often and try to help where I can.  My handle there is Lweel8 (it’s a bumper stumper for my punctuality when it comes to sporting events).

Best of luck to everyone out there.  I hope to see you in the forum.

Yep, it’s true.  It appears that one hand of Microsoft doesn’t know what the other is doing.  I haven’t found a lot of hits on this but apparently both sides of this bug confirm its existence and both claim it’s not their problem.

Symptom:

• Microsoft Excel cannot find the references inside a pivot table that references the same workbook when opened from Internet Explorer

Cause:

• Internet Explorer adds ‘[X]’ to the end of the file name when storing the file in the IE temp folder, where X represents an incrementing integer based on the number of downloads of the same file, and the square brackets are illegal characters for file names in Windows

Steps to reproduce:

• Browsing with Internet Explorer
• Clicking on a link to an Excel spreadsheet with a pivot table in it that references itself
• Clicking on "Open" rather than "Save"

The only solution I could think of was to write a VBA macro to rename the file and reattach all of the pivot tables.  I hope this saves someone else some trouble in the future.  When you think of me, think of me fondly…

( You’ll have to add a reference to Microsoft VBScript Regular Expressions 5.5 or better. )

 Sub Auto_Open()
    
    Dim sFilename As String   ‘ the corrected filename
    Dim oASheet As Worksheet  ‘ the worksheet object
    Dim oPT As PivotTable     ‘ the pivot table object
    Dim sTempSource As String ‘ the pivot table temporary source variable
    Dim sNewRange As String   ‘ the new pivot table source variable
    Dim reg As New RegExp     ‘ a regular expression for matching wildcards
    Dim i As Integer          ‘ looping integer
    Dim j As Integer          ‘ looping integer
    
    ‘ Set a regex to test whether this is a temp spreadsheet from IE.
    ‘ Only a temp spreadsheet from IE will have square brackets in the filename.
    reg.Pattern = "[[\]]"
    
    If reg.Test(ActiveWorkbook.FullName) Then
        ‘ the temp filename has square brackets in it
        
        ‘ just a little message telling the user what’s going on
        MsgBox "Renaming temp file and reattaching pivot tables…"
        
        ‘ The .Name property already has the brackets stripped out of it so
        ‘ we can build the new path using it and the .Path property.
        sFilename = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
        
        ‘ Now we save the file so that the assignment of source data to the
        ‘ pivot tables won’t fail.
        ActiveWorkbook.SaveAs Filename:=sFilename
            
        ‘ The range for the pivot table requires a square bracket
        ‘ just before the file name so we rebuild the path, less the drive letter.
        sNewRange = Replace(ActiveWorkbook.Path, "C:", "") & "\[" & ActiveWorkbook.Name & "]"
        
        ‘ now set the regex for the tail end of the spreadsheet, including the trailing "]"
        reg.IgnoreCase = True
        reg.Pattern = ".*\.xls\]"
        
        ‘ now loop through all of the sheets looking for pivot tables
        For j = 1 To ActiveWorkbook.Worksheets.Count
            Set oASheet = ActiveWorkbook.Worksheets(j)
        
            ‘ now loop through any pivot tables on the worksheet
            For i = 1 To oASheet.PivotTables.Count
                Set oPT = oASheet.PivotTables(i)
            
                sTempSource = oPT.SourceData
                
                ‘ go ahead and replace the invalid path with the new path
                sTempSource = "’" & reg.Replace(sTempSource, sNewRange)
            
                ‘oPT.PivotTableWizard SourceType:=xlDatabase, SourceData:=sTempSource
                
                ‘ assign the new path to the sourcedata property
                oPT.SourceData = sTempSource
                
                ‘ release the object
                Set oPT = Nothing
            Next i
            
            ‘ release the object
            Set oASheet = Nothing
        Next j
        
        ‘ Save the workbook, just to be thorough.
        ‘ Without saving a user would be able to close the workbook without saving
        ‘ and then open the workbook from the
        ‘ recent documents list and have broken pivot tables.
        ActiveWorkbook.Save
    Else
        ‘ Not a spreadsheet with square brackets in the filename
        ‘MsgBox "No renaming required"
    End If

End Sub

My wife is a fan of the Shopaholic series of books and one of her favourite moments is when the heroine decides she’s going to get control of her over-spending. She decides that she’s going to write all of her spending down in a notebook and analyze where everything goes. In order to do that, she goes out and buys a fancy new notebook and a beautiful, and very expensive, pen - thus going further into debt. We had to giggle when we talked about building my office because we were spending what seemed like a ton of money in order to make the money back - to pay for the office!

Well, after only a few days nights in the new space I can already see that it’s going to be great.  The lighting, the tunes, the white board, the dual monitor, the filing cabinets.  I finally have a place to work - and my wife has her dining room table back.

I read it somewhere relating to studying but you really do have to have a space set aside specifically for working.  Doing any kind of job in an inappropriate environment just leads to sub-par results…

Here’s to awesome results, CHEERS!

I’m pretty handy with a chunk of code. Within a couple of seconds I can parse just about any regular expression with a reasonable amount of success. What I can’t seem to do is write the darn things when switching between JavaScript, PHP, VB.NET and VBscript. That’s where RegexBuddy comes in.

RegexBuddy is a little, but powerful, program written by a company called JGSoft (Just Great Software - catchy, no?). I was already a supporter of EditPad Pro, which is a replacement for Notepad that allows regular expression find and replaces, tabbing, line numbering and tons of other features, but I’m downright evangelical about RegexBuddy. You can build your expressions by hand or use the helpful “Insert Token” button, you can have your expression explained back to you in plain English, you can test your expression against a string you insert, but best of all, you can have the program build the entire script you need - in the coding language of your heart’s desire!

Being that I’ve just jumped into SQL Server 2005 with both feet, my knowledge of VB.NET is not nearly as complete as my knowledge of PHP and VBscript. Even once I had my expression, and was confident that it should return the proper results, I didn’t know that I had to import a library to use it. RegexBuddy knew.

Long story short, there’s a text file I’ve been procrastinating about parsing and cleaning up for almost 3 years. Using RegexBuddy I built, tested and implemented a housekeeping function in just over an hour. A 500KB files was reduced to under 30KB and made importing into SQL a breeze. Yay RegexBuddy! Now I just need to find my credit card to pay for the program…

If you’re curious about the expression that made my life so easy, here it is: “^.* (\d?\d/\d?\d/\d{4}) to \1.* (\d{4}[BMR]|\d{7}).* \d?\d:\d\d:\d\d.* \d?\d:\d\d:\d\d.*(\d?\d:\d\d:\d\d).* \d?\d:\d\d:\d\d.* (\d?\d:\d\d:\d\d).*$” which I then used in a replace with “$1,$2,$3,$4″.