SSIS Package working in BI Studio but not working in SQLServer 2008

0
votes

I have the SSIS package which iterates all excel files in the particular folder and import rows into the SQL 2008 database. I use 4 Package Scope Variables for files and folders paths.

enter image description here

I could run the package and import all data in excel files if I run the package in BI Studio.

But when I move the package and set up the job in the SQL 2008 server, I could not run the package and it shows me the following message.

Executed as user: xxxxx Microsoft (R) SQL Server Execute Package Utility Version 10.0.4000.0 for 32-bit. Started: 16:19:23 Error: 2011-05-26 16:19:23.53 Code: 0xC0202009 Source: PCounter Connection manager "Excel Connection Manager" 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: "The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data.". End Error Error: 2011-05-26 16:19:23.53 Code: 0xC020801C Source: Data Flow Task Excel Source 1 Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2011-05-26 16:19:23.53 Code: 0xC0047017 Source: Data Flow Task SSIS.Pipeline Description: component "Excel Source" (1) failed validation and returned error code 0xC020801C. End Error Error: 2011-05-26 16:19:23.53 Code: 0xC004700C Source: Data Flow Task SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2011-05-26 16:19:23.53 Code: 0xC0024107 Source: Data Flow Task Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 16:19:23 Finished: 16:19:23 Elapsed: 0.547 seconds. The package execution failed. The step failed.

I am sure that the files are not opened by anyone and my SSIS Proxy account do has the permission to access that folder. It still shows me this error however I tried.

Please give me suggestions.

1
sqlsql-server-2008ssisdata-import
I run it as the SQL Job Under SQL Server Agent – TTCG
You can also use a tool like technet.microsoft.com/en-us/sysinternals/bb896655 to see if any other process has their finger on the file. – billinkc

1 Answers

1
votes

You are using a variable to set the excel file via your Excel Connection Manager. Unless you have DelayValidation set to true at the package level, the connection manager will fail when it can't find the file you originally specified when you set up the Excel Connection Manger. Try setting DelayValidation on both the package and the connection manager to true - or make sure that the file you used to set up the connection manager is accessible by your sql server/sql agent account.