Post reply

OLE DB Source Dynamic Source Name

  • 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.

    excel columns

     

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

    OLE DB 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.

  • 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.

  • 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/

     

     

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply