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.
