Home / server / Questions / 117089
Accepted
alimack
Asked: 2010-02-27 01:34:18 +0800 CST

SSIS Foreach loop (SQL Server 2005) adding loop variable causes failure

  • 772

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

sql-server-2005 ssis microsoft-excel

1 Answers

  1. Best Answer

    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(!)

    • 0