dynamic excel connection in ssis

• Shree23

  SSC-Addicted

  Points: 497

  More actions

  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

  

   

  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";

   

  

• Thom A

  SSC Guru

  Points: 99137

  More actions

  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

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  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.

  ---