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.

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 :

In English

Exception has been thrown by the target of an invocation

Answer 1

Finally, my script works.

The database engine ACE 12.0 was not installed on my local machine. I downloaded the .exe package from Microsoft website then I changed the execution mode of my package from 64bit to 32 bit because the installed Microsoft Office version is a 32bit version (Run64BitRuntime to FALSE in project settings).

Here is a post about how to run a 32 bit package in a 64 bit environnent.

Answer 2

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());