138 How to import any excel file and create table automatically in SSIS

How to import any excel file and create table automatically in SSIS

How to import any excel file and create table automatically in SSIS
How do I import an Excel file into a table in SSIS?
How to import data from an Excel workbook into an existing table?
How to loop through Excel files and load them into a database using SSIS package?

Happy Learning.

[MethodOverRide]

Lovely solution! Going to bookmark this one as I can see it coming in handy at work not only for excel but also other types of files.

Usually for excel I end up saving the sheets as individual tab delimited files and import them into SQL via SSIS. This may save me a step. How is the performance when you get up to 10k or 100k rows?

[venkatkavi]

Hello @Learn SSIS,
I have a developed a tabular model in SSAS and deployed into Azure Analysis service and generated a excel pivot table report.

Now, I need to edit the data in the excel pivot table report and those modified data needs to reflected into Tabular model.

How to do this?
Thank You.

[brianjohnson]

Can you do a video on how to loop through a directory and read the file name of a csv and based on the file name determine which table to write it to. For my example, I have a directory where 24 csv files are loaded daily, those 24 files are really multiple files of 6 types, the naming convention changes each day but has a similar component to each 1234_567_Person_890_123 or 2345_678_Address)901_234 and so forth. So there will be 4 files with person and 4 files with address and each one I would need to append to the appropriate table. How would I dynamically change the data source or direct the flow so that it would write to the appropriate table.

[DhanyaGowda-lm]

I’m not geetiny any error I have not used sql bulk copy actually I’m using oledbcommand for connection string as yhat provider is not supported I was getting exception How to fill that datatable

[Shankar-ziyk]

cant we do this with out script task because we cant simply copy paste your code with out knowing about C#

[HariKrishna-jivf]

Some times script task shows binary code errors

[amarnadh]

How to give specific sheet names in Excel

[manumys]

Thanks for this great video!

Could you please show me some examples of dynamic staging? I mean, I'm getting files from different locations with the same headers, but the order of the headers in each file is different. I need to load all of the files into a table that I've already created, and I need to map the headers in each file to the fields in the table.