CODEWITHSUNDEEP
sql-serverexcelvisual-studio-2012ssisetl
ANASI-Newbie
ANASI-Newbie

Reputation: 111

SSIS Package to pull data from multiple excel files - getting on error on 'Expression Properties'

Need some help please. I am getting Source Connection error when I try to make it dynamic.

I am using Visual Studio 2010, Excel 2010, SQL 2012 running on WIN 2008.

I am trying to do this in VS: Connection Manager (Source) – I have SQL 2012 and Excel 2010 / 2016

Steps:

  1. Rt Click on 'SOURCE Connection under Data Flow tab and Click Properties
  2. Under Expression Properties, Select String Property and click on expression icon and Create String statement for EXPRESSIONS (for dynamic folder) – I am using this expression

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +@[User::FileName]+";Extended Properties=\"Excel 8.0:HDR=Yes\";"

The above breaks Source Connection.. I am attaching screen shots

Please help. Thank you

Connection Error

Connection Error

Connection Error

Upvotes: 3

Views: 275

Answers (1)

Hadi
Hadi

Reputation: 37313

First

In extended properties why using : Replace it with ;

Second

For excel 2007 files and newer .xlsx you have to use Microsoft.ACE.OLEDB Provider instead of Microsoft.Jet.OLEDB

your connection string must be like the following :

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx; Extended Properties="Excel 12.0;HDR=YES";

you expression might look like:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +@[User::FileName]+";Extended Properties=\"Excel 12.0;HDR=Yes\";"

Upvotes: 1

Related Questions