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 whenrun in Management Studio. Integration Services is runningthe whole query when validating. how can I disable this validation or better make it validate properly. thanks

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.

Yes I have tried setting delay validation to true, but that just disabled validation during design, but not in execution.

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]

Anyone from Microsoft please????

Hi Pelucon, could you try toset ValidateExternalMetadata property to false on the OLE DB Source component and see if that helps? Thanks, Bob

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;

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,

Pelucon's response was around using it in SSIS - I don't know that it would apply to SSRS.

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.

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