Error Processing Cube from SSIS package run as a SQL Job

  • Brian Shaw

    Say Hey Kid

    Points: 682

    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.

  • Phil Parkin

    SSC Guru

    Points: 243693

    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.

    A couple of things to check (which, I agree, do not seem to bear any relevance to the error message!):

    • If you connect to the AS instance via SSMS and get properties on the cube's data sources, do they all point to the correct database instance?
    • Is the data source impersonation set to 'ImpersonateServiceAccount' and does the SSAS service account have access to the datasource databases?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Brian Shaw

    Say Hey Kid

    Points: 682

    Phil Parkin - Tuesday, April 10, 2018 7:40 AM

    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.

    • If you connect to the AS instance via SSMS and get properties on the cube's data sources, do they all point to the correct database instance?
    • Is the data source impersonation set to 'ImpersonateServiceAccount' and does the SSAS service account have access to the datasource databases?

    Thanks for the reply Phil.
    All data sources are pointing to the correct DB instance.  Since it processes fine in VS I know those connections are good.
    The data source impersonation was set to "Default", I changed it to "ImpersonateServiceAccount", but still received the error. The SSAS service account does have access to the datasource.

    -Brian

  • Phil Parkin

    SSC Guru

    Points: 243693

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

    Thanks for the reply Phil.

    All data sources are pointing to the correct DB instance.  Since it processes fine in VS I know those connections are good.
    The data source impersonation was set to "Default", I changed it to "ImpersonateServiceAccount", but still received the error. The SSAS service account does have access to the datasource.

    -Brian

    The annoying thing is that I had this error not so long ago ... and, you know how it is, I can't remember how I solved it.
    I'll post back if my memory manages to dredge up anything useful.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • sgmunson

    SSC Guru

    Points: 110440

    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.

    This seems likely to be either a permissions issue, or a file path issue that wouldn't be a problem within VS, but is when run as a SQL Agent Job.  The two execution contexts are likely rather different.  I'd start looking at permissions to the file locations of the assembly being run, and see if the Agent Service account has execute permissions or even read permissions to that folder.    It may not.   Your error effectively says "file not found", which usually means you "just can't see it".   That can be anything from a path not being specified correctly, to a folder permissions issue.   I'd see if I could get that path into a package variable and somehow log it before that part of the package executes.   That might be rather revealing...

    Steve
    ‌(aka sgmunson)
    ‌:) 🙂 🙂
    Health & Nutrition

  • Brian Shaw

    Say Hey Kid

    Points: 682

    Thanks Steve.
    The SQL agent is running with a service account in the administrators group so its not a permission thing, but that got me thinking.
    I tried running the package as the service account with DTExec.exe (32 and 64 bit) and received the same error with both, so its not related to SQL agent.

    I fired up Process Monitor and discovered it was looking for Microsoft.AnalysisServices.AdomdClientUI.dll in a directory where it didn't exist. I found this DLL, copied to where it wanted it and it works now. It was looking for it in several places, I copied the file from D:\Program Files (x86)\Microsoft SQL Server\140\DTS\Binn to D:\Program Files\Microsoft SQL Server\140\DTS\Binn. I know one directory is for 32 bit DLLs and the other is for 64 bit DLLs, but I tried it and it worked.

    As far as I am concerned this is a bug in the installation process or DTExec.exe.

    -Brian

  • Phil Parkin

    SSC Guru

    Points: 243693

    Brian Shaw - Tuesday, April 10, 2018 12:29 PM

    Thanks Steve.
    The SQL agent is running with a service account in the administrators group so its not a permission thing, but that got me thinking.
    I tried running the package as the service account with DTExec.exe (32 and 64 bit) and received the same error with both, so its not related to SQL agent.

    I fired up Process Monitor and discovered it was looking for Microsoft.AnalysisServices.AdomdClientUI.dll in a directory where it didn't exist. I found this DLL, copied to where it wanted it and it works now. It was looking for it in several places, I copied the file from D:\Program Files (x86)\Microsoft SQL Server\140\DTS\Binn to D:\Program Files\Microsoft SQL Server\140\DTS\Binn. I know one directory is for 32 bit DLLs and the other is for 64 bit DLLs, but I tried it and it worked.

    As far as I am concerned this is a bug in the installation process or DTExec.exe.

    -Brian

    Well done on tracking that down.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • sgmunson

    SSC Guru

    Points: 110440

    Brian Shaw - Tuesday, April 10, 2018 12:29 PM

    Thanks Steve.
    The SQL agent is running with a service account in the administrators group so its not a permission thing, but that got me thinking.
    I tried running the package as the service account with DTExec.exe (32 and 64 bit) and received the same error with both, so its not related to SQL agent.

    I fired up Process Monitor and discovered it was looking for Microsoft.AnalysisServices.AdomdClientUI.dll in a directory where it didn't exist. I found this DLL, copied to where it wanted it and it works now. It was looking for it in several places, I copied the file from D:\Program Files (x86)\Microsoft SQL Server\140\DTS\Binn to D:\Program Files\Microsoft SQL Server\140\DTS\Binn. I know one directory is for 32 bit DLLs and the other is for 64 bit DLLs, but I tried it and it worked.

    As far as I am concerned this is a bug in the installation process or DTExec.exe.

    -Brian

    Excellent that you were able to track it down, but I have to wonder if the PATH environment variable might be where the problem started.   The order in which the path elements occur can sometimes be critical to finding files, and while copying a file to where it's "expected" can solve the problem, it may be that the PATH just isn't set right, or failed to be updated to the new file location by the most recent product upgrade installation or other similar install.   I'd check your current PATH environment variable to see if that file would be found in any of the folders listed.   Alternatively, there may just be a registry entry that just points to the wrong folder, very possibly for the same reasons I suggested were possible causes of PATH problems.   Admittedly, the registry is a dangerous place to go, and one would want to verify the correctness (or incorrectness) of what you have before making a change, as well as have a backup of same before making a change.

    Steve
    ‌(aka sgmunson)
    ‌:) 🙂 🙂
    Health & Nutrition

  • willgart

    SSC Enthusiast

    Points: 147

    Hi guys,

    I got the same issue here after installing the CU5.
    found the DLL in the Visual studio folder of my dev computer.
    once copied in the BINN folder of SQL Server everything starts working.

    is microsoft aware about this problem?
    its certainly a dev guy which references this DLL by mistake.
    and pass all the Microsoft unit tests.... which mean... they dont test all the SSIS tasks before releasing a CU.
    this is a huge problem.

  • micheal.staples

    Valued Member

    Points: 53

    SQL Server Version: 2017 CU 5

    I had the same problem as described here. I tried to modify the PATH variable to remove all old instances of SQL Server. However, no change in behavior. I then attempted to find the DLL for the ADOMD client, but none existed on the server. After installing the 64-bit ADOMD client from https://docs.microsoft.com/en-us/azure/analysis-services/analysis-services-data-providers, I was able to copy Microsoft.AnalysisServices.AdomdClientUI.dll from C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio to both C:\Program Files (x86)\Microsoft SQL Server\140\DTS\Binn and C:\Program Files\Microsoft SQL Server\140\DTS\Binn. This resolved the issue for me.

    Also, another symptom I was experiencing is when deploying my Tabular model using the deployment wizard, it would fail and lock my domain account even though it only appears to attempt the deployment one time. Super frustrating!! I'm not sure if this was related or if I was doing something else wrong, but thought I would mention it.

    Thanks!

  • chelly.med

    Valued Member

    Points: 57

    Hi,
    I had the same problem after installing SQL SERVER 2016 SP2.
    I can't find the 130 version of the Microsoft.AnalysisServices.AdomdClientUI.dll file.
    I tested with the 140 version but I have an error that the assembly version la ont compatible.
    Any idea ?
    Many thanks

  • NitinPatel31

    Hall of Fame

    Points: 3611

    micheal.staples - Wednesday, April 18, 2018 9:34 AM

    SQL Server Version: 2017 CU 5

    I had the same problem as described here. I tried to modify the PATH variable to remove all old instances of SQL Server. However, no change in behavior. I then attempted to find the DLL for the ADOMD client, but none existed on the server. After installing the 64-bit ADOMD client from https://docs.microsoft.com/en-us/azure/analysis-services/analysis-services-data-providers, I was able to copy Microsoft.AnalysisServices.AdomdClientUI.dll from C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio to both C:\Program Files (x86)\Microsoft SQL Server\140\DTS\Binn and C:\Program Files\Microsoft SQL Server\140\DTS\Binn. This resolved the issue for me.

    Also, another symptom I was experiencing is when deploying my Tabular model using the deployment wizard, it would fail and lock my domain account even though it only appears to attempt the deployment one time. Super frustrating!! I'm not sure if this was related or if I was doing something else wrong, but thought I would mention it.

    Thanks!

    Thanks Guys. I had the same issue. It saved my day. I have copied dll in "C:\Program Files\Microsoft SQL Server\140\DTS\Binn" and it worked.

    Regards,
    Nitin

  • ListerD

    SSC Veteran

    Points: 242

    We recently upgraded from SQL Server 2016 CU8 to SQL Server 2016 SP2. After upgrading, SSIS packages which perform cube processing functions throw the following error:

    Error Code: 0 Description: Could not load file or assembly 'Microsoft.AnalysisServices.AdomdClientUI, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.

    This has happened on two servers we upgraded in our lower environments. Anyone heard anything from MS? There was a posting on the community PowerBI board about this issue last week, but it seems to have been pulled.

  • willgart

    SSC Enthusiast

    Points: 147

    SQL 2017 suffered the same issue.
    you'll found the missing DLL from a PC where VS is installed. this missing DLL is not available on the server itself.
    Copy it on the BINN folder of your server.

  • ahitz

    SSC Enthusiast

    Points: 104

    After installation of SQL Server 2016 SP2, was also getting this error (for Version 13.0.0.0), I was getting this error.  I tried copying from a bunch of different ones, and also had to add to the GAC, but can only get it to where it now returns "Error: Method not found: 'Boolean Microsoft.AnalysisServices.AdomdClientUI.AdomdConnectionStringBuilder.IsAzure()'

Viewing 15 posts - 1 through 15 (of 28 total)

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