Check IsNumeric() with Derived Column Transform in SSIS Package

At some point or another, you’ve probably run into the road block that is the lack of an ISNUMERIC() equivalent within the SSIS expression language. While you can’t use ISNUMERIC() in an SSIS transform, such as a Conditional Split Transform or a Derived Column Transform, that doesn’t mean you can’t check to see if a field is numeric using the SSIS expression language (If you feel so inclined, you can use a Script Task to check if a field is numeric. Tim Murphy, another Pragmatic Works consultant, covers that in his blog here).

As I said before, there is a way we can use a Derived Column Transform (or Conditional Split) to check if a field is numeric. After dragging in a Derived Column Transform into your Data Flow Task, create a new column to be added as a new column to your data flow. Give it a meaningful name and use this expression:

(DT_I4)CheckForNumeric == (DT_I4)CheckForNumeric ? 1 : 0

check for numeric with derived column

Then near the bottom of the Derived Column Transform Editor window, click Configure Error Output. You need to tell SSIS to Ignore failure on Error, as seen here:

Ignore failure

Optionally, you could choose to redirect rows that are not numeric to the Error output of the Derived Column and then handle those rows there.

Now when we run the Data Flow Task, we should see results like this:

IsNumeric results with Derived Column

You can see that the rows that are not numeric have a NULL value for the IsNumeric field we created with the Derived Column. Rows that are numeric have a 1. This way it is easy for us to determine which rows of a certain field are numeric and which are not numeric by checking for NULLs in our field called IsNumeric.

Creating Stepped Reports with SSRS

One of the nice things about a stepped report is that the child groups are displayed beneath the parent groups but within the same column as the parent group. In the next several steps, I’m going to walk through creating a basic stepped report using the Tablix data region. I used the Matrix data region template for this example.

First I created a report and dragged in a Matrix data region into the design area. I used the Adventure Works Cube and a simple data set with the Product hierarchy, Reseller Sales Amount and Calendar Year. Here is the query I used for the main dataset in case you would like to follow along.


I put Product in the row group, Calendar Year on the column group, and Reseller Sales Amount in the details.


Next we need to create a parent group for to display our Subcategory. To create a parent group, in the Row Groups pane, right-click the Product group, select Add Group, then select Parent Group.


Select Subcategory as the field you want to group by. Make sure to check the Add group header check box.


After creating the new group for Subcategory, right-click the Subcategory group in the Row Groups pane, and create a new parent group for the Category field. Again, check the box next to Add group header.

At this point, you matrix should look like this.

SSRS Stepped report layout

But since we are creating a stepped style report, we want our parent groups in the same column as our detail group. So we can delete the columns that contain Category and Subcategory.

Customize your SSRS stepped report layout

Then add Category and Subcategory to the rows above Product so that your Matrix looks like what you see below. Also, don’t forget to add Reseller Sales Amount in the cells next to Category and Subcategory.

Consolidate the groups in your SSRS stepped report

Now we’re almost done. To create the stepped formatting, right-click the cell containing Subcategory and select Text Box properties. In the Alignment pane under Padding options, set the Left padding to 15pt. Then click OK.

Create dynamic padding for your SSRS stepped report

Then right-click the cell containing the Product field and click Text Box properties. This time in the Alignment pane, set the Left Padding to 30pt.

Modify the SSRS stepped report group properties

The last thing we have to do is create that nice, fancy, collapsible drill-down action. To do that, right click the Product row group in the Row Groups pane and navigate to the Visibility pane. When the report is initially run, we want the Product row group set to Hide. Check the check box next to Display can be toggled by this report item. Since we want the Product row group to be toggled by its parent group, select the Subcategory text box in the drop down list.

Modify the SSRS stepped report group properties

We also should set the visibility settings of the Subcategory row group so that it is also hid when the report is initially run. It should be toggled by the Category row group.

And after adding some nifty back ground colors to make our report a little easier to read and some bold text on the header row, you are done! We now have a simple, yet easy to read, stepped report that will allow the end user to drill down into the child group rows to view the data at a more granular level.

The final Reporting Services SSRS stepped report example

Recording Available for Reporting on a Cube with SSRS 2008 Webinar

Thanks to all of the attendees that showed up for my webinar yesterday. I had a great time speaking and I hope everyone enjoyed the webinar and maybe even learned something.

If you missed the webinar, Reporting on a Cube with SSRS 2008, have no fear! There is a recording available for you to watch. Just head over to the Pragmatic Works CMS site and create a free account. Not only will you be able to watch my webinar from yesterday, you’ll also be able to check out tons of other great webinars from some really sharp speakers. So head over to the Pragmatic Works CMS site, create a free account, and watch my webinar here.

Free Webinar Tomorrow (7/21) @ 11 am EST: Reporting on a Cube with SSRS 2008

Tomorrow (7/21) I’ll be speaking on reporting on an SSAS cube with SSRS 2008 at 11 a.m. EST. It’s a completely free event hosted by Pragmatic Works.

In this session I’ll give you an introduction to reporting on a cube with SQL Server Reporting Services (SSRS). You’ll learn about some of the advantages of using your cube as a source for your SSRS reports, how to build reports based on your cube with minimal knowledge of MDX, as well as a few tricks you can use to increase the usability of your reports.

It’s completely free to attend and you can do it during your lunch break. I’ll also be taking questions so don’t forget to get registered!

Encrypting Sensitive Information in SSIS Package Configurations using BI xPress

One of the downfalls to SSIS package configurations is that it does not natively allow you to encrypt your sensitive information, such as a connection string or a variable, that you may be configuring with an outside package configuration. There are some work-arounds that you can impliment that will allow you to automatically encrypt and decrypt stored passwords, but its not pretty. The good news is that now, with BI xPress, SSIS developers can easily and automatically encrypt any configured property of an SSIS package. So lets walk through using the BI xPress Package Configuration Organizer and look at a couple of the different options we have when encrypting an SSIS package configurations.

There’s a couple ways you can access the BI xPress Package Configuration Organizer. Just right-click on some empty space in your Control Flow design area and select Package Configurations (BI xPress).


You can also click the BI xPress menu at the top of BIDS and then select Package Configurations (BI xPress).


This should open the BI xPress SSIS Configuration Wizard. This is the wizard that will allow you to select which package properties you would like to configure and encrypt. Make sure you check the box to Enable package configurations.


On the next screen of the wizard, you’ll need to set a few options. The first is to decide what type of Configuration you want to use. You can choose from several options seen below, but I’ll be using an XML configuration file.


After you specify the Configuration Name, click Next.

On the next screen specify which property you want to configure/encrypt. At the top of the window, I can select an encryption mode, I can choose to encrypt only the sensitive data or the full value of the property. In this example, I’m encrypting the connection string for the connection manager OLEDB_SSISOPS_1, so I’ve checked the check box next to the ConnectionString property under OLEDB_SSISOPS_1. We can also select an algorithm type. I’m using the Advanced algorithm to encrypt the connection string. Using these settings, the entire connection string for this connection will be stored in the configuration file in an encrypted format.



Click Next and on the next screen click Start to generate the configuration file. Once the wizard is finished a summary screen will appear. Click Finish to close the wizard.

And you’re done! Here is a screen shot of the configuration file. You can clearly see that connection string is encrypted and unreadable.


Finally, it’s a piece of cake to encrypt sensitive information stored in package configurations!

The Best SQL Documentation Tool on the Market

If you read my blog you know from time to time I will blog about an exceptional third party SQL tool. Previously I’ve blogged about Task Factory and BI xPress, two amazing SSIS development tools. Well this week I figured I would talk about a killer SQL documentation tool called BI Documenter.


BI Documenter allows you to quickly and easily document your entire BI environment. So unlike the other popular tools out there, BI Documenter will document SQL Server databases (2000, 2005, 2008 & R2), SSIS packages, SSAS cubes, and SSRS reports. Because all of these objects can be documented in a single snap shot, you are then able to easily perform impact analysis across all objects, which has a pretty slick UI and is very intuitive. You can also do snapshot comparison across your whole stack, so you can see how any object in a snapshot differs from a previous snapshot.


One other thing to mention about documenting SSIS packages is that BI Documenter is the only documentation tool that not only documents the SSIS packages textually, but also visually!


This means that after you document you SSIS packages, you can view the SSIS packages (Including the Data Flow Tasks!) just like you would in BIDS. Very cool!

BI Documenter is a pretty cool documentation tool that is very well rounded and since it’s the only tool on the market that can document the entire BI stack all together, its basically you’re only choice if you want to do impact analysis across the stack. BI Documenter also has a lot of awesome features coming up in the next release such as documenting server level objects, like SQL Agent Jobs, Linked Servers, Backup Locations, and others.

If you’re in the market for a rock solid documentation tool with the kind of support you can count on, I’d highly recommend that you check out BI Documenter and download the free trial. It’s at the very least worth a look.


MDX Aggregate Functions Can Not Be Used on Calculated Members in the Measures Dimension

If you’ve ever tried to use the Aggregate function on a calculated measure, you’ve seen the following error:

Aggregate functions error

This is a problem if you’re trying to calculate something like Year To Date for a calculated measure. Take the following MDX query (which I understand doesn’t make sense and isn’t something you’d do in a real world situation, but just go with it for the sake of the example), which can be run against the Adventure Works cube, for example:

WITH MEMBER [Measures].[YTD Average Price] AS Aggregate ( PeriodsToDate( [Date].[Calendar].[Calendar Year], [Date].[Calendar].CurrentMember) , [Measures].[Internet Average Unit Price] ) Select [Measures].[YTD Average Price] on0, [Date].[Calendar].[Date] on1 From [Adventure Works] Where [Date].[Calendar Year].&[2003]


If you execute this query, it will complete successfully with #Error as the results. You have a couple different options. First, you could replace the Aggregate function with the Sum function. If you are unable to use the Sum function, then you’ll have to  adjust the way you calculate Year to Date Average Price. We’ll have to break this up into a couple different calculations.

WITH MEMBER [Measures].[YTD Unit Price] AS Aggregate ( PeriodsToDate( [Date].[Calendar].[Calendar Year], [Date].[Calendar].CurrentMember) , [Measures].[Internet Unit Price] ) MEMBER [Measures].[YTD Transaction Count] AS Aggregate ( PeriodsToDate( [Date].[Calendar].[Calendar Year], [Date].[Calendar].CurrentMember) , [Measures].[Internet Transaction Count] ) MEMBER [Measures].[YTD Average Price] AS [Measures].[YTD Unit Price]/[Measures].[YTD Transaction Count], format=CurrencySelect [Measures].[YTD Average Price] on0, [Date].[Calendar].[Date] on1 From [Adventure Works] Where [Date].[Calendar Year].&[2003]


Doing our calculation this way, we are able to work around our inability to use the Aggregate function on a calculated member. I hope this gives someone an idea of how they can work around this annoying issue.

Dustin Ryan is a Data Platform Solutions Architect with Microsoft interested in Azure, SQL Server, and Power BI.

%d bloggers like this: