BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
Knowing how to write DAX queries is one thing, but where are you going to use them? This blog shows how you can integrate DAX queries into SSMS, SSRS, SSIS, Excel and PowerPivot.
- Using DAX queries in SQL Server and other applications
- Running DAX queries within Management Studio
- Basing SSRS (Reporting Services) reports on DAX queries
- Writing DAX queries in Integration Services (SSIS) (this blog)
- Retrieving data into Excel using DAX queries
- Loading data into PowerPivot data models using DAX queries
This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.
Posted by Andy Brown on 18 February 2016
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Writing DAX queries in Integration Services (SSIS)
This page shows how to import data using SQL Server Integration Services (SSIS) and DAX queries.
As for SSRS, this page assumes a good level of familiarity with SSIS. If you're a newbie, you could follow my YouTube tutorial or enrol on our two-day introduction to SSIS course.
Creating a data source and connection
The first thing to do is to add a data source into a data flow task:

Add an OLEDB data source into a new data flow task within a new package.
Choose to create a new connection for this data source:

Click on this button when editing the source to add a connection.
Now choose to create a new connection:

Click on this button at the bottom right of the dialog box which appears, to add a new connection.
You can now choose the appropriate provider:

I've got SQL Server 2014 on my laptop, but I've chosen the provider for Analysis Services 11.0, which is the internal name for SQL Server 2012.
Now type in the server you want to connect to:

Type in the Analysis Services server name (yours will be different). The Location seems irrelevant for our purposes!
You should now be able to choose the model to which you want to connect:

Choose a tabular model from the drop list, ignoring the odd Initial catalog label to the left of it.
Typing or pasting in your query
You should now have a dialog box looking something like this:

You've created a connection; time now to choose how you want to use it.
Choose the data access mode as an SQL command (even though it isn't), and type or paste in your DAX query:

Choose SQL command, and type or paste in the query.
I've gone for the following query again:
EVALUATE
SUMMARIZE(
'Transaction',
Species[SpeciesName],
Quadrant[QuadrantName],
"Total sales",
SUM('Transaction'[Quantity])
)
Even though it's not SQL, the Parse Query button still works:

You can parse this query to check you've got the syntax right.
The Preview button confirms whether you've got the right data:
![]() |
![]() |
Click Preview... | ... to run the DAX query. |
Time now to move from using DAX in SQL Server to retrieving data into Excel.
- Using DAX queries in SQL Server and other applications
- Running DAX queries within Management Studio
- Basing SSRS (Reporting Services) reports on DAX queries
- Writing DAX queries in Integration Services (SSIS) (this blog)
- Retrieving data into Excel using DAX queries
- Loading data into PowerPivot data models using DAX queries