0

I'm trying to extract data from a cube using MDX, when I run the query in SSMS I get 500K rows (same result I get when I use excel to connect to the cube), however, when I put the query into a SSIS package and execute it I get only 100k rows. The package executes just fine (completes correctly), it doesn't show any errors, warnings, anything so not sure why I'm not getting the same number on rows :(.

Thanks for the help! :)

user1112251
  • 119
  • 1
  • 4
  • 13
  • Could you please tell us what OLEDB provider version you are using in your connection? – Matt Apr 28 '15 at 19:21
  • Here are the connection string details **Provider=MSOLAP.5;Integrated Security=SSPI;format=tabular;** and I'm using a **OLEDB** data flow task. – user1112251 Apr 28 '15 at 20:01
  • Thanks. How long is the query running in SSMS? Are you hitting your timeout threshold? – Matt Apr 28 '15 at 20:21
  • 1
    Are you sure you are pointing to the same database? – rvphx Apr 28 '15 at 20:33
  • Is the same user executing the query? Could it be that a Role/Perspective is at play? – billinkc Apr 28 '15 at 20:49
  • Timeout is set as 0 so I don't think we are timing out, also the package executes without any errors at all. Query takes around 30 - 35 min to run in SSMS. Same user is running the queries in SSMS and in the SSIS package. – user1112251 Apr 28 '15 at 21:11
  • Did you try a profiler trace on the SSAS server while the SSIS query was running? – Tab Alleman Apr 29 '15 at 14:56

2 Answers2

0

Well I still don't know what was causing this issue but I was able to pull all of the data by using a linked server query instead of a direct MDX query to the cube.

user1112251
  • 119
  • 1
  • 4
  • 13
0

If you dump it to RAW file SSIS dumps the whole extract. Then you can import from the RAW file. It's not an ideal but solves the issue.

DmitryV
  • 31
  • 3