T-SQL Tuesday 50: Automation, Automation, Automation!

TSQL-Tuesday#050: The automation storyAutomation, whoah, what a subject for our monthly T-SQL Tuesday party!  This month’s event is hosted by SqlChow.  Head over to his post for more details on this month’s topic and to find out what T-SQL Tuesday party actually is, if needed:

T-SQL Tuesday #050: Automation, how much of it is the same?

Automation, what would the world be without it?  I have no idea but it would definitely not be the same!  And what would my job be without it?  Again I don’t know but it would definitely not be the same!

As a Business Intelligence consultant automation is a big part of the task list!  How else would we be able to build a data warehouse (DWH), if it wasn’t through automation?  Our ETL processes are all about automation: retrieving data (Extract), converting and remodeling it into something useable (Transform) and pushing it into the DWH (Load).  Without automation we would never be able to go home at 5PM and still couldn’t get it all done!

And it doesn’t end there, even for report delivery we use automation now and then: reports can get exported to a shared folder or emailed to a bunch of people through subscriptions.  You just set it up once and user-friendly output gets generated as scheduled!

Before I switched to the BI world – regular readers know that I used to be a software developer – automation was a big part of my job too: automating the import of flat files into an accounting application, ensuring money transactions got approved (or rejected) automatically, …  Automation is a big part of a software developer’s life too!

So as you can see it’s not only DBAs who would/could benefit from automation, practically everyone active in our IT domain can use it here and there!

Let’s see if I can share something more about automation, something which you might find useful in your daily job.  Ow yeah, do you see that Must-Have Downloads title in the right bar on this site, right underneath my tag cloud?  It mentions two of my favorite tools: SSMS Tools Pack and BIDS Helper!

SSMS Tools Pack

This great SSMS add-on, developed by Mladen Prajdic, should contain something useful for everyone using SQL Server.  My most favorite functionality is the Script Grid Results item when right-clicking the results of a query:

Script Grid Results

Using that functionality you can generate a script that inserts data into a table, real handy if you quickly want to move some data over into another table or obviously if you just want to script out your data!  Here’s your typing automated for you!

BIDS Helper

This is a must-have add-on for Visual Studio (BIDS) that adds a lot of additional functionality for SSIS and SSAS (both multidimensional and tabular) and even a bit for SSRS projects.

My favorite of them all: Smart Diff!  Using the regular Compare function on an SSIS package or SSRS report doesn’t produce the most readable result.  Since these are XML files they are a bit hard to read to humans.  And they contain item positioning info which obfuscates it all even more.  Smart Diff is smart enough to only highlight the parts in which you as developer are actually interested.  Have a look at the following documentation page and the screenshots will immediately clarify what I mean: Smart Diff

Other really useful additions are:

Sort Project Files: how annoying if your SSIS packages are not displayed alphabetically, right?  With this BIDS Helper functionality you can just right-click the SSIS Packages node and choose Sort by name!

Expression and Configuration Highlighter (SSIS): adds colored triangles on connection manager icons to indicate that they are configured through package configuration.  Same for tasks and connection manager which have an expression on them in a property somewhere.  Very useful while debugging packages you’re not familiar with!

And there’s much more, have a look at the main documentation page for the full list.

I hope the above info helps you in getting a couple of your daily tasks improved, or perhaps even some of your daily annoyances removed.  In my case it surely did!

Have fun!




© 2008-2014 BI: Beer Intelligence? All Rights Reserved