Execute SQL Task Executes MDX Query More Than Once

Enter a topic name to show or a new topic name to create; then press Enter

.

Here's an interesting one discovered by Chris Webb whilst doing some work at a client site. Chris was curious as to why it is that executing the same MDX query in SSMS and the ExecuteSQL task in SSIS produced very different Profiler footprints.

Let's have a look.

Given a very simple MDX query of this

 SELECT 
         NON EMPTY [Store].[Geography].AllMembers ON 1,
         {[Measures].[Retail Amt],[Measures].[Return Qty]} on 0  
 FROM 
         [REAL Warehouse]

If we use Profiler against the AS Server we can see what happens

Now let's switch to the Execution graph in Profiler when the exact same query is done through the ExecuteSQL task and SSIS.

Different right?

Here are the contents of the Query Begin EventClass for the SSMS Executed Statement (Remember only one)

 SELECT 
        NON EMPTY [Store].[Geography].AllMembers ON 1,
        {[Measures].[Retail Amt],[Measures].[Return Qty]} on 0  
 FROM 
        [REAL Warehouse]


 <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis">
           <Catalog>REAL Warehouse Sample V6 MT</Catalog>
          <SspropInitAppName>Microsoft SQL Server Management Studio - Query</SspropInitAppName>
          <LocaleIdentifier>1033</LocaleIdentifier>
          <ClientProcessID>272</ClientProcessID>
          <Format>Native</Format>
          <AxisFormat>TupleFormat</AxisFormat>
          <Content>SchemaData</Content>
          <Timeout>0</Timeout>
        </PropertyList>

And here are the contents of each of the Query Begin EventClass for the SSIS Executed Statement ( 3 times)

1.

What I think is happening here is the task is retrieving Metadata for the query

 SELECT 
        NON EMPTY [Store].[Geography].AllMembers ON 1,
        {[Measures].[Retail Amt],[Measures].[Return Qty]} on 0  
 FROM 
        [REAL Warehouse]


 <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"
 xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><Catalog>REAL Warehouse Sample V6 MT</Catalog>
 <Timeout>0</Timeout><Content>Metadata</Content><Format>Tabular</Format>
 <DbpropMsmdFlattened2>false</DbpropMsmdFlattened2><ExecutionMode>Prepare</ExecutionMode>
 <LocaleIdentifier>1033</LocaleIdentifier></PropertyList>

2.

What I think is happening here is the task is retrieving the query format

 SELECT 
        NON EMPTY [Store].[Geography].AllMembers ON 1,
        {[Measures].[Retail Amt],[Measures].[Return Qty]} on 0  
 FROM 
        [REAL Warehouse]


 <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
 <Catalog>REAL Warehouse Sample V6 MT</Catalog><Timeout>0</Timeout><Format>Native</Format>
 <DbpropMsmdFlattened2>false</DbpropMsmdFlattened2><LocaleIdentifier>1033</LocaleIdentifier></PropertyList>

3.

And I think here the task is retrieving the data from the query

 SELECT 
        NON EMPTY [Store].[Geography].AllMembers ON 1,
        {[Measures].[Retail Amt],[Measures].[Return Qty]} on 0  
 FROM 
        [REAL Warehouse]


 <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"  xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
 <Catalog>REAL Warehouse Sample V6 MT</Catalog><Timeout>0</Timeout><Content>SchemaData</Content>
 <Format>Tabular</Format><DbpropMsmdFlattened2>false</DbpropMsmdFlattened2><LocaleIdentifier>1033
 </LocaleIdentifier></PropertyList>

Looking at this then it would appear that the SSMS query gets the metadata, retrieves and formats the data all from the one hit whereas SSIS would seem to hit the server more than once. Logic would say that having to hit the cube 3 times vs the once would be less efficient.

Another interesting feature is that if the Connection Manager in SSIS is an ADO Connection Manager then this behaviour does not happen and you get the same results as when you execute the query in SSMS.