SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Error Processing Cube from SSIS package run as a SQL Job


Error Processing Cube from SSIS package run as a SQL Job

Author
Message
Brian Shaw
Brian Shaw
SSC-Addicted
SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)

Group: General Forum Members
Points: 498 Visits: 401
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
Phil Parkin
SSC Guru
SSC Guru (240K reputation)SSC Guru (240K reputation)SSC Guru (240K reputation)SSC Guru (240K reputation)SSC Guru (240K reputation)SSC Guru (240K reputation)SSC Guru (240K reputation)SSC Guru (240K reputation)

Group: General Forum Members
Points: 240006 Visits: 25403
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?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

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.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Brian Shaw
Brian Shaw
SSC-Addicted
SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)

Group: General Forum Members
Points: 498 Visits: 401
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
Phil Parkin
SSC Guru
SSC Guru (240K reputation)SSC Guru (240K reputation)SSC Guru (240K reputation)SSC Guru (240K reputation)SSC Guru (240K reputation)SSC Guru (240K reputation)SSC Guru (240K reputation)SSC Guru (240K reputation)

Group: General Forum Members
Points: 240006 Visits: 25403
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

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.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
sgmunson
sgmunson
SSC Guru
SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)

Group: General Forum Members
Points: 108915 Visits: 7524
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)
Smile Smile Smile
Health & Nutrition
Brian Shaw
Brian Shaw
SSC-Addicted
SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)

Group: General Forum Members
Points: 498 Visits: 401
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
Phil Parkin
SSC Guru
SSC Guru (240K reputation)SSC Guru (240K reputation)SSC Guru (240K reputation)SSC Guru (240K reputation)SSC Guru (240K reputation)SSC Guru (240K reputation)SSC Guru (240K reputation)SSC Guru (240K reputation)

Group: General Forum Members
Points: 240006 Visits: 25403
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

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.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
sgmunson
sgmunson
SSC Guru
SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)

Group: General Forum Members
Points: 108915 Visits: 7524
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)
Smile Smile Smile
Health & Nutrition
willgart
willgart
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 Visits: 179
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
micheal.staples
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 15
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search