Archive for January, 2014

SSIS #116–What is the variable value at runtime?

January 29, 2014 1 comment

Using variables in SSIS packages can make your ETL process more dynamic and respond to different scenarios during runtime.

Not only we can use variables, we also have many different ways to set values for our variables. This is all good news for developers. But like many things in real life, when we have too many choices, we can get carried away and overcome by our enthusiasm. This is especially true in terms of variables in SSIS packages.

5 different ways to set values for variables

In general, we have the following 5 different ways to set values for variables. The question we need to ask is what is the order of precedence during runtime when we are using all these 5 different ways to set values for a variable.

  • In the Variable Editor in the Value field: although varCubeName is a String type, we do not need to use the double quotes to enclose the value Cube Name in Value.


  • In the Variable Expression Editor: variable values can also be set in the Expression field. We must enclose the value in double quotes. This is true even for Integer type variable. Notice that the value in the Value field has changed from Cube Name in Value to Cube Name in Expression. Once I entered an expression, both the Value and the Expression field has become the same.


  • In the package configuration file: I always prefer an XML configuration file, in which I set varCubeName to a value of Cube Name in Package Configuration.
  • image

  • In many control flow tasks, such as an Execute SQL Task, and some data flow transformation tasks. for the purpose of this blog, I picked the Execute SQL Task to set the value for varCubeName to Cube Name from Execute SQL Task during runtime.
  • In Script Task, I can also modify the value of varCubeName. For the purpose of this blog, I set it to Cube Name in Script Task in a scrip task.
    To summarize, here are the values I set for the variable varCubeName using 5 different ways:

  1. Cube Name in Value: in the Variable Editor in the Value field
  2. Cube Name in Expression: in the Variable Editor in the Expression field
  3. Cube Name in Package Configuration: in the package configuration file
  4. Cube Name from Execute SQL Task: in the Execute SQL Task
  5. Cube Name in Script Task: in a scrip task

4 and 5 are essentially the same, so I’ll just test it one at a time. In my first test, I disabled the scrip task because I only want to test 1, 2, 3, and 4. I want to see what the value for my variable varCubeName really is at runtime. I enabled the package configuration so I can throw #3 in the midst.

I used two script tasks with the MessageBox function to show me the value at runtime, both before and after the Execute SQL Task.



“Cube Name in Expression” took precedence over all other values

Before the Execute SQL Task…


After the Execute SQL Task…


The value is always Cube Name in Expression. It’s like the Execute SQL Task had never even executed.

“Cube Name in Package Configuration” took precedence over Cube Name in Value, but gives way to “Cube Name from Execute SQL Task”

Now we know that Cube Name in Expression has the highest precedence order. Let’s remove it and only leave the Cube Name in Value, as shown in the following screenshot.


Before the Execute SQL Task…


After the Execute SQL Task…


This time we see that the Cube Name in Value which is set in the Variable Editor in the Value field never showed up. Cube Name in Package Configuration and Cube Name from Execute SQL Task both overwrite the Cube Name in Value.

Enabling the Scrip Task produces similar results, in which “Cube Name in Script Task” has the same precedence order as the “Cube Name from Execute SQL Task”.

Conclusion on the precedence order

  1. Cube Name in Expression: the value is set in the Variable Editor in the Expression field. The value set in this way has the highest precedence order and overwrites the values in all other ways.
  2. Cube Name from Execute SQL Task: the value is set in the Execute SQL Task. It has the second highest precedence order. Cube Name in Script Task has the same precedence order.
  3. Cube Name in Package Configuration: the value is set in the package configuration file. It can only overwrites the variable’s default value.
  4. Cube Name in Value: the value is set in the Variable Editor in the Value field. This value is usually called the default value, because it can be overwritten by all the proceeding ways during runtime.

Lessons learned

During the SSIS package deployment, your DBA decided to include all the variables along with all the connection strings as the property/value pairs in the package configuration files, and somehow your packages are not working the way you had expected.

During development, your packages are not working the way you would expect and you decided to debug your variables and found out that the variable value at runtime was incorrect.

The above two scenarios happened to me in the past and they have inspired this blog.

The lessons I’ve learned are:

  • During deployment, do not include variables property/value pair in the package configuration file. Variables should only be handled in the SSIS packages.
  • If variable expression is sufficient, use variable expression only.
  • If you need to combine different ways to set variable values at runtime, only use these two combinations.
    Default value +  Execute SQL Task, or

    Default value +  Script Task


  • All screenshots are from Integration Services 2012.

SSRS #73 – Marlon Ribunal and Mickey Stuewe’s New Book: Reporting Services 2012 Blueprints

January 24, 2014 Leave a comment

The book’s link is here, SQL Server 2012 Reporting Services Blueprints.

This book is a step-by-step, task-driven tutorial that goes straight to the practical development of reporting skills, explaining actions as they are taken. If you perform the role of report development using SSRS in your job and you already have a basic knowledge of how data source and datasets in SSRS work, this book will advance your reporting skill to the next level.

