Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

«November 2015»

DirectQuery in Power BI Desktop

In the latest Power BI Desktop a new Preview features was released that now allows you to connect using DirectQuery to either SQL Server or Azure SQL Databases.  DirectQuery is a really neat feature that allows you to point to the live version of the data source rather than importing the data into a data model in Power BI Desktop. 

Normally when you want to get an updated dataset in the Power BI Desktop you would have to manually click the refresh button (this can be automated in the Power BI Service), which would initiate a full reimport of your data.  This refresh could take a variable amount of time depending on how much data your have.  For instance, if you’re refreshing a very large table you may be waiting quite a while to see the newly added data. 

With DirectQuery data imports are not required because you’re always looking at a live version of the data.  Let me show you how it works!

Turning on the DirectQuery Preview

Now, because DirectQuery is still in Preview you must first activate the feature by navigating to File->Options and settings->Options->Preview Features then check DirectQuery for SQL Server and Azure SQL Database


Once you click OK you may be prompted to restart the Power BI Desktop to utilize the feature.

Using DirectQuery in Power BI Desktop

Next make a connection either to an On-Premises SQL Server or Azure SQL database.

Go to the Home ribbon and select Get Data then SQL Server.


Provide your Server and Database names then click OK. ***Do not use a SQL statement.  It is not currently supported with DirectQuery***


From the Navigator pane choose the table(s) you would like to use.  I’m just going to pick the DimProduct table for this example and then click Load.  You could select Edit and that would launch the Query Editor where you could manipulate the extract.  This would allow you to add any business rules needed to the data before visualizing it.


Next you will be prompted to select what you want to connect to the data. Again, Import means the data

Read more

The Big Data Blog Series

Over the last few years I’ve been speaking a lot on the subject of Big Data. I started by giving an intermediate session called “Show Me Whatcha’ Workin’ With”. This session was designed for people who had attended a one hour introductory session that showed you how to load data, to look at possible applications … Continue reading The Big Data Blog Series
Read more

SSIS For Loop Containers Part 1

  • 4 April 2012
  • Author: Tom Lannen
  • Number of views: 93409

In a previous blog I talked about the Sequence Container, and some of its uses.  To continue with that theme I now bring you the For Loop Container. 

The For Loop is one of two Loop containers available in SSIS.  In my opinion it is easier to set up and use than the For Each Loop, but it is just as useful.  The basic Function of the for loop is to loop over whatever tasks you put inside the container a predetermined number of times, or until a condition is met. The For Loop Container, as is true of all the containers in SSIS, supports transactions by setting the Transaction Option in the properties pane of the container to ?Required?, or ?Supported? if a parent container, or the package itself is set to ?Required?

There are three expressions that control the number of times the
loop executes in the For Loop container.

  1. The InitExpression is the first expression to be evaluated on the For Loop and is only evaluated once at the beginning. This expression is optional in the For Loop Container.  It is evaluated before any work is done inside the loop.  Typically you use it to set the initial value for the variable that will be used in the other expressions in the For Loop Container. You can also use it to initialize a variable that might be used in the workflow of the loop.
  2. The EvalExpression is the second expression evaluated when the loop first starts. This expression is not optional. It is also evaluated before any work is performed inside the container, and then evaluated at the beginning of each loop.  This is the expression that determines if the loop continues or terminates. If the expression entered evaluates to TRUE, the loop executes again. If it evaluates to FALSE, the loop ends.  Make sure to pay particular attention to this expression.  I will admit that I have accidentally written an expression in the EvalExpression that evaluates to False right away and terminated the loop before any work was done, and it took me longer than it probably should have to figure out that the EvalExpression was the reason why it was wrong.
  3. The AssignExpression is the last expression used in the For Loop. It is used to change the value of the variable used in the EvalExpression. This expression is evaluated for each pass through the loop as well, but at the end of the workflow. This expression is optional.


Lets walk through setting up an example of the package. In this example we?ll create a loop that executes a given number of times.

Create a new package and add two variables to it, intStartVal and intEndVal.


Next add a For Loop Container to the package and open the editor.  Assign the following values for the expressions:


That is all the configuring that is required for the For Loop Container.  Now lets add a Script Task that will display a message box with the value of the intStartVal variable as the loop updates the value of that variable. Here is the code to do that:

Public Sub Main()

Dts.TaskResult = ScriptResults.Success
End Sub

Once that is done the package is ready to execute.


First Iteration


Second Iteration


Fifth Iteration




Now that the EvalExpression @intStartVal <= @intEndVal evaluated to false the package ends.  In Part 2 of SSIS For Loop Containers I?ll go a little deeper in using a For Loop with some real world examples. Until then?

Categories: Blogs
Rate this article:

Please login or register to post comments.