Home Forums SQL Server 2017 SQL Server 2017 - Development Error Processing Cube from SSIS package run as a SQL Job RE: Error Processing Cube from SSIS package run as a SQL Job

  • April 10, 2018 at 10:03 am

    #1986168

    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) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)