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

SSIS Execute SQL error – No disconnected record set is available

If you get the error in SSIS that says:

…failed with the following error: “No disconnected record set is available for the specified SQL statement.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

This is can be…

Read more

0 comments, 582 reads

Posted in Mike Davis SQL on 18 November 2013

SSRS Map Layers Parameter

With the new Map control in SSRS 2008 R2 there are three different map types, Road, Aerial, and Hybrid. If you want to allow your users to change the map type you would think you could map a parameter to the property on the tile layer with these three types.…

Read more

1 comments, 1,112 reads

Posted in Mike Davis SQL on 11 November 2013

SSIS Pivot on bad data

The pivot transform in SSIS is already a pain to use. When you have bad data it makes it even worse. In this blog I am going to show how to handle bad data when using the pivot transform. You should understand how to use the pivot transform already and…

Read more

0 comments, 1,479 reads

Posted in Mike Davis SQL on 28 October 2013

SSIS Records on the Same Row – kind of like pivot

If you have two or more records on the same row, and need to write each record on its own row in a destination, you have two choices. You can do this in series or parallel in a single data flow in SSIS. Here is the input table I am…

Read more

1 comments, 1,739 reads

Posted in Mike Davis SQL on 7 October 2013

MDX YTD Calculation Issus

If you are using the templates for MDX calculation built into SSAS 2008 you might run into some strange issues. In the image below you can see the calculation is working for all years except for 2008.

My first thought was data issue. After querying the table and finding no…

Read more

0 comments, 527 reads

Posted in Mike Davis SQL on 30 September 2013

MDX Scope with an If Then statement

The scope statement in MDX is great for applying calculations to a certain area of a cube, also called a subcube. Dustin Ryan wrote a great article on the scope statement here. The issue I am going to cover will be using the scope statement when you only want to…

Read more

2 comments, 532 reads

Posted in Mike Davis SQL on 23 September 2013

Loop Through Excel Files in SSIS

You can loop through excel files using SSIS. This will use the For Each Loop container and a data flow task.

First Create a variable named strExcelfile as a string variable; you can leave the value blank.

Next, drag in a For Each Loop. Set it to For Each File,…

Read more

10 comments, 451 reads

Posted in Mike Davis SQL on 16 September 2013

Junk Dimensions with no Loading Needed

When you are working with dimensional modeling there are some situations where several dimensional attributes don’t make since to be in any other dimension. When this occurs you can combine them into one dimension call a Junk Dimension.

In this example you will see a person type dimension that only…

Read more

0 comments, 831 reads

Posted in Mike Davis SQL on 9 September 2013

Handling Large Many to Many bridge tables

In some scenarios you will need to create a many to many relationship in your cube in SSAS. One of the problems that arise from many to many bridge tables are the size of these tables. In this example we have a fact table that contains the history of a…

Read more

1 comments, 1,742 reads

Posted in Mike Davis SQL on 2 September 2013

Execute SSIS Packages with a Macro in BIDS

When running SSIS packages in BIDS it is common to click on the green arrow at the top of BIDS to run a package.

One problem that can occur when using this button is the deployment of project and the creation of the deployment manifest file. If you have turned…

Read more

1 comments, 1,475 reads

Posted in Mike Davis SQL on 19 August 2013

Environment Variables in SSIS Packages and Configuration Tables

Configuration tables are a best practice in just about any SSIS environment. They make it easy to update multiple packages from a single change. But one of the issues with configuration tables is the location of the server is different on each server. You may have a Server name of…

Read more

4 comments, 1,082 reads

Posted in Mike Davis SQL on 12 August 2013

Custom Navigation controls in SharePoint 2010

There are several ways to create a custom navigation control in SharePoint. In the below image you can see the left navigation is hidden. This is done with the use of a master page and some custom CSS. Now you want to create your own list of links to other…

Read more

0 comments, 484 reads

Posted in Mike Davis SQL on 5 August 2013

Custom Coding in SQL Report Services

Reporting Services 2008 is a very powerful tool for creating reports. It contains many different tools and even allows you to place complex expressions on reports in every field. Even with all of these tools there are some instances where you need more functionality. This is where custom coding comes…

Read more

0 comments, 400 reads

Posted in Mike Davis SQL on 29 July 2013

Creating a Rolling Date Range Named set in MDX

One Client I was working with wanted a named set that would give them the last 5 years. Of course this needed to be a rolling 5 years so it automatically moved the years up every January.

To create this I used the StrToMember function in MDX.

First To create… Read more

0 comments, 473 reads

Posted in Mike Davis SQL on 22 July 2013

SSAS Joining Facts at Different Granularities

In an SSAS cube you sometimes need to connect a dimension at a different granularity than other dimensions. For example, if you have budget measures and actual numbers. If you are trying to budget your expenses you budget at a higher level than the actual amounts. You budget money for…

Read more

1 comments, 1,416 reads

Posted in Mike Davis SQL on 15 July 2013

Report from a Cube in Reporting Services

Data warehousing has become a staple of businesses to help make business decisions. From these data warehouses you can develop an Analysis Services cube which allows for fast reporting. Creating reports in Reporting Services 2008 from a cube is very different from a normal SQL query. We are going to…

Read more

3 comments, 1,870 reads

Posted in Mike Davis SQL on 8 July 2013

Create a Named Set with all but one Member in MDX – SSAS

I was trying to create a Named set in SSAS 2008 using the MDX calculations. I wanted to include all of the members of a hierarchy except one of them. I started with trying to use the filter expression and this worked but was very cumbersome to get the syntax…

Read more

1 comments, 928 reads

Posted in Mike Davis SQL on 1 July 2013

Comparing Previous Rows in SQL

I had a client recently that needed me to get the direction a student’s GPA was headed on a regular basis.

To do this I needed to get the last GPA record on the table and the previous GPA. The dates on these rows were different for different schools and…

Read more

0 comments, 482 reads

Posted in Mike Davis SQL on 24 June 2013

Pivoting weird data in SSIS – Sort of…

Sometimes we get data in some bad forms. For example look at the below table:

The names and the amounts are comma separated in two different columns. Let’s imagine we need to get this data into a table like the one below:

Notice the column names are the names that…

Read more

0 comments, 1,527 reads

Posted in Mike Davis SQL on 17 June 2013

SSRS – Using a Parameter to make Dynamic Columns

Multiple value parameters are a common tools used in SSRS. You can use this tool to select which columns actually show on a report. You will need to create a multiple value parameter and place an expression on each column on the report. More specifically the expression needs to be…

Read more

5 comments, 2,931 reads

Posted in Mike Davis SQL on 10 June 2013

Newer posts

Older posts