SSIS importing Excel files xls/xlsx

Question

I have a requirement of importing information from an excel file to a database. I have a webpage that runs an ssis package, that picks up an excel file, and loads data into a database.
The problem now lies, in the different types of excel files to be processes either xls or xlxs.

SSIS excel connection manager, lets you specify which type of excel file, you will be connecting to either xls or xlxs, you can not use one connection manager for both types, this now only allows the user to always change an xlxs file to xls, then process it, is there a way to dynamically change the connection manager, based on the type of excel file, or should i just have two different SSIS packages called, when a different type is processed.

Answer 1

In SSIS 2008, you can set a Connection to a 2007 Excel file (.xlsx) and then use an Expression on the Connection Manager to set the ExcelFilePath to be the value of a variable. The value of this variable can be either type, 97-2003 (.xls) or 2007 (.xlsx) and the Excel Source will work, as long as the Sheet names are the same.

I'm not sure if this is the same behaviour in SSIS 2005.

Answer 2

If you are running the ssis package from code already, I would imagine this should be relatively easy to do. I have been fiddling around with editing packages from code over the last week or so and it is pretty easy to modify variables etc. I know you can also access the connections and specify a dtsConfig file

                using (var p = app.LoadFromSqlServer(config.PackageName, config.SqlServerName, config.UserName, config.Password, null))
            {

                // changing variables in code
                Variables vars = p.Variables;
                vars["FromDate"].Value= criteria.From;
                vars["ToDate"].Value = criteria.To;
                // using a configfile in code
                p.ImportConfigurationFile(config.ConfigurationFile);
                DTSExecResult result = p.Execute();
                if (result != DTSExecResult.Success)
                {
                    throw new ApplicationException("SSIS Package did not compelte successfully.");
                }

            }

You could potentially have 2 different config files one for xlsx and on for xls connections and use the appropriate config file based on the uploaded excel files extentsion.