Generic OnError Event Handler in SSIS

Josh,

So you're needing to custom log errors in SSIS, and naturally you seek to utilize the "OnError" event handler. You don't want to have to implement an event handler on each task, so you create one at the package level. You place an "Execute SQL Task" in the event handler and set it up to log the error. Great!

You then test with an error, and the event handler runs, but when you go to check the custom log in SQL Server, you see multiple entries for the same error! There has to be a way to ensure only one error gets logged...

Case Sensitive SQL Statements

Josh,

So, you have a column with some upper case and lower case values.  By default, SQL Server treats upper case and lower case the same.  The case sensitivity can be set at the Server, Database, or Column level as well as on a query by query basis...

SSAS Sorting Attribute Member by Secondary Attribute

Josh,

So you have an attribute like Month that contains values of "January", "February", etc.  By default this attribute will sort by text order, but this can be changed to sort off another attribute (i.e. Month Value).

SQL Derived Table from Values

Josh,

So you have a small set of data you want to use in a query.  The data is static and temporary in nature so you don't really want to store it in a table.  A derived table from values might do the trick!

SSRS Multi-Value Parameter Performance Issue

Josh,

So you've built a report off a query that uses a multi-value parameter and you've defaulted the parameter to use every option from a dataset.  This works fine for a few values, but when the multi-value parameter contains hundreds, the query is dreadfully slow!

The solution is to add a default value to ignore your parameter in the query...

Create Hash of Multiple Columns

Josh,

So, you need to compare several columns of one table to several columns of another.  Perhaps you are checking to see if data has changed before updating a row, or you need to track if multiple columns have changed.  Besides doing a bunch of AND statements, a nifty feature of SQL is the HASHBYTES function.

Auditing MS SQL Dependencies

Josh,

So one of the concepts discussed at the Kimball training was around audit metadata and tracking the objects and actions that get a Dimension or Fact to its final result. This definitely seems like it could be valuable but also could require a lot of effort.  Unless it is a firm business requirement, it isn't likely to be implemented due to the large amount of time and effort to track and document the lineage.

However, some of the tracking might be already done for you!

Row Level Security in MS SQL

Dear Josh,

You just got back from the excellent Kimball DW/BI Lifecycle in Depth in NYC, and one of the concepts briefly mentioned was row level security.  You made a note to look into this and came up with this demo script: