SSIS #111 – Looking for Conditional IIF() in SSIS Expression?

There is no IIF statement in SSIS scripting functions

If you are looking for IIF() for SSIS expressions, you’ll be disappointed.

Most of us are familiar with the IIF statement in SSRS expressions (or in many other scripting languages).

But in SSIS, any Program Flow functions are unmistakably missing. Here are all the function categories you can see in the Expression Builder in SSIS. Program Flow is not one of them.

We can achieve IIF() using ( Condition? Value_when_true  : Value_when_false)

There is a very good explanation for the missing IIF statement in SSIS. Before I go too far on this topic, I want to give you the good news first. The Integration Services did give us a tool to accomplish the same function as the IIF statement does, only in different disguise.

Here is what we can use:

( Condition? Value_when_true  : Value_when_false)

You can write any acceptable expression in the Condition part, but it’ll only make sense for the expression to include at least one variable in order to achieve the goal of dynamic as you set out to achieve with IIF in the first place.

The value_when_true and value_when_false part can obviously include variables too.

Here is an example. For a user variable varSourceServerPrefix, I want to set it to an alia name of the linked server in our development environment, but set it to blank in the production server.

( @[User::varProduction] == 0? @[User::varLinkedServer]  : "")

Processing flows are largely controlled by the Precedence Constraints

There is an even better reason why Integration Services are missing the entire category of Program Flow Functions.

In Integration Services, processing flows are largely controlled by the Precedence Constraints between tasks. These Precedence Constraints are almost completely controlled by conditions that are expressed using variables, while variables in Integration Services can be manipulated by Script Tasks.

For examples how variables can be manipulated by Script Tasks and example of Precedence Constraints:

• SSIS #109 – Wait for data with For Loop Container
• SSIS #96 – Everybody Needs Integration Services Expression Reference

Note: the parentheses are not really required. I don’t think the parentheses are required even in nested conditional expressions. It certainly doesn’t hurt to have the parentheses in nested conditional expressions for better readability.

Here is the MSDN link on this: http://msdn.microsoft.com/en-us/library/ms141680.aspx

SSRS #71 – Cannot Format Expressions, Format Placeholders Instead

Placeholder is a perfect solution for formatting substrings inside a textbox

Formatting substrings inside a textbox in SSRS 2008 is no longer a problem.

In the following example, one textbox can hold both the bolded and underlined title “Report Definition”, and also the unformatted text.  

What if the actual report definition text is dynamically set in my report (say it comes from a dataset, a parameter, or a variable), and I still want to keep only the title bold and underlined, and leave the definition text unformatted?

Placeholder in SSRS 2008 and above is a perfect solution for the scenario.

In this example, I’ve decided to place three table fields into one column, having a bold and underlined title for each field, and leaving the texts unformatted.   

I’ve tried creating an expression that combines all three fields together, which has lead me nowhere in terms of formatting sub strings inside the expression.

Get to the Placeholder Menu

Formatting placeholders are pretty easy. That is, if you know how to access the placeholder feature. I find that there are many wonderful and very useful implementations in SSRS 2008 and above, compared to SSRS 2005. But Microsoft has decided to hide them from developers. One example is the Advanced Mode, with which we can achieve features such as fixing data on pages, or repeating data on every page etc. But accessing the Advanced Mode is not immediately obvious. Placeholder is another very useful feature, but not immediately obvious either.

To create placeholders inside a standalone textbox, click inside the textbox once to make sure that the cursor is placed inside the textbox, and then do a right click. The “Create Placeholder…” menu should be available.

  

To create placeholders inside a textbox in a Tablix or Matrix, you need to click inside the textbox twice to make sure that the cursor is placed inside the textbox, and then do a right click.

To edit a placeholder inside a textbox (standalone or not) , you need to highlight the placeholder by clicking on the placeholder once (your cursor needs to be inside the textbox first), and then do a right click. The “Placeholder Properties….” menu should be available.

Formatting substrings is one application of the placeholders. Do you have other application examples?

MDX #9 – Capture MDX Queries with SQL Server Profiler

Very often, we are asked by our business users to explain certain behaviors of PivotTable in Excel. Our first instinct is to capture the MDX queries fired from Excel. Once I have  the actual MDX query, I can analyze the query and the result, and will be able to provide our business users with a more in-depth explanation.

Capture MDX Queries with SQL Server Profiler

There are many very helpful blogs that explain simple steps to do this. As a SQL developer, using SQL Server Profiler can be a natural choice.

Use SQL Server Profiler to capture MDX queries fired from Excel

1. Open SQL Server Profiler from Microsoft SQL Server –> Performance Tools –> SQL Server Profiler.

2. Create a new trace with Analysis Services as Server Type. Enter your Analysis Services server name. Click Connect to make connection to the Analysis Services server.

3. No need to use the Standard template. Choose the Blank template.

 

4. Only select Query End as the Event to capture. Click the Run button at the page bottom. Now the trace is running and ready to capture any queries sent to the Analysis Services server.

5. Fire up the MDX queries from Excel by Refresh the PivotTable. You should see results similar to the following in the SQL Server Profiler. The MDX queries fired from Excel should be captured by the Profiler and displayed at the bottom of the trace window.

Two other ways to capture MDX queries fired from Excel

This blog will be incomplete without mentioning the following two common ways:

1. Use the OLAP PivotTable Extensions. This is an Open Source CodePlex project, and you can download it from http://olappivottableextend.codeplex.com/. Once you installed the Excel add-in, you can right-click a cell in a PivotTable and will notice a menu item “OLAP PivotTable Extensions…”.

The MDX tab will display the MDX query. The option to format the query is also very nice.

You might not want to recommend this If your company doesn’t allow third-party components.

2. Another option is to write some VBA code. Check out Marco Russo’s blog Display the MDX query of an Excel 2007 PivotTable at http://sqlblog.com/blogs/marco_russo/archive/2007/01/18/display-the-mdx-query-of-an-excel-2007-pivottable.aspx.

SQL Server Profiler can be used to capture MDX queries fired from any OLAP application

SQL Server Profiler can capture not only MDX queries fired from any OLAP application, but also XMLA commands sent from applications such as BIDS.