Creating tables in SSIS from flat files

SSIS and Import/Export
1

For the life of me I can't remember how this is done. I have an excel file as destination and want to create table from it dynamically. Funny thing is I was messing around with it yesterday and it somehow created a sql script to create the table. I don't know what I done so can't replicate it and have had to do a bit of a rebuild since and removed a lot of the workflows.

I have 4 tables/4 sheets to create. There is potential for the Excel files to be changed, i.e. new columns etc. so want to have it dynamic, although if it picked up changes it would be overwriting the table with all data in it, so not sure maybe this is best idea going forward, but want to at least be able to do it first run.

Thanks
Andrew

Thanks fo any useful info.

Andrew

2
  1. How often do you get these files?
  2. Do you want to create a new table every,time you get a new file?
  3. These tables you want created are they just for staging purposes or are they final tables (latter not recommended)
3
  1. Every day
  2. I'm not sure what would be best way to be honest
  3. I think in the whole they would be staging, we have to take 2 flat file reports, merge them, then do a look up with 2 look ups as well.

but I'm thinking it would be daft replacing the table each day, however there is the potential that fields may change as we implement it. It's being done on the fly as there is a rush as it's COVID absense reporting for the whole hospital, and they need it every morning.