OLE DB Source Dynamic Source Name

• ringovski

  Hall of Fame

  Points: 3526

  More actions

  February 5, 2021 at 1:59 am

  #3841261

  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: 80342

  More actions

  February 6, 2021 at 2:10 am

  #3841777

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

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

• kaj

  SSCommitted

  Points: 1616

  More actions

  February 15, 2021 at 7:08 pm

  #3845338

  Instead of manually wrangling with the connection string in SSIS, you'd be better off modifying/overriding the connection attributes using the Expressions on the connection. Hint: you can select the ExcelFilePath keyword.

  See these tutorials for example (there are many more out there, just search for them):

  https://sqlserverrider.wordpress.com/2013/01/07/dynamic-file-name-for-excel-connection-manager-ssis/

  https://mikedavissql.com/2013/09/16/loop-through-excel-files-in-ssis/