SSIS Importing Excel Data

Question

I've been tasked with importing data from an excel spreadsheet in to a table in SQL 2012. The spreadsheet will have data added to it monthly.

My plan, is to use SSIS to create a workflow to do this, I then will use SQL Job agent to execute the workflow at the beginning of every month to add in the new data.

One problem i can think of with this plan is the spreadsheet is going to become huge and eventually exceed the excel maximum rows. Instead of adding to the one spreadsheet I could have a new spreadsheet for each month? Though I'm not sure how I can use the workflow to pick the newest spreadsheet to add to the table

I'm a complete novice to SSIS, there might even be a more practical way of doing this whole process, so please feel free to offer suggestions.

Can you save the spreadsheet as a csv (flat file)? – Tab Alleman Apr 16 '19 at 14:01 — Tab Alleman, Apr 16 '19 at 14:01
Very good suggestion, thanks – Clem_Fandango Apr 16 '19 at 14:08 — Clem_Fandango, Apr 16 '19 at 14:08

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

1

Why inserting data into one Excel worksheet??

Inserting data into one Excel worksheet or even one workbook (Excel file) is not a good practice at all, you have to think in another way, you can create a new Excel file each time new data comes and save historical data in another repository or directory (if you need to). Or as @TabAlleman suggested if you can use flat files, it is more recommended since reading data from Excel is more difficult. But also make sure that you will not store all data in one flat file.

edited Jun 20 '20 at 09:12

Community

1
1

answered Apr 16 '19 at 17:22

Hadi

36,233
13
65
124

Thanks @Hadi I will take that info on board – Clem_Fandango Apr 16 '19 at 19:16

SSIS Importing Excel Data

1 Answers1

Why inserting data into one Excel worksheet??