processing cube on remote host using ssis

Roman Primolennyy 101 Reputation points
2020-12-24T16:20:56.173+00:00

Hello
I am using Analysis Services Processing Task to process cube and use the following connection string:
Data Source=<SSAS instance>;User ID=<User ID>;Password=<Password>;Initial Catalog=BTR;Provider=MSOLAP.5;Persist Security Info=True;
It works fine if cube and SSIS sit on the same host, but if cube deployed on the remote host I have got an error message : "A connection cannot be made .Ensure that the server is running."
The server is running, I am certain.The user who run the package has administrative priveliges on remote host and Admin rights in the cube. What might be the problem ? Please, help

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,702 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,344 questions
0 comments No comments
{count} votes

Accepted answer
  1. Roman Primolennyy 101 Reputation points
    2020-12-25T14:20:23.153+00:00

    I have found workaround:

    1. Create a package which process the cube on remote host.
    2. Create agent job which calls that package.
    3. Create the package which calls that job.
      Of course, it would be interesting to find the way to process the cube directly.
    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
  1. Monalv-MSFT 5,926 Reputation points
    2020-12-25T02:15:13.557+00:00

    Hi @Roman Primolennyy ,

    1. Please use SQL Server Configuration Manager to check the following server and services on the remote host are installed and running:
      a. SQL Server
      b. SQL Server Analysis Services
      c. SQL Server Integration Services
      Please refer to How To: Ensure SQL Server Components Are Installed.
    2. Please check if the SQL server on the remote host allows remote connection.

    Best Regards,
    Mona

    ----------

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    Hot issues in November--What can I do if my transaction log is full?
    Hot issues in November--How to convert Profiler trace into a SQL Server table?


  2. Lukas Yu -MSFT 5,826 Reputation points
    2020-12-25T05:51:07.07+00:00

    Hi,

    Looks like you have connectivity issues in your remote server. We could try to troubleshot this from two aspects.

    1. Try to check if the TCP/IP connection is OK.
      -- In Prompt Command run -ping *SSASServerName* see if two machine is connected.
      -- Then try -TELNET SQLASCLUS 2383 two check the port. (Normally this is the default port. )
    2. Try connect to SSAS with SSMS in client machine. If it could not connect, check what the error is.
    3. Also make sure the SQL Browser is running in your SSAS server.

    For detail description of the troubleshooting follow this : Troubleshooting Common SQL Server Analysis Services Connectivity Issues

    Regards,
    Lukas


    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    What can I do if my transaction log is full?--- Hot issues November
    How to convert Profiler trace into a SQL Server table -- Hot issues November


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.