I have two models of EXCEL files with many sheets and thousands of EXCEL files filled according these two models.
I have an SQL SERVER Database.
I don’t know what the best solution for importing the data from EXCEL to SQL SERVER is. I planned to write a C# code because of several reasons:
• The import has to be automatic by verifying whether files with proper name are present in a given directory;
• The data have to be checked before being imported and the import of the entire EXCEL file has to be abandoned if some data is not good
• There are many foreign keys:
o Each sheet of the first EXCEL file has a column named hole_name
o Each sheet of the second file has a column named hole_name and a column named sample_name.
o The database has the following structure:
¿ table HOLE : hole_id (PK IDENTITY column), hole_name, …
¿ table SAMPLE: sample_id (PK IDENTITY column), hole_id (foreign key), …
¿ Many tables linked to HOLE: table_id (identity column), hole_id (foreign key)
¿ Many tables linked to SAMPLE
• Some data have to be transformed, for example:
o Yes and No in EXCEL transformed in bit 0 or 1 (boolean) in SQL SERVER
o Some code of six characters in EXCEL sheet transformed in the ID corresponding to the code in a dictionary table in SQL SERVER
• A report has to be written in order to say whether the import was successful or not and if not, why …
If C# is used, which are the best components, to connect to EXCEL, to connect to the database, ... ?
Could SSIS be used? Or would another way be more convenient?
Thank you for your help.