Archive for the ‘BI’ Category

I use this script all the time when setting up an SSIS package. (Unfortunately, I can’t remember where I found the original code. I’ve adapted it slightly, so if anyone recognises the original then let me know and I’ll link to it.)

The Problem

When setting up a data flow in SSIS the data transfer speed can be very slow because the default settings in the package have not been optimised.

The Solution

SSIS Properties

The code below will show you each table in the database. I take the column MaxBufferSize and round it down to the nearest hundred – so 87235 becomes 87000. I use this value as the DefaultBufferMaxRows value. I change the DefaultBufferSize from 10485760 to 104857600 (same number but add a zero to the end). Finally, I’ll add values to the BlobTempStoragePath and BufferTempStoragePath, normally I’ll use C:\temp, but make sure the directory exists and you’re probably better choosing a value not on the C drive.

SELECT s.[name] + '.' + t.[name] as TableName, SUM (max_length) as [row_length], 10485760/ SUM (max_length) as MaxBufferSize
FROM sys.tables as t
JOIN sys.columns as c
ON t.object_id=c.object_id
JOIN sys.schemas s
ON t.schema_id=s.schema_id
GROUP BY s.[name], t.[name];

These changes will allow SSIS to load more rows simultaneously and so should speed up your loading. I tend to use OLD connection for Source and Destination.


When importing Excel documents in SSIS on a 64 bit machine you may will encounter this error:

[SSIS.Pipeline] Error: component “Excel Source” (35) failed validation and returned error code 0xC020801C.

It may also tell you that it failed to acquire a connection.

This is because there is no 64bit driver for Access or Excel – with csv files you can use the Flat File connector.

To carry on working you need to set your project up to run in 32 bit mode. Right click on the project properties and go to debugging. Change the value in run64BitRuntime to False.

64bit properties

Using Parameters in SSRS

Posted: December 1, 2011 in BI, Parameters, SSRS

Sometimes you’ll write a report and you’ll want to use parameter drop-downs. Actually it’s not sometimes, it most of the time. Most people requesting reports ask for some form of parameter, either dates or a data driven value. This is all easy stuff, all you need to do is create a new dataset and associate the parameter with that. You then change the main dataset to filter data based on the parameter value. But how do you deal with the All records value?

This is how I do it.
In this example I am going to draw data from a table called chess players – because I am chess geek.
This is a basic parameter dataset. I’m bringing back a list of world chess champions – and I want a distinct set – no duplicates. In this fictitious table world champions includes ex-champs.
Select Distinct ChessPlayerName as value, ChessPlayerName as label
From ChessPlayers
Where WorldChampion = ‘True’;
My main dataset will now look like this (It’s a list of tournaments where the winner was a world champion:

Select ChessTorunaments, TourneyYear, Victor
From ChessMatches
Where Victor = @WorldChampion;

That example is fine if you always want to filter, but I don’t. I want to be able to list all tournaments regardless of the winner. And so, I will change my query.

SELECT null as Value, ‘All’ as label
Select Distinct ChessPlayerName as value, ChessPlayerName as label
From ChessPlayers
Where WorldChampion = ‘True’
Order by value;

Order by value will ensure that the null value appears at the top of the list.
I will now change my parameter values to allow nulls.
Now I need to change my main dataset.
Select ChessTorunaments, TourneyYear, Victor
From ChessMatches
Where Victor = IsNull(@WorldChampion, Victor);

So, if the @WorldChampion parameter is null it will be replaced by the Victor column value. As the Victor column value will always match the victor column values we will always get the full record set, won’t we? Hey? Well, not always. Victor = Victor will only work when Victor is not null. If the column Victor allows for nulls, and has nulls in it, it will not match. Why? Because null means unknown – so null = null will return false because you can’t compare two unknown values. What you’ll need to do if Victor (or your column) is nullable is use IsNull again:
Where IsNull(Victor,0) = IsNull(@WorldChampion, IsNull(Victor,0));
Now you won’t get null = null you’ll get 0=0 which is true.

The Problem

A data import failed with the following error messages:

  • Failed to retrieve long data for column “Description”.
  • There was an error with output “OLE DB Source Output” (742) on component “OLE DB Source” (732). The column status returned was: “DBSTATUS_UNAVAILABLE”.
  • SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The “output “OLE DB Source Output” (742)” failed because error code 0xC0209071 occurred, and the error row disposition on “component “OLE DB Source” (732)” specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
  • SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component “OLE DB Source” (732) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

The column description is of the data type VarChar(Max). This error will normally occur when the data type is some form of BLOB

The Solution

When SSIS Imports BLOB data it writes the data our to a file and then imports the file. By default it will use the system temp directory for this. However you can set two directory locations for Blob data and system cache. It is recommended that you use fast disks and that these two directories are on separate disks.

Click on the Data Flow Task and view the properties. You need to enter values for two options: SSISBlobTempStoragePath and BufferTempStoragePath. I created directories with those names on the E and L drives.

Blob Path Config Setting

After making this change the data task ran in 5 minutes rather than 8. More importantly it shouldn’t fail in the future

The Problem

In reporting services a new report is created. The report region/Language is set to English(UK). A date parameter is created.
When the report is run the date parameters are showing up in mm/dd/yyyy format 

The Fix

A parameter will take a label and a value field.
Change your query to have a label formatted in the way you like and the value left raw:
Select Right(‘0′ + Cast(Day(dateRange) as Varchar(2)),2) +’-‘+ Right(‘0’ + Cast(Month(dateRange)as Varchar(2)),2) + ‘-‘+ cast(Year(dateRange) as char(4)) as label, dateRange as value
FROM dbo.vwDateRange
Order by dateRange desc 

Where daterange is a smalldatetime 

Set the report parameters to display label as the label and value as value

This assumes a user does not have Content Manager as their SSRS role and SSRS is set up more or less as it comes out of the box.

The Problem

If a user hits subscribe on a report then the chooses email as the delivery option they will see that the To field has been pre-populated with their windows login. They will not have the permissions need to change this setting. This is fine if you are running Exchange as it will resolve the login against the Active Directory and work out the real email address.

If you are not running Exchange it is not so fine.

The fix

You need to make a change to the rsreportserver.config file.

This should be under C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer by default – if you installed SQL somewhere else then the first part of this path will be wrong.

Open up this file and look for a value <sendemailtouseralias> under <rsemaildpconfiguration>. If this is set to True, as it is by default, you will get the problem above. If it is set to False the To field will be blank and the user can enter an email address.