ForEach loop with Excel

Hi,

I'm attempting to use the Foreach loop container to loop through the excel files located on a shared network folder. I've set up the Excel file connection manager to include the user variable generated from the container, but I get the below error messages when applying that variable to the connection string in the data flow. I've tried everything but I can't seem to get SSIS to recognize the path of the Excel files. I've tried copying the files to my PC, I tried running the package on the server, etc. The connection works fine if I set it up to point to any of the excel files in the network directory, but not with the Foreach loop connection name.

Any help or suggestions would be greatly appreciated! I've looked everywhere and tried everything but to no avail...

Thanks,

Kevin

TITLE: Microsoft Visual Studio

Error at GDW - RDB LOAD [Connection manager "UK RDB"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.

Error at Extract UK RDB [UK RDB [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "UK RDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)

BUTTONS:

OK

You should give more detail about how you're configuring the connection manager, included any expressions and which properties they're being applied to. Confirming the run-time values of variables through the use of breakpoints would be helpful, too.

You say you're setting the connection string. Isn't there a FileName property?
|||

Thanks!

It's my first time posting on this site so I'll try and do a better job explaining what I'm trying to do.

I have a connection to an Excel file created in my package. Using the ForEach loop I'm attempting to change the connection string on that connection for each excel file located in the directory. I've created a user variable in the ForEach loop that's supposed to be populated with the fully qualified location of each excel file. The data flow component has the error on it before I even execute the package. That component is linked to an OLE DB destination which is a SQL table.

I can send you further detail if you'd like or if I'm missing anything...

Kevin

|||

Kevin wrote:

I have a connection to an Excel file created in my package. Using the ForEach loop I'm attempting to change the connection string on that connection for each excel file located in the directory. I've created a user variable in the ForEach loop that's supposed to be populated with the fully qualified location of each excel file. The data flow component has the error on it before I even execute the package. That component is linked to an OLE DB destination which is a SQL table.

You're saying the OLE DB Destination component is giving you an error? I would expect the error to be on the Excel Source. Usually this is because whatever variables are used in the expression to control Source have not been initialized with default values. The Source needs design-time access to one of the files so it can read the metadata.

Your For Each loop should be placing the fully qualified name of your Excel files into a package-level variable. This package-level variable should have a valid path to an existing file as a default value. You should set up an expression on the Excel connection manager to set the ExcelFilePath property with your variable containing the filename.
|||

The OLE DB Destination component is ok. I've assigned an excel file to the excel source but it gets overwritten because of the ForEach loop variable that was created.

I've done what you explained in the second part of your reply. I set the ConnectionString expression in the Excel file connection to the variable in the ForEach loop.

I used the example in this article but with an excel connection:

http://www.sqlis.com/55.aspx

Thank you,

Kevin

|||

Kevin wrote:

The OLE DB Destination component is ok. I've assigned an excel file to the excel source but it gets overwritten because of the ForEach loop variable that was created.

Correct. The default value is only there for design-time metadata. It will be overwritten at run-time when you actually read the file.

Kevin wrote:

I've done what you explained in the second part of your reply. I set the ConnectionString expression in the Excel file connection to the variable in the ForEach loop.

I think you want the ExcelFilePath property, not the ConnectionString.
|||

I really appreciate your help.

I tried using the ExcelFilePath expression instead and I'm still getting the same error. Also, when I set the excel file in the connection and then go back into it in design mode, the file path is empty.

This is getting pretty frustrating to have this great option but not have it work

Here's the message text again. It's erroring on Package Validation...

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Extract UK RDB [UK RDB [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "RDB" 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 Extract UK RDB [DTS.Pipeline]: component "UK RDB" (1) failed validation and returned error code 0xC020801C.

Error at Extract UK RDB [DTS.Pipeline]: One or more component failed validation.

Error at Extract UK RDB: There were errors during task validation.

Error at GDW - RDB LOAD [Connection manager "RDB"]: 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: "Invalid argument.".

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

OK

|||

I think I may have helped answer my own post with that last reply. There's a setting in the package properties called DelayValidation which I set to True, and now the package runs. I still get the error on the data flow component, but each excel file is loaded. DelayValidation indicates whether the validation of the executable is delayed until run time.

|||

Kevin wrote:

I tried using the ExcelFilePath expression instead and I'm still getting the same error.

I don't know what that error is. Maybe the component got messed up somehow. Try deleting it and creating a new one.

Kevin wrote:

Also, when I set the excel file in the connection and then go back into it in design mode, the file path is empty.

That indicates to me that you don't have a default value in that variable.