April 10, 2018 at 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.
April 10, 2018 at 7:40 am
Brian Shaw - Tuesday, April 10, 2018 7:27 AMHi,
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!):
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 10, 2018 at 7:53 am
Phil Parkin - Tuesday, April 10, 2018 7:40 AMBrian Shaw - Tuesday, April 10, 2018 7:27 AMHi,
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
April 10, 2018 at 8:29 am
Brian Shaw - Tuesday, April 10, 2018 7:53 AMThanks 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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 10, 2018 at 10:03 am
Brian Shaw - Tuesday, April 10, 2018 7:27 AMHi,
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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 10, 2018 at 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
April 10, 2018 at 12:37 pm
Brian Shaw - Tuesday, April 10, 2018 12:29 PMThanks 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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 11, 2018 at 11:12 am
Brian Shaw - Tuesday, April 10, 2018 12:29 PMThanks 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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 17, 2018 at 2:22 pm
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.
April 18, 2018 at 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!
April 26, 2018 at 1:34 pm
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
April 27, 2018 at 10:05 am
micheal.staples - Wednesday, April 18, 2018 9:34 AMSQL Server Version: 2017 CU 5I 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
May 7, 2018 at 6:05 am
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.
May 7, 2018 at 6:15 am
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.
May 9, 2018 at 8:05 am
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 27 total)
You must be logged in to reply to this topic. Login to reply