title: I want to parameterize a connection to an excel file. But the location of the file is in a table in sql server. How can i stablish the connection?
body: <p>Hi I want to parameterize a connection to an excel file. But the location of the file is in a table in sql server. The table has 2 columns. The first is the id and the second contains the location in the form of C:/.../.../. The idea is that when I insert new rows in the table, the connection will be updated. I tried to create a parameter with the name "connection" in the package and in the value I wrote the command sql to bring me the last location in the table. Then I open the excel source editor and in the data access mode I put "sql command from variable". Then I chose the variable-parameter "connection", but the connection is not established. Maybe I have to bring the field, store it in a variable using a sql task and then use it in "expressions" in the properties of the excel connection manager.  It tells me: "the microsoft jet cannot open the file. It is open in exclusive mode by another user or you need permission to see its data" and the second error is "cannot acquire connection from connection manager". Is the method I am using correct?</p>