OLE DB Source Dynamic Source Name

• ringovski

  Hall of Fame

  Points: 3521

  More actions

  February 5, 2021 at 1:59 am

  #3841260

  Hi All,

  Using VS 2017 I have a for each loop using a variable @currentfile to pass the file name of the excel file which changes every time it runs and the excel headers start in row 3 and column B to BO.

  I've tried using a excel source with the SQL command SELECT * FROM [IFAM$B3:BO] however the connection is not seeing the headers and so the column names are F1, F2...... even with the tick first row has headers.

  

   

  So I have switched to using OLE DB source which works and finds the headers.

  

   

  But how do I build a connection string expression with the variable file name. I tried a few different combinations and they don't work.

  OLE DB Connection String;

  "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="

  + @[User::CurrentFile] + ";Extended Properties="EXCEL 12.0;HDR=Yes;";"

   

  Thanks for any help.

• Site Owners

  SSC Guru

  Points: 80338

  More actions

  February 6, 2021 at 2:10 am

  #3841776

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.