Archive

Posts Tagged ‘default value’

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.

image

  • 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.

image

  • 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.

image

 

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

Before the Execute SQL Task…

image

After the Execute SQL Task…

image

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.

image

Before the Execute SQL Task…

image

After the Execute SQL Task…

image

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

Note

  • All screenshots are from Integration Services 2012.

SSRS #52 – Setting Dynamic Default Parameters in MDX Dataset

May 30, 2011 Leave a comment

In my previous blog post, SSAS #21 – Steps to create a SSRS report with MDX accessing a Cube, I blogged the out-of-box features in SSRS to create a report with parameters accessing an Analysis Services database.

5 report parameters were automatically created.

When I preview the report, the Year Number shows the following available values, which include 0 (zero).

image

Goal

There are two things I’d like to change for the Year Number parameter.

  1. Remove the value 0 (zero) from the available list.
  2. Set a dynamic default value to the current year. 
    Here is what I’d like to achieve;

image

 

Step 1 – Show Hidden Datasets

In my BIDS, the default is to hide all the MDX datasets that are automatically created. To show all the MDS datasets, right-click on the Analysis Services data source, and select Show Hidden Datasets (make sure the check mark is showing).

Now, all 5 MDX datasets are showing under the Analysis Services data source.

image

Step 2 – Add a Filter function for Year Number.

In order to remove the 0 from the list, I will need to use the Filter function.

To save some typing, I copied the automatically created MDX query to SSMS, and modified it with a Filter function.

image

This is the final MDX query with a Filter function.

image

In the Query Designer, I pasted my new MDX query, and tested it.

image

Step 3 – Set a dynamic default value to parameter Year Number

I’ll achieve this with Reporting Services expressions. However, with MDX datasets, I’ll need to use a combination of regular RS expressions and MDX expressions.

First, get to the Report Parameter Properties window.

image

On the Report Parameter Properties window, go to the Default Values tab. Check “Specify values”. Then click Add, and the function button image .

image

In the Expression editor, type this:

="[DimDate].[Year Number].&["+CSTR(Year(Today))+"]"

image

You will need to replace [DimDate] with your own date/time dimension. [Year Number] is the one of the attributes in [DimDate]. You will need to replace it with your own too.

However, the Today function, the Year function, and the CSTR function are all regular RS functions.

You are done. Now preview your report, you will see that value 0 is removed from the available list, and 2011 is provided as the default value for parameter Year Number.

%d bloggers like this: