3

I have a huge amount of data in excel files, with at least 20 columns each file. I am working with .net (c#), my task is to import rows that met the conditions to insert data into SQL database, for an example, I need to insert only rows with current year (or selected year), also I have column name is 'Full Employee Name' I need to check it if it exists in table Resource Human.

Also other condition is to check if the column name is the same in the SQL table. I am succeeding to do it with code, but at least 200 lines to do all the possible checks. I read about SSIS (integration service, BI tool), and it looks that can help me to do my task.

My question how doing it? I am stacking with this new concept.

Hadi
  • 36,233
  • 13
  • 65
  • 124
Med Amin
  • 95
  • 1
  • 6
  • Usually you load all the contents into the database (a staging table) and then use a stored procedure to do all your validation logic. You may use SSIS to load the excel into the staging table, but there are many other tools also. – EzLo Feb 12 '19 at 14:22
  • Yes, that's what I am doing, the idea is I want to customize it without code, maybe mapping can help – Med Amin Feb 12 '19 at 14:37

2 Answers2

1

If you've already got a working .net solution, and 200 lines of code doesn't sound that bad to me, I wouldn't bother looking into SSIS to replace it.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • the problem that now I am working with 15 column, but I have some Excel files with more than 20 columns, also in the future I need to be able to customize which column imported – Med Amin Feb 12 '19 at 14:39
  • 1
    Well Excel is notoriously tricky to work with in SSIS. I think you're probably still better off keeping it in .net. Unless you want to first export your Excel files to .csv before importing with SSIS. – Tab Alleman Feb 12 '19 at 16:36
  • 2
    Yes, much better than with Excel. – Tab Alleman Feb 12 '19 at 17:55
1

I think that choosing the best approach is based on your needs:

  • If you are looking to create automated jobs and to perform data import from excel to SQL periodically, i think it is better to go SSIS
  • If you are trying to create a small tool that convert an excel file to SQL table, then working with .NET is fine
  • If you are looking to loop over Excel files with different structure, then you should use .NET or you have to convert files to .csv then use SSIS.

Also you can refer to the following Microsoft documentation for more options in importing Excel files to SQL: (SQL queries, Linked servers, OPENROWSET ...)

Hadi
  • 36,233
  • 13
  • 65
  • 124