SSIS Dynamic Connection String – DelayValidation

Posted on June 21, 2012 by mdkabir

When I develop SSIS packages, I set the connect managers’ connection strings in one of the following ways:

Package Variables reading from an XML config file
Environment Variables
Package Variables whose values are passed from a SQL job invoking the package

I had a set of SSIS packages running for my client using the third option listed above. The packages worked fine for ages until one fine day when they failed. The logs showed the packages had failed validation and I discovered that all the packages had their connection managers’ DelayValidation property set to False. The variable used to set the connection string had a default value pointing to the DEV server. These packages in production were actually trying to validate against DEV database though the connection string was dynamically set via a variable to point to PROD. This was dangerous as the jobs will not run if DEV server was down, which is exactly what had occurred.

Whenever you have a connection string set dynamically using variables, the best practise is to set the DelayValidation property to True for all the connection managers and all the Data Flow Tasks. I found the following blog article to be useful with some quick handy tips.

http://www.louisnguyen.info/Home/ssis-best-practices

Hide-and-Seek with expressions in SSRS Reports

Posted on June 13, 2012 by mdkabir

This will be a quickie as I’m posting from my tablet for the first time on this site and it is just before going to bed. It’s about an issue I came across with an SSRS report this evening. It was a perfectly running report in production for over a year. The report’s layout has a dashboard-like feel with a summarised tablix of budgets and expenses representing my client’s business expenditure. The highlight of the summary was a Gauge Panel to show graphical representation of the data in every row. I had to modify the dataset dsExpenses ( which contained the results of an MDX query) to reflect the changes I had made to the cube from which the report was generated. I sucessfully ran the new dataset’s MDX query from the dataset’s query designer and changed all references in the report’s tablix cells to use the new fields from the newly modified dataset dsExpenses. One of the fields that was changed in dsExpenses was Payment_Amount which is now referred to as Payment_Amount_Programs.

I changed all references in the summary tablix from Payment_Amount to Payment_Amount_Programs manually by checking on each cell’s properties. I avoided modifying the report’s code as there were other tables that had other datasets with the same field name Payment_Amount. After carefully checking on each cell’s properties and values, I ran a quick build of the report and to my surprise the Gauge Panel’s background colour expression was thrown on the error report as it still had references to the old Payment_Amount. I knew, for sure, that I had checked the background colour expression for any outdated references. But when I checked the report’s xml code, I found that there was an expression hiding under the gauge data series’ back colour. I changed the expression to refer to Payment_Amount_Programs instead of Payment_Amount and the report ran successfully. This was a good lesson learnt the hard way to always check for expressions hiding in the code holding old references after a dataset is modified.

Posted to The No-Brainer Lab using Android

BIDS Helper to the rescue (SSAS cubes)

Posted on June 12, 2012 by mdkabir

Most often we think that the cubes we design and implement will process without any issues when the corresponding dimensions and facts have sensible data. This, however, is only a myth. I learnt this the hard way when I modified an existing SSAS cube which had some changes to the dimension attributes. The processing of the cube failed due to a mismatch of an attribute’s data type between a dimension in the data source view (DSV) and the dimension in the cube. That’s when BIDS Helper came to my rescue. I quickly installed BIDS Helper from here and ran its inbuilt Dimension Health Check feature to discover the hidden truth. I found BIDS Helper to be extremely useful to perform health checks on measures as well.

As soon as I run this feature on my measure group, it points out that the data type of measures contained within the group can be changed for optimised results.