Unable to use expression on excel connection manager in SSIS 2017

▲
3
▼

I'm trying to loop through excel files in a directory and perform a data flow task in SSIS.

The For-Each Loop container seems pretty simple to set up:

For-Each Loop

I map to a variable called FileNameTemp.

For-Each Loop 2

Inside the For-Each Loop, I have a data flow task where the source object is an Excel Source with an Excel Connection Manager. I use the FileName temp to set the File Name of the ExcelFileName:

Excel File Manager

My problem is whenever I try to run the package, I get the error below:

[Connection manager "Excel Connection Manager"] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft Access Database Engine" Hresult: 0x80004005 Description: "Failure creating file.".

I found other similar posts. I definitely have permission to write to this folder. If I remove the expression and just open the same file over and over it works. I also set DelayValidation to true on pretty much every level.

ssis
asked on Stack Overflow Oct 30, 2018 by James Franklinton • edited Oct 30, 2018 by miselking

2 Answers

▲
2
▼

Try removing the "C:..." from your expression definition. The For-Each file enumerator will give the full path.

In the future you can set a breakpoint on your data flow task and view the value of your variable that you set in the locals tab.

answered on Stack Overflow Oct 30, 2018 by Mike Baron
▲
1
▼

Same answer, just more verbose than @mike Baron's answer is that in the ForEach Loop Container, the radio button is checked for "Fully Qualified" with the result pushed into our variable @[User::FileNameTemp]

Each file found in the specified source folder C:\SourceCode\ExcelSourceFinancialReconcilliation is in turn going to be assigned to that variable in the form of

  • C:\SourceCode\ExcelSourceFinancialReconcilliation\file1.txt
  • C:\SourceCode\ExcelSourceFinancialReconcilliation\file2.csv
  • C:\SourceCode\ExcelSourceFinancialReconcilliation\file2.xls

Then, when we set the Expression on the Excel Connection Managers ExcelFilePath property, we need to just use @[User::FileNameTemp] As it stands, the expression is doubling up the path so that Excel is attempting to find 

C:\SourceCode\ExcelSourceFinancialReconcilliation\file1.txt\C:\SourceCode\ExcelSourceFinancialReconcilliation\file1.txt

As a general rule, only use a direct variable in the Expressions associated to "objects" in SSIS. Property1 = @Variable The reason for this, is that you cannot put a break point to on the evaluation to determine why @Property1 = "Foo" + @Variable is invalid. If you create a custom variable @Property1Variable = "Foo" + @Variable and then assign @Property1 = @Property1Variable, you can put a breakpoint in the package and then inspect the value of the SSIS variable. It's much easier to find problems this way.

Possibly helpful other answers on the subject

https://stackoverflow.com/a/18640174/181965 https://stackoverflow.com/a/21536893/181965

answered on Stack Overflow Oct 31, 2018 by billinkc

User contributions licensed under CC BY-SA 3.0