Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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:

OK

share|improve this question

1 Answer 1

up vote 0 down vote accepted

Essentially SSIS works differently for Excel files, the instructions are much more like this. You need to hand craft your connection string in the expression field, rather than just drop the variable in there. The string will end up looking something like this:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::FileName] + ";Extended Properties=\"Excel 8.0;HDR=YES\";"

Along the way you'll have to make sure you've set the project to be 32 rather than 64 bit. You'll probably need to to refresh your column headings and debug your spreadsheets, basically SSIS is just a tiny bit unintuitive(!)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.