FEL combining Excel Task Plus and Excel Source Plus

[Leigh Anne Duvall]

I have been able to use these independently very well, but I'm having a problem getting them to work together. My scenario is that I need to loop through a directory and retrieve each Excel workbook in that directory. Then, I need to loop through each workbook and pull out the individual worksheets.  I have set up nested FELs. I'm attaching 3 screenshots to show my scenario. I'd appreciate any thoughts or help.

I've got a ForEachLoop set up with "Foreach File Enumerator" and the Directory is set to a string expression. (I'm getting and setting the string from an entry in a SQL table. When I run my package with breakpoints, I show that the directory name is evaluating properly.) I set up a variable mapping to another string for WorkbookFilePath.

I'm using that WorkbookFilePath variable as the connection string for my Excel connection. Then, I have an Excel Task Plus task that takes that connection and writes the results of each workbook's worksheet list to a variable. When I only have one single workbook, it is perfect and loops through all 92 worksheets in that workbook. It's when I try to make it use a variable connection that it breaks.  My "FEL Workbook" is not setting the "WorkbookFilePath." Is the "Foreach File Enumerator" not the correct option here?

Thanks in advance for your help!

[Ivan Peev]

Hi Leigh Anne,

Right-click on the Excel connection manager and select Properties. Make sure RetainSameConnection=False. Try now to execute . Did it work ?

[Leigh Anne Duvall]

No, unfortunately. I tried that and it still fails with: "[Excel Task] Error: An error occurred with the following error message: "The connection "\\techdeptsql\Baylor\HSTM_TESTTHREE\Input_CodeConversions\WSCode_ConversionMaps.xlsx" is not found. This error is thrown by Connections collection when the specific connection element is not found.
". That is indeed the correct path and the spreadsheet is sitting there.

[Ivan Peev]

Leigh Anne,

You have not setup your expression on the correct place. You should not setup expression modifying the ExcelConnection parameter on the Excel Task. You have to setup an expression on the Excel connection manager which setups the connection dynamically.

[Leigh Anne Duvall]

Ivan,
I only tried that as a troubleshooting method when it wouldn't pick up the workbook. I have removed it and tried again. I only have the expression set on the Excel connection manager (in the tray) itself. Just like FEL1.jpg attached. When I turn on breakpoint and watch locals, it is getting the right connection string. When it gets to the Excel task to grab the worksheets, it isn't working with this setup. The array returned in to the variable only identifiers one sheet (sheet1) which isn't correct. It's not using the right workbook. When I don't do the FEL that sweeps for workbooks and I start just with the Excel task against a single workbook, it was grabbing all the 92 worksheets perfectly. There has to be something I'm missing. Where do you tell the Excel Task to look at the Excel connection manager that is set up with an expression? Putting it only on the Excel connection manager doesn't seem to be enough.

[Ivan Peev]

Leigh Anne,

You might be seeing only Sheet1 if you don't have permissions to the Excel file. Try to place the file on a local drive and see if it loads the correct sheets.

[Leigh Anne Duvall]

That is a local directory. It's just the full UNC path to it. I changed it to the drive letter instead and it's still not working. How do I tell the Excel Source Plus to use the connection manager (which is configured to a variable set by the earlier loop) but also tell it to use the appropriate worksheet being pulled by the Excel Task? Should the "Validate External Metadata" option be set to True or False in this case? I have tried it different ways and still can't get it to pick it up. I can get it working to either (a) use the right workbook based on the loop but not be able to read the worksheets or (b) be limited to a single workbook but have the Excel Data Task properly pick up the 90+ worksheets. I need it to do both. I'm so very close on this project. Can you think of anything else?

[Leigh Anne Duvall]

I finally got it to work, but I'm honestly not sure how. Right now, I have that Excel Connection manager set up with 2 expressions. One is for the connection string and one is for the Excel File Path. I have them both set to use the results of that first FEL workbook loop. It's working so I'm going to just go with it.