Data Dictionary Using Extended Properties

Documenting database objects within the database makes logical sense versus trying to maintain separate documentation externally. My typical practice has been to create SQL Server extended properties at the database, table/view and column levels. Then you can write a query against the system objects to retrieve metadata about your database.

You can create extended properties via the GUI or script it out.

Conditional Splits

My task was to load a provider table. A provider can be appointed to multiple hospitals. However, my source file was flattened and only listed a provider one time and had extra columns to determine each hospital they were appointed to. Below is a simple illustration of what the source file's data looked like:

• Provider A, Hospital 1, Hospital 2, Hospital 3
• Provider B, Hospital 1, Hospital 2, Null
  
• Provider C, Null, Hospital 2, Null
  

I decided to play around with the Conditional Split data flow transformation task. My first pass was to create a condition for providers that met the conditions to belong to each hospital. I created 3 conditions - Hospital 1, Hospital 2, Hospital 3.

Because the provider can belong to all 3 hospitals at once, doing it in a single pass wasn't going to work (as I quickly found out). The Conditional Split evaluates the record only once as soon as it meets the first criteria. This makes too much sense because that's how your basic If...Then...Else logic works. If it meets the first condition, it never gets to the next condition. Duh (blond moment) - it's not a loop.

So I then created 3 separate data flow tasks for each hospital w/ their own Conditional Split. Another option would have been to create a stored procedure with a couple of cursors. But I wanted to play with the functionality within SSIS. I think I can streamline this even more if I were to do some looping using the control flow items 'For' or 'Foreach' Loop Containers. I'll look into those next...

Back to Conditional Splits - it took me some getting use to when evaluating Nulls. For example, my Hospital 1 condition was met if Hospital 1 start date is not null and Hospital 1 end date is null. The condition expression ended up looking like this:

• !ISNULL([Hospital 1 Start Date]) && ISNULL([Hospital 1 End Date])

Create a "snaking" Multi-Column Report in SSRS 2008

To create that newspaper effect (i.e., columns) in an SSRS 2008 report, you need to set the Columns property. For some reason, I always get confused between the right-click properties versus the more traditional properties dialog. In this case, you'll need to refer to the traditional properties dialog. Be sure to select Report in the drop-down so you're modifying the report object properties. All you have to do is specify the number of columns you would like your report to display and adjust width as you see fit:


Now, here's the caveat. When you preview the report, it appears that the snaking feature isn't working. In order to actually see the columns, I had to publish the report and use that export to PDF option for the columns to magically appear.