none
OLEDB Source running full MDX query when validating RRS feed

  • Question

  • Hi,

     

    I have an Integration Services project which creates a flat file report from Analysis Services, I'm using an OLE DB as data source and running an Openquery in the SQL statement.

     

    the problem is that Integration services runs the query twice before getting the data into the flat file. I know this because the query runs two times in Profiler, and because the same query takes half the time when run in Management Studio.

     

    Integration Services is running the whole query when validating. how can I disable this validation or better make it validate properly.

     

    thanks 

    Monday, February 18, 2008 9:20 PM

Answers

  • Hi,

     

    I opened a case about this issue with microsoft. So I'm going to share the solution.

     

    There is a way to run an MDX without going through a Linked Server an using a plain OLE DB Source, and the Analysis Services 9.0 Driver.

     

    The thing is you have to edit the connection string manually, because this option isn't showed even in the advanced properties.

     

    Data Source=localhost;Initial Catalog=AdventureWorksDW;Provider=MSOLAP.3;Format=Tabular;Integrated Security=SSPI;

     

     

    Monday, March 24, 2008 3:11 PM

All replies

  • SSIS validates that queries are correct by executing them. In SQL Server, it runs a SET FMTONLY ON before running the query, so only the metadata is returned. I don't know if there is an equivilent in MDX (I don't think so). Have you tried setting the DelayValidation property to TRUE on the data flow task? That disables one of the validation checks.

     

    Tuesday, February 19, 2008 12:50 AM
    Moderator
  • Yes I have tried setting delay validation to true, but that just disabled validation during design, but not in execution.

    Tuesday, February 19, 2008 1:28 AM
  • Actually, it prevents the initial validation check when the package is loaded at runtime, but not the one immediately before the task is executed.

     

    Not sure what to do here. Maybe one of the MSFT guys can suggest something.

     

    [Microsoft follow-up]

     

    Tuesday, February 19, 2008 2:06 AM
    Moderator
  • Anyone from Microsoft please????

    Tuesday, February 19, 2008 3:40 PM
  • Hi Pelucon,

     

    could you try to set ValidateExternalMetadata property to false on the OLE DB Source component and see if that helps?

     

    Thanks,

    Bob 

     

    Friday, March 21, 2008 10:58 PM
  • Hi,

     

    I opened a case about this issue with microsoft. So I'm going to share the solution.

     

    There is a way to run an MDX without going through a Linked Server an using a plain OLE DB Source, and the Analysis Services 9.0 Driver.

     

    The thing is you have to edit the connection string manually, because this option isn't showed even in the advanced properties.

     

    Data Source=localhost;Initial Catalog=AdventureWorksDW;Provider=MSOLAP.3;Format=Tabular;Integrated Security=SSPI;

     

     

    Monday, March 24, 2008 3:11 PM
  • Pelucon,

    Tried your solution but the query is still running twice and some times three times.

    I tried this from the Report Manager and also using the method LoadReport and in both cases I get multiple querries.

    Thanks,
    Thursday, April 3, 2008 4:58 PM
  • Pelucon's response was around using it in SSIS - I don't know that it would apply to SSRS.

     

    Thursday, April 3, 2008 6:26 PM
    Moderator
  • I also have this problem in R2.

    When I set DelayValidation=True

    and ValidateExternalMetaData=False

    and RetainSameConnection=False

    and add Format=Tabular to the ConnectionString

    my package still fails when looping inside of a loop.

    Monday, September 20, 2010 2:09 PM
  • I'm still getting this issue even with the work around above
    To get round this I used a script component as a data source rather than ODBC or OLE DB so that it doesn't validate each time it loops around. 

    Imports System
    Imports System.Data
    Imports System.Math
    Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
    Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
    Imports Microsoft.AnalysisServices.AdomdClient
    Imports Microsoft.AnalysisServices
    Imports System.Data.SqlClient
    Imports AMO = Microsoft.AnalysisServices

    <Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute()> _
    <CLSCompliant(False)> _
    Public Class ScriptMain
        Inherits UserComponent
        Private cn As AdomdConnection
        Private cmd As AdomdCommand
        Dim CubeReader As AdomdDataReader


        Public Overrides Sub PreExecute()
            MyBase.PreExecute()

            Dim Cmd As New AdomdCommand
            cn = New AdomdConnection(Me.Connections.SSAS.ConnectionString)
            cn.Open()

            Cmd = New AdomdCommand()
            Cmd.CommandType = CommandType.Text
            Cmd.CommandText = Me.Variables.MDX.ToString
            Cmd.CommandTimeout = 0
            Cmd.Connection = cn
            CubeReader = Cmd.ExecuteReader()

        End Sub

        Public Overrides Sub CreateNewOutputRows()


            Do While CubeReader.Read
                With Output0Buffer

                    .AddRow()
    'You need to set up the output columns in the Input and Outputs section

                    .OutputVariable1 = CubeReader.GetValue(0)


                End With

            Loop

            cn.Close()
            CubeReader.Close()
        End Sub
    End Class
    Friday, December 24, 2010 12:57 PM