-1

How to fetch the Excel file path connection dynamically through a variable in the Excel Source?

Inside my Foreach Loop Container, I have Excel Source which has Excel Connection String.

I am using the variable to map the Incoming folder path. I set DelayValidation to True.

Folder Path : c:\IncomingPath\

However, I am getting an error as

The connection is not found.

We put the excel files by extracting the zip file. Steps are done through SSIS Package.

enter image description here

CR241
  • 2,293
  • 1
  • 12
  • 30
goofyui
  • 3,362
  • 20
  • 72
  • 128
  • Are you also using the variable to set the source on the Excel connectionstring property? – Jacob H Jan 08 '19 at 18:17
  • I am using the variable mapped in the Excel Connection String by giving the folder path. Variable does not have the Excel file – goofyui Jan 08 '19 at 18:18

1 Answers1

0

In the Foreach Loop make sure it's using the Foreach File Enumerator type if it's not already. Then on the Variable Mappings page, set a string variable to Index 0, this will hold the file name for each iteration of the loop. Then go to the Excel Connection Manager, click on the ellipsis next to the Expressions property (highlight the connection manager and press F4 to view the Properties window) and set the same string variable that was set at Index 0 in the loop as the expression for the ExcelFilePath, not ConnectionString, property. This will set the Excel Source component to use the current file from each iteration of the Foreach Loop.

userfl89
  • 4,610
  • 1
  • 9
  • 17
  • I did exactly by keeping the variable in the For each loop container with the value = 0. And then in the Excel Connection ; I set the value = 0 for ExcelFilePath Property. I tried by keeping both Delay Validation = True (or) False – goofyui Jan 08 '19 at 18:39
  • I am getting error as " This error is thrown by Connections collection when the specific connection element is not found. " – goofyui Jan 08 '19 at 18:40
  • In the ExcelFilePath expression add the string variable (not "0") that was added at Index 0 of the Foreach loop. The value of the variable doesn't need to be set. – userfl89 Jan 08 '19 at 18:46
  • I removed the value 0. But there is no variable mapping is done in the Excel Connection . Do i set the Expression or Variable mapping ? – goofyui Jan 08 '19 at 18:47
  • Yes set the expression on the Excel Connection Manager for the ExcelFilePath property as the same variable that was used in the Variable Mappings pane of the Foreach loop. – userfl89 Jan 08 '19 at 18:49
  • I made the changes to have the variable. I am still getting the error : This error is thrown by Connections collection when the specific connection element is not found. – goofyui Jan 08 '19 at 18:51
  • If you changed the ConnectionString property before make sure to revert this back. – userfl89 Jan 08 '19 at 18:52
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/186375/discussion-between-goofyui-and-userfl89). – goofyui Jan 08 '19 at 18:54