Error Processing Cube from SSIS package run as a SQL Job

  • vborets

    SSC Enthusiast

    Points: 131

    Anybody can share  Microsoft.AnalysisServices.AdomdClientUI.dll   v.13.0.0  ???

    Can`t solve this problem

  • uiop1

    Valued Member

    Points: 57

    I'm experiencing the same issue with SQL Server 2016 SP2... Cube Processing via SSIS package is not working anymore... Quite  annoying... Does anyone know if and when Microsoft plans to release a Hotfix for that?

  • willgart

    SSC Enthusiast

    Points: 147

    vborets - Tuesday, May 15, 2018 6:26 AM

    Anybody can share  Microsoft.AnalysisServices.AdomdClientUI.dll   v.13.0.0  ???

    Can`t solve this problem

    you can find this DLL on a computer with VS installed.

  • vborets

    SSC Enthusiast

    Points: 131

    willgart - Thursday, May 17, 2018 6:19 AM

    vborets - Tuesday, May 15, 2018 6:26 AM

    Anybody can share  Microsoft.AnalysisServices.AdomdClientUI.dll   v.13.0.0  ???

    Can`t solve this problem

    you can find this DLL on a computer with VS installed.

    Nope,  only  v.14.XXXX

  • L-P Larsson

    SSC Veteran

    Points: 200

    Brian Shaw - Tuesday, April 10, 2018 7:27 AM

    Hi,
    I am trying to upgrade a SQL server with Analysis Services to SQL 2017 cu5, 14.0.3023.8, (I tried CU4 before this) and I'm running into a problem with an SSIS package that processes my cubes, but only when run as a SQL Agent job.

    The package is very simply, 1 Analysis Services Processing Task that connects to AS on the same server and tries to process the cubes. This worked fine in previous versions of SQL, the latest we have is SQL 2014. I've tried creating a new package from scratch and get the same error when running the package as a job.

    The error is:
    Executed as user: <myUser>. Microsoft (R) SQL Server Execute Package Utility Version 14.0.3023.8 for 64-bit Copyright (C) 2017 Microsoft. All rights reserved.  Started: 9:22:29 AM Error: 2018-04-10 09:22:29.59  Code: 0x00000000  Source: Analysis Services Processing Task Analysis Services Processing Task  Description: Could not load file or assembly 'Microsoft.AnalysisServices.AdomdClientUI, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:22:29 AM Finished: 9:22:29 AM Elapsed: 0.531 seconds. The package execution failed. The step failed.

    The package runs fine in VS2017 on the server and from DTExecUI.exe, but not when run as a SQL Job. I just installed, and reinstalled, SQL 2017 developer edition on a new Windows 2016 server. I've tried this on 2 different server and received the same results.
    I tried installing the Analysis server Feature pack related items (AS AMO, AS OLE, AS ADOMD) along with copying some AS DLLs into the GAC, but nothing has worked.

    The connection string to the cubes should be fine since it works in VS, but its:
    Data Source=Server1\Instance1;Initial Catalog=MyCubes;Provider=MSOLAP.8;Integrated Security=SSPI;
    The TargetServerVersion is SQL 2017.
    I tried configuring the job step to "Use 32 bit runtime", no luck.

    Has anyone else experienced this? Does anyone know of a fix for this?

    Thanks,
    Brian S.

    There is a bug, it will probably be corrected in SP2 CU1 which I've been told will be released at the end of the month.
    We got a workaround suggestion from MS, but that was not enough.

    Inshort: The providedworkaround does not work as is, but I found out what more to do  so now itworks on the server with SP2

     

    Longer: The provided workaround does notwork as is because it does register the correct assembly and the correctversion (v14), but when the package is run it will still search for theprevious version v13.
    I have tested and this is the case even if I have SP2 on my laptop, rebuild allin the SSIS project and deply – it will still be v13 that is search for.
    It does not help to register v13 of the assembly, since that version does nothave a method that is called, so that will just lead to other error messages.

     

    Theresolution I made on the server is to put in a binding redirect in the .NETmachine.config so that v14 is used even thoug v13 is searched for.

      

    Step-by-stepI did, run from an elevated CMD:

     

    1: Register the assembly:
    "C:\Program Files (x86)\MicrosoftSDKs\Windows\v8.1A\bin\NETFX 4.5.1 Tools\x64\gacutil.exe" /i"C:\Program Files (x86)\Microsoft Visual Studio14.0\Common7\IDE\PrivateAssemblies\Microsoft.AnalysisServices.AdomdClientUI.dll"

    You shouldget message one assembly successfully registered in GAC.

     

    2: (Optional) Verify registration:

    "C:\Program Files (x86)\MicrosoftSDKs\Windows\v8.1A\bin\NETFX 4.5.1 Tools\x64\gacutil.exe" /l Microsoft.AnalysisServices.AdomdClientUI

    You shouldsee message it is version 14.0.0.0 registered

     

    3: Configure version redirect in machine.config (be careful when editing!!)

    File toedit:
    "C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config\machine.config"

     

    The elementto add content to is

    configuration/runtime

     

    On theserver I fixed it on, this element was completely empty, i.e. <runtime/>

    If it isnot empty, of course keep what’s in the element and add the assemblyBindingelement seen below.

     

    Change/addso that the whole element contains this instead:

     

    <runtime>

      <assemblyBindingxmlns="urn:schemas-microsoft-com:asm.v1">

         <dependentAssembly>

             <assemblyIdentity name="Microsoft.AnalysisServices.AdomdClientUI" publicKeyToken="89845dcd8080cc91" culture="neutral"/>

             <bindingRedirect oldVersion="13.0.0.0" newVersion="14.0.0.0" />

         </dependentAssembly>

      </assemblyBinding>

    </runtime>

     

    5: Save thefile. The package should now work.

     


    When afix in CU1 is installed, these steps should be done:

     

    1: Remove the above added assemblyBinding element and save the file

     

    2: Remove the dll that was added to the GAC:
    "C:\Program Files (x86)\MicrosoftSDKs\Windows\v8.1A\bin\NETFX 4.5.1 Tools\x64\gacutil.exe" /u Microsoft.AnalysisServices.AdomdClientUI


     

  • vborets

    SSC Enthusiast

    Points: 131

  • ahitz

    SSC Enthusiast

    Points: 104

    I don't see any mention of this issue in the release notes for 2016 SP2 CU1, but I did install the update on one server and can confirm it fixed the issue there.

  • ListerD

    SSC Veteran

    Points: 242

    Good

    Good to hear!  Saw there was no mention of it as well, but I will not have a chance to test this until early next week.  Will report back findings.

  • Chris Mangrum

    SSC Rookie

    Points: 34

    No mention of it in the 2016 CU, but it is mentioned in the 2017 CU.  We installed it on our 2016 dev box hoping it was an unintended omission.  It appears to have fixed the problem for us.

  • romil.shah 99314

    Valued Member

    Points: 74

    After upgrading to SQL Server  2016 SP2 we are facing following issues and our job is failing.

  • ASP_Cube_RCC_Load:Error:Could not load file or assembly 'Microsoft.AnalysisServices.AdomdClientUI,Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one ofits dependencies. The system cannot find the file specified
  • We have tried to place the DLL file in almost all folders but still unable to resolve the issue.

    We have now moved to CU9 SP1 but still issue is not resolved.

    Can anyone help us???

  • vborets

    SSC Enthusiast

    Points: 131

    romil.shah 99314 - Wednesday, June 20, 2018 11:28 PM

    After upgrading to SQL Server  2016 SP2 we are facing following issues and our job is failing.

  • ASP_Cube_RCC_Load:Error:Could not load file or assembly 'Microsoft.AnalysisServices.AdomdClientUI,Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one ofits dependencies. The system cannot find the file specified
  • We have tried to place the DLL file in almost all folders but still unable to resolve the issue.

    We have now moved to CU9 SP1 but still issue is not resolved.

    Can anyone help us???

    You must install Cumulative Update package 1 (CU1) SP2 (build number: 13.0.5149.0)

  • romil.shah 99314

    Valued Member

    Points: 74

    Error Processing Cube from SSIS package run as a SQL Job

    I will check and revert.

  • ListerD

    SSC Veteran

    Points: 242

    ListerD - Thursday, May 31, 2018 12:28 PM

    Good

    Good to hear!  Saw there was no mention of it as well, but I will not have a chance to test this until early next week.  Will report back findings.

    Worked for us as well on 2016

  • Viewing 13 posts - 16 through 28 (of 28 total)

    You must be logged in to reply to this topic. Login to reply