Data Flow Task with variable source

As a general answer, no, you can't do this with SSIS. Since you tagged this with C# however, you can use OLE to add sheets to an Excel file and add data to those sheets, http://jayadevjyothi.blogspot.com/2013/01/step-1-create-new-project.html . This can be done outside of SSIS, or if your solution needs to run inside SSIS, you can put the C# inside a script task.

           // Excel file path
        string excelFilePath = @"F:\Excel File.xlsx";
        // Connection string for accessing excel file
        string connectionString = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0}; Extended Properties=""Excel 12.0 Xml;HDR=YES""", excelFilePath);

        using (OleDbConnection Connection = new OleDbConnection(connectionString))
        {
            try
            {
                Connection.Open();
                using (OleDbCommand Command = new OleDbCommand())
                {
                    Command.Connection = Connection;
                    Command.CommandText = "CREATE TABLE [Students] ([First Name] Char(200), [Last Name] Char(200), [Age] Char(2))";
                    Command.ExecuteNonQuery();
                    Console.WriteLine("Table Created Successfully");
                }
                Console.ReadLine();
            }
            catch (OleDbException ex)
            {
                Console.WriteLine("Message: " + ex.Message);
                Console.ReadLine();
            }
            finally
            {
                Connection.Close();
            }
        }