x

ssis scheduled job on ssas fails on excel connection

Here is an explanation of the issues I am having and steps I have taken to get my ssis package to work in the clustered instance environment of a 32bit SQLserver database…

We have created a clustered user on both the Windows side and a corresponding Credential on the SQLserver. We then created a Proxy using the Credential and a cluster/smartssis logon for the test database giving the logon datareader and datawriter roles. The cluster/smartssis user was given full security to all the appropriate Windows folders. I have run this package through the SSAS on a standalone 32bit and 64bit server(using CmdExec) as well as a 64bit SQL server instance setup and have done so with success…finally. The error I’m getting with the clustered server is an error connecting to the Microsoft Jet OLEDB engine.

To try to rectify this I created a configuration file that tries to connect to the template .xls file using the \ (we created a share for the Packages folder). For the servername, I have tried the actual servername, the virtual servername, and the virtual servername/instancename, as well as hardcoding the path C:...\Packages… None of these have worked in creating the excel spreadsheet. The database portion of the package runs fine and produces rows in the tables.

I was successful in creating the .xls file when running the package interactively by logging into the Integration Services connection and executing it through the File System.

Here is the actual error being produced…

Message Executed as user: CLUSTER\SMARTSSIS. ...00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:19:33 AM Error: 2010-05-05 11:19:58.42 Code: 0xC0202009 Source: AESOPSubload Connection manager "Excel Connection Manager Sub" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error". End Error Error: 2010-05-05 11:19:58.56 Code: 0xC00291EC Source: Create Excel Worksheet Execute SQL Task Description: Failed to acquire connection "Excel Connection Manager Sub". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Error: 2010-05-05 11:20:06.50 Code: 0x00000000 Source: Execute stored procedure worker Description: String or binary data would be... The package executed successf... The step succeeded.

The Windows user cluster/smarssis is a poweruser and has full authority to the folder the template .xls file is in.

more ▼

asked May 14, 2010 at 04:42 PM in Default

Mortimar71 gravatar image

Mortimar71
1 1 1 2

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

If it works with your permissions, then it must have something to do with the connections. Maybe they are encrypted so the proxy account can't read them?

Try loggin in as the proxy account, then open the package in BIDS and try running it. If it still fails, try entering the connection details back in.

more ▼

answered May 17, 2010 at 11:51 PM

Daniel Ross gravatar image

Daniel Ross
2.9k 10 12 14

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x927
x171
x6

asked: May 14, 2010 at 04:42 PM

Seen: 2118 times

Last Updated: May 17, 2010 at 12:58 PM