Processing cubes on a different server, using SSIS package run by a SQL Agent job from another s

Hi,

I am faced with this issue in a production environment. I have to implement an architecture where the Analysis Services database is in a server different from the server on which the SQL Serever database it accesses for data is.

I have changed the connection string for the data source in the Analysis Services database to point to the other server's SQL Server database. Log on to this server has been set to use SQL Server Authentication.

Then I created an SSIS package on the server containing SQL Server database, to process a dimension in the other server's Analysis Services database. The Analysis Services connection manager in this package was set to log on to the Analysis Services database on the other server using 'Specific User name and Password'. This user is a part of the administrator group on the server containing Analysis Services database. The package executed fine when executed from the Business Intelligence Studio.

The problem came when I tried to execute this package through a SQL agent job. The job is created on the server containing the SQL Server database. The step in the job meant to execute the package uses 'SQL Agent Service Account' for the 'Run as' option. The package was deployed as 'File System'.

I missed out posting the error message I am getting. It is as follows:

Code: 0x00000000 Description: A connection cannot be made. Ensure that the server is running.

Hope this helps to give a better understanding of the problem.

Thanks.

|||

Hi,

The problem was resolved by changing the LOG ON for the SSIS service to that of an user who is administrator on both the machines.

|||

Did you have to create a Credential and Proxy? I had to create one and set the Run As parameter for the step in Agent to the Proxy name. The job has to write a file to a network directory and from what I read it seems that the only way for an SSIS job to access anything outside SQL Server was by using a proxy. I am not sure if the same applied in your situation.

|||

Hmm.... I was helped by the fact that the OS was installed with a network administrator id. So no new credentials or proxy was required. The run as parameter for my job's step remained as "SQL Agent Service Account" and changed the Log On for SSIS service to use network administrator account. Hope this helps.

I would like to know how you created a proxy and made the Run As parameter to point to it.

Regards,

Emil