In order to accomplish all the steps discussed in this book, including the steps on how to integrate SharePoint 2010/2013, PowerPivot, and Power View with SSRS, it is recommended that you use SQL Server 2012 Enterprise or Business Intelligence Edition. You can still use the Standard Edition to accomplish most of the steps in the book but it doesn’t support the advanced BI features, such as PowerPivot and Power View.

To create SSRS 2012 reports, you have two options:

  • You can use the SQL Server Data Tools (SSDT). SSDT is Visual Studio shell which is an integrated environment used to build SQL Server database objects. You can install SSDT
    from the SQL Server installation media.
  • You can also use Visual Studio 2012. If you use Visual Studio, you must install the SSDTBI templates. SSDTBI (SQL Server Data Tools-Business Intelligence) is a component that contains templates for SQL Server Analysis Services (SSAS), SQL Server Integration Services (SSIS), and SQL Server Reporting Services (SSRS) for Visual Studio 2012.

To try the examples in this book, you will need to have 2012 AdventureWorks Sample database.

In Chapter one you will learn right away different tricks on how to implement parameters for  
different scenarios, ability to exclude one or more parameters at runtime, drop-down parameter, multivalued parameter, cascading parameter and cascading multivalued parameter.

I really appreciate Chapter 2 in which a section is devoted to create custom report template. Report templates not only save time but also creates visual consistency that suit your organization branding. Number crunching is really all about data summaries, aggregates, and groupings. Through practical examples in Chapter 2, you will be able to pick up the skills very quickly.

Another essential skills in creating SSRS reports is to be able to add interactive elements, such as Actions, Tool tips, Document Map, Sorting and Bookmark to a report. In SSRS, one report can also interact with another report through Subreport, Drillthrough report or linked report. You will absolutely love Chapter 3 which focuses on adding actions to SSRS reports. 

Have you ever wondered if SSRS is also a good tool for data visualization? In SSRS 2012, in the report designer toolbox, you will find a few report items that are new, they are, Data Bar, Sparkline, Indicator and Map. Chart and Gauge exist in the prior versions.

Chapter 4 will show you how to create reports in SSRS 2012 with data bars, sparklines, gauges, and indicators. Next time if you need to meet the common data visualization requirements, you will find that this book can make your data visualization work seem effortless.

I particularly like Chapter 5 which goes right into map visualization. It not only shows how to use the built-in maps for the United States, but also how to import and use commercial shapefiles (map files) to create world map report.

If you are tasked to develop reports based on the cubes in the OLAP, Chapter 6 will show you how to survive simple implementation just with the query designer in SSRS.

The last chapter in this book goes beyond just report development. It will show you how to get your SSRS reports ready for production. You will find practical information on deployment configurations, user security, shared schedules, cached reports, and snapshots. It also dives into report subscriptions with e-mail delivery / scheduled delivery and data-driven subscription.

Overall, this SSRS 2012 blueprints book is a well written book and worth buying if you just start out developing SSRS reports in 2012. If you started with the prior versions and already are an experienced report developer, then this book might not be right for you. However, if you are like me and new to the data/spatial visualization features in SSRS 2012, you will find very useful information in this book on how to use the data bars, sparklines, gauges, and indicators and map objects.

Congratulations to Marlon Ribunal and Mickey Stuewe on their first SSRS book. And to reviewers Basit A. Masood-Al-Farooq, Varinder Sandhu, SIVAKUMAR VELLINGIRI and Valentino Vranken. Their combined experience in SQL Server Reporting Services report development makes this book a valuable addition to every SSRS developer’s library.

2013 in review

January 1, 2014 Leave a comment

The stats helper monkeys prepared a 2013 annual report for this blog.

Here’s an excerpt:

The Louvre Museum has 8.5 million visitors per year. This blog was viewed about 120,000 times in 2013. If it were an exhibit at the Louvre Museum, it would take about 5 days for that many people to see it.

Click here to see the complete report.

MDX #36 – Many thanks to the reviewers

January 1, 2014 Leave a comment

In this blog, I’d like to give special thanks to the people below who have taken time to write reviews for my book MDX with SSAS 2012 Cookbook.

“It helped me a lot in my projects and helped me advanced my MDX skill in a very short time.”

hxy0135 NJ

“What I like about the cookbook style is the scenario and solution design. ”

Dan English

“The structure of the books is very similar and is again very easy to follow.”

Gerhard Brueckl

"This book is a must have. I have struggled trying to find a book with good illustrations and easy to follow samples UNTIL this book!"

Desert Fun

“I recommend this book to anyone regardless of MDX exposure, really great to have a reference like this book when you are trying to work through tough requirements."

richard mintz

"MDX with SSAS 2012 Cookbook" has a good chapter and topic organization which starts simply, but not too simply, and builds in complexity. "Elementary MDX" sets the stage for the expected level of knowledge with the other chapters building from that knowledge base. I particularly liked the chapters on "Working with Time", and "Business Analytics" because the regression and non-allocated expenses are tough problems for MDX newbies to solve."


In the blog below, I’ve given my thanks to Vincent Rainardi for his quick reviewing.

MDX #32 – Vincent Rainardi’s blog

%d bloggers like this: