Import multiple tables into SQL from CSV if existing in folder
In an extension to this question: Import Multiple CSV Files to SQL Server from a Folder
As the question states, I want to import multiple tables from CSV into SQL server from a folder. But the thing is in my case, is that I can have up to 17 different tables, that all have different layouts. Some have 2 columns, while others have 10 for instance.
Is it possible to create a script that takes into account the different tables setup based on the name of the CSV file, and import it correct if the table actually exist in the folder?
The good thing is that the CSV files never change names/layout. But not all 17 tables are used in every project.
I want the rough code along with proper comments so that I understand it.
Someone do please help me out on this one.
Thanks a lot in advance 🙂
One Solution collect form web for “Import multiple tables into SQL from CSV if existing in folder”
This is a perfect situation for using SQL Server Integration Services (SSIS), provided you have it available in your working environment. (I will assume you do, or can get it installed.)
SSIS is a huge platform in its own right so I cannot explain everything about how to use it here. Some good places to start learning about it are:
- The Wikipedia article
- The MSDN documentation
- The MSDN beginners’ tutorial
There are plenty of books and other websites available too.
Broadly speaking, you first need to create an SSIS “package”, which is a file of .dtsx extension. You create it on your local PC using Microsoft Visual Studio or Microsoft SQL Server Data Tools. It has a reasonably user-friendly graphical interface.
Within this package you will need to configure a data source for each of the 17 possible files, then “map” them to the corresponding tables in your target SQL database.
Provided you set it up appropriately, the package will work gracefully if some of the files don’t actually exist – it will load whichever are present. This is the crux of why SSIS is a good option in your use-case.
After creating the package, there are multiple ways to use/deploy it. Based on your comments and question, I think the best way for you might be:
- Upload it to an SSIS Package Store on your SQL Server (using SSMS Object Explorer, connecting to an SSIS instance rather than a SQL Server instance). You’ll need the SSIS components installed on the SQL Server platform for this.
- Create a SQL Job via SQL Agent, with a step that triggers the SSIS package. You might also want steps before or after that step, to run T-SQL code that prepares the SQL tables you will load data into, or does things to the data after loading (such as cleaning and validating it).
- Rather than running a SQL script to set this whole process off, you instead trigger the job to run, either manually (via SQL Agent again) or using the built-in scheduling facilities (e.g. it could run every night at 6pm).
Packages can also be saved as a kind of standalone executable, in any folder location, and run from within Windows with a double-click (without needing to open Visual Studio/SSDT again).