Home » Microsoft TechnologiesRSS

OLEDB Source running full MDX query when validating

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 

 

9 Answers Found

 

Answer 1

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.

 

Answer 2

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

 

Answer 3

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]

 

Answer 4

Anyone from Microsoft please????

 

Answer 5

Hi Pelucon,

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

Thanks,

Bob 

 

Answer 6

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;

 

Answer 7

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,
 

Answer 8

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

 

Answer 9

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.

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter