How to open an OLEDBConnection through SSIS script task?

Question

I need to process Excel files saved in a specific folder.

In my SSIS package I added a Foreach loop configured as a File Enumérator, populating a filepathvariable. Then, a script task use this variable to open the Excel file and process it.

enter image description here

However, I'm not able to open the OLEDB Connection to my file in the script task. The filepathcontains the valid path. I added a test in my script to check the file.

Here is a sample of my code :

// Check file to process.
string rawfilePath = Dts.Variables["User::FilePath"].Value.ToString().Replace(@"\",@"\\");

if (rawfilePath == String.Empty || !File.Exists(rawfilePath))
            Dts.Events.FireError(0, SCRIPT_TASK_NAME, "Invalid input file '" + rawfilePath + "'...", String.Empty, 0);

MessageBox.Show(rawfilePath);

// Open connection.
string rawFileConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='"
 + rawfilePath + "';Extended Properties='Excel 12.0;HDR=NO;IMEX=1'";

MessageBox.Show(rawFileConnectionString);

OleDbConnection rawExcelConnection = new OleDbConnection(rawFileConnectionString);
rawExcelConnection.Open();

My folder is C:\TestFolder. It contains two files : C:\TestFolder\export_20140101.xls and C:\TestFolder\export_20140102.xls.

Here is the error :

enter image description here

In English

Exception has been thrown by the target of an invocation

Do your message boxes fire? Which line is failing, the instantiation of rawExcelConnection or the Open call? — billinkc
Run it in debug mode or put your entire code into a try catch block, and use Dts.FireError with the exception message in the catch block — jazzytomato
The try/catch block saved my life !. The error was about the database engine (ACE 12.0). The driver was not found, I never installed it before. — K4timini

SFrejofsky SFrejofsky · Accepted Answer · 2014-11-14T17:54:14

There is no need to replace the "\" with "\\" as you have with the check file to process.

The Compiler recognizes this when you are converting a DTS variable.

The compiler is recognizing your path as C:\\TestFolder\\export_20140102.xls

When that happens it sees two empty directories and can not return a value.

When the compiler reads a DTS variable it reads it as a literal string.

It should work if you change

string rawfilePath = Dts.Variables["User::FilePath"].Value.ToString().Replace(@"\",@"\\");

to

string rawfilePath = Dts.Variables["User::FilePath"].Value.ToString());

How to open an OLEDBConnection through SSIS script task?

2 Answers