I'm trying to use SSIS to traverse a directory of Excel files and put them into a table in SQL Server. I seem to have a basic choice - either I get the same spreadsheet dumped in the table 10 times or I get an error message. I'm using the instructions here:
http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx
Error message:
TITLE: Package Validation Error
Package Validation Error
ADDITIONAL INFORMATION:
Error at Data Flow Task [Destination - HR_Data [200]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "DestinationConnectionOLEDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error at Data Flow Task [DTS.Pipeline]: component "Destination - HR_Data" (200) failed validation and returned error code 0xC020801C.
Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.
Error at Data Flow Task: There were errors during task validation.
Error at HR Full Data Load [Connection manager "DestinationConnectionOLEDB"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for ODBC Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
(Microsoft.DataTransformationServices.VsIntegration)
BUTTONS: