Import each Excel File to a new SQL Table using SSIS

Question

I have more than 50 Excel file and i want to import each one of them to an new SQL table in SQl server with the name of the table is the name of the excel file How can i achieve that using SSIS ?

each file have its own column names. so i need a way to loop over each files in the folder and get the data of each file into new sql table

This might be a job for BIML, depending on whether it's quicker to learn BIML, or quicker to just slog through and create each data flow manually. — Nick.Mc
– Nick.Mc, Commented Apr 19, 2017 at 22:58

Wendy · Accepted Answer · 2017-04-19 19:54:25Z

1

This just gives you a general idea what could do.

Create a staging table in SQL Server

CREATE TABLE dbo.ExcelStaging
(LineId INT NOT NULL IDENTITY(1,1)
, FileLine VARCHAR(2000)
, FileName VARCHAR(50)
)

Truncate staging table before import each of your file.

Create script task, read every line of your file and insert into staging table colume ‘FileLine’. LineId=1 should always hold the original file column header. You must careful choose which delimiter, comma separator or fixed width?

Once file loaded into your table, execute SQL task – a stored procedure

a. based on the filename and FileLine where LineID = 1, create a new SQL table. The new table may look ugly – every column data type is varcher, since don’t know advance what data type is for each field.

b. Insert into new table select substring of FileLine as columns from staging table. Here the delimiter you choose will determine how you substring for each column.

answered Apr 19, 2017 at 19:54

Wendy

6601 gold badge4 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

LattaliAhmed Over a year ago

nice idea, but i need to keep the column data type the same as Excel

Wendy Over a year ago

Ok, you need to create the tables before importing files. By read file name and first line of file content, how are you or computer able to decide what data types are?

LattaliAhmed Over a year ago

the import/ export wizard in SQL server can do that, my goal is to do the same thing in SSIS without creating each table manually.

Collectives™ on Stack Overflow

Import each Excel File to a new SQL Table using SSIS

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related