Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Make an SSIS package Delay or Wait for Data

Packages can be scheduled to run at a time when you expect data to be in a database. Instead of guessing the time when the data will be in the database we can have the package look for data in a SQL table. When the table has data then the…

Read more

2 comments, 8,778 reads

Posted in Mike Davis SQL on 21 July 2014


The XML Task in SSIS allows you to parse through an XML file and read the nodes in the XML. In a previous blog I showed how to use a web service task to get the city and state when entering a zip code.

The results we got back from…

Read more

0 comments, 2,270 reads

Posted in Mike Davis SQL on 7 April 2014

SSIS Web Service Task

The Web Service task in SSIS can be used to call a web service command to perform a needed operation in your package. The results of most web services will be in XML Format. You can save these results into a variable or into a file. Either way, you can…

Read more

2 comments, 2,295 reads

Posted in Mike Davis SQL on 31 March 2014

Using Variables and Parameters SSIS

When creating an SSIS package it is always a best practice to use variables, and parameters in 2012,  to avoid hard coding values into any part of your package. But there are some best practices involved with creating those variables/parameters also. The rest of this article will refer to variables.…

Read more

1 comments, 724 reads

Posted in Mike Davis SQL on 24 March 2014

Using Configuration Files in SSIS

Now in SQL 2012 we have parameters that make it easy, but configuration files are still an option and I still see a lot of my clients using them even on 2012 due to several reasons, but mostly because of the work to convert over.

SSIS packages are great ETL…

Read more

8 comments, 2,652 reads

Posted in Mike Davis SQL on 17 March 2014

Two Digit Dates in SQL – MM/DD/YYYY

When creating dates or numbers as strings it is sometimes required to have two digit numbers.

For example, instead of this: 3/1/2011

You want to see this: 03/01/2011

There is an easy way to do this using the Right() function and adding a string zero to the front of a…

Read more

0 comments, 753 reads

Posted in Mike Davis SQL on 10 March 2014

SSRS Top N and Bottom N reporting with Duplicates

Previously I wrote a blog on Top N and Bottom N reporting. There were a couple of gotcha I did not cover in that blog so I thought I would cover those here to answer some of the questions I have received. The other blog can be found here.


Read more

2 comments, 1,998 reads

Posted in Mike Davis SQL on 3 March 2014

Standard Deviations with CTE in SQL

I was working with a client recently getting the number of standard deviations a student was off the average number of suspensions. I accomplished this with two Common Table Expressions, (CTE).

The first CTE gets the standard deviation and the average for the entire database. The Database holds all of…

Read more

0 comments, 2,140 reads

Posted in Mike Davis SQL on 24 February 2014

SSRS – Creating Drill Through reports with MDX

If you are reporting from a Cube in SSRS and you want to drill through to another report you can set up actions just like any other drill through except one small change. The parameter you pass to the drill through report must be changed to Unique Name instead of…

Read more

1 comments, 787 reads

Posted in Mike Davis SQL on 10 February 2014

SSIS – Using Kill with SP_Who to Break locks

The dreaded table lock can occur and cause your SSIS packages to fail. A popular request I receive asks “How can I get rid of these table locks?” This blog will show you how to build a package that will kill any SPID’s that are running on your system that…

Read more

2 comments, 974 reads

Posted in Mike Davis SQL on 3 February 2014

SSAS Trending KPI

KPI’s (Key Performance Indicators) in Analysis Services are a great way to show visually if a measure is trending in right direction based on previous levels. If you want to show a measure has increased or decreased since last month, this can be accomplished by using the MDX function PrevMember.…

Read more

0 comments, 403 reads

Posted in Mike Davis SQL on 27 January 2014

SSAS Reporting Action with Date Parameters

Reporting Actions in Analysis Services allows you to open a report in Reporting Services. Most of the time users want to pass in some parameters to the report so it shows the relevant data. If the parameter name matches the item in excel then this is a breeze. The problem…

Read more

0 comments, 1,654 reads

Posted in Mike Davis SQL on 20 January 2014

SSAS – Using the Euro or Pound currency Symbol in a measure Format String

If you have a set of measures and some of them are US dollars, some are Euros, and some are UK Pounds, you want to format them correctly in SSAS.

First you will need to know the ASCII command to type a pound and euro symbol. You can find this…

Read more

1 comments, 1,337 reads

Posted in Mike Davis SQL on 13 January 2014

SSAS Dynamic KPI from a table

KPI’s (Key Performance Indicators) in Analysis Services are a great way to show visually if a measure is above or below a goal. Managing the goal numbers can be a headache because they made need to be updated often. By placing the goal numbers on a table you can dynamically…

Read more

0 comments, 1,736 reads

Posted in Mike Davis SQL on 6 January 2014

SSRS Interactive Sorting a Matrix

Sorting a matrix report can be done interactively. To perform interactive sorting, your report you will need a cell to click on to perform the action. In the image below you can see a basic matrix report with State as the row group, with year and quarter as the column…

Read more

1 comments, 920 reads

Posted in Mike Davis SQL on 30 December 2013

SSIS For Loop Skip Files

When running a For Each Loop through a set of files, sometimes you will have specific files that you do not want to load.

For example, I have a set of files named:


If I want to skip the file that starts with “W” then…

Read more

4 comments, 2,195 reads

Posted in Mike Davis SQL on 23 December 2013

Setting up Security on sub sites in SharePoint 2010

A lot of companies have several departments and want to limit the sub sites users can view. For example, the IT department should only see the IT sub site, but the HR department may need to be able to see all sub sites. To set up individual security for sub…

Read more

0 comments, 974 reads

Posted in Mike Davis SQL on 16 December 2013

SSAS – Setting Attributes as Properties for Excel

When a user is browsing your cube with excel they may not understand how to slice the data and end up using things like “first name” as a hierarchy to slice the data. This does not make for good analysis, since grouping all of the people with the same name…

Read more

1 comments, 567 reads

Posted in Mike Davis SQL on 9 December 2013

SSRS – Self Referencing Reports or Drill through to same report

When looking at a report and wanting to drill through to get more detail, we often create two reports for this. This can be accomplished by just one report by creating a self-referencing action that links to the same report. This can be done even if the report has parameters…

Read more

3 comments, 2,336 reads

Posted in Mike Davis SQL on 2 December 2013

Reverse or Mirrored Bar Chart in SSRS

There are some pretty charts out there and most of them can be done in Reporting Services. There is one type of chart I had someone ask for that is not natively built in to SSRS. But with a little manipulation you can get the look you want. Here is…

Read more

0 comments, 436 reads

Posted in Mike Davis SQL on 25 November 2013

Older posts