Post reply

dynamic excel connection in ssis

  • April 7, 2022 at 8:55 pm

    #4012343

    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

  • April 8, 2022 at 8:00 am

    #4012568

    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

  • April 8, 2022 at 8:00 am

    #4012569

    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