dynamic excel connection in ssis

  • Hi Expert,

    i wanted to user variable name in connection string . I tried [User:IntegrationFIle]

    in table or variable name property of excel connection string but did not worked

    Screenshot 2022-04-08 022312

     

    also tried entering in connection manager properties but did not work

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=@[User::IntegrationFile].xlsx;Extended Properties="EXCEL 12.0 XML;HDR=YES";

     

    Screenshot 2022-04-08 022154

  • You've not set the connectionstring property to be an expression, you've set it to be literally the value Provider=Microsoft.ACE.OLEDB.12.0;Data Source=@[User::IntegrationFile].xlsx;Extended Properties="EXCEL 12.0 XML;HDR=YES";, and your file path to be literally @[User::IntegrationFile]. You need to use the expressions pane to do this (which you can see the option to access in your bottom screenshot).

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Create a variable which contains the entire connection string (not just the file path).

    Map that variable to the ServerName property of the connection manager.

    At least, that's how I do it.

    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

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

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