I have created an SSIS package that grabs an Excel spreadsheet from a web server and copies all of the rows into a separate SQL Server. The reason that we are doing this is because, when the app is done, the client will be able to load a spreadsheet using a web browser, and once that spreadsheet is done I am going to run an SP to trigger the SSIS package to import the data into the table.
The error I get when running the SSIS package is:
[SQL Server Destination ] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security.".
The Excel source is referenced as \\xx.xxx.xx.xxx\E$\Web\Colony1\ExcelImport\Import.xls
I suspect that there is not a problem with the spreadsheet since the error is pointing to the SQL Server, but I am not 100% sure and I don't know where to look.