How to set file source via Parameter in SSIS?

Question

I have multiple packages for each location site and each site contains 5 other sub sites for which data has to be populated every month. Hence I have approx 20 excel files as a source data for which the excel file acts as a source and I need to create an excel destination. Because of so many excel file as a source I have multiple Excel connection Managers (Source + Destination) across my various packages.

The source file name and the location will change everytime and so I was thinking to automate the file path via parameter. I follow the steps mentioned in the article: https://blogs.msdn.microsoft.com/sqlgardner/2015/06/18/ssis-tip-using-a-file-path-parameter-for-multiple-flat-files/

I followed every step but I got the error when I open the excel file (source). Below is the error snapshot:

Can someone guide me what exactly I am doing wrong here?

Answer 1

Looks like you're using an expression on the excel connection manager "ConnectionString" property. When that property is changed to just a file path that isn't a valid connection string for that type of connection.

With excel file connections it's a little different and you'll want to use the ExcelFilePath property, which in turn is used by the connection manager when building out the connection string.

With Flat File Connections, yes you would use the ConnectionString property, Excel Connections, slightly different, use the ExcelFilePath property.