OK, finally got this working but with one minor correction.

That is, I needed to use an Execute SQL task vs. a Data Flow task to be able to specify the connection and SQL source type. I'm sure that was just a typo. But I dropped and recreated the worksheet table using two separate Execute SQL tasks, then ran the Data Flow task to an Excel destination.

Initially I had problems using my existing DFT to create the Excel output so I recreated the DFT and associated tasks; probably some internal metadata reference in the package was confusing it.

From there it worked as advertised.

1. Execute SQL task to drop the worksheet name. This cleared the worksheet although it still exists with that name. SSIS, however, believes that it's gone (try writing to it) 😉

2. Execute SQL task to create the worksheetname.

3. Data Flow task to an Excel destination via the same Excel connection.

There are several variations on this theme out there but this is what I wanted to do.

Thanks pss!

Matt