Importing Excel File

• dreamslogic

  Ten Centuries

  Points: 1131

  More actions

  July 16, 2009 at 9:17 pm

  #136184

  Hi Guys,

  I am looping through excel file by using the Foreach File enumerator. However after selecting the new Excel connection manager properties window, In the Property Expressions Editor I selected property as connection string and entered following expression.

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

  But excel is not loading it's giving me error message as there is no valid object. When I remove the expression it works fine.

  Can anyone shed some light on this issue.

  Thanks,

  Rahul

• hussain27syed

  Ten Centuries

  Points: 1191

  More actions

  July 17, 2009 at 12:19 am

  #1026024

  i would advise you to use ExcelFilePath instead of ConnectionString in your expressions.

  Are you getting this error when you tried to use it in a data flow task?

• Phil Parkin

  SSC Guru

  Points: 246960

  More actions

  July 17, 2009 at 12:34 am

  #1026026

  Also check that the BudgetFilePath variable has been set so that it points to a valid file so that SSIS's meta-data validation does not fail.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• COZYROC

  One Orange Chip

  Points: 28499

  More actions

  July 17, 2009 at 11:51 am

  #1026365

  Double-quote the variable like this:

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

  Your path probably contains spaces.

  ---
  SSIS Tasks Components Scripts Services | http://www.cozyroc.com/