We are deciding between bulk insert or SSIS. We have a simple spreadsheet with 5 columns, customerid, cost, date, product name, etc. The spreadsheet is generally only around 200 rows every week.
All we do is import the flat file CSV into a simple table, no transformations or anything. After , we conduct few calculations in T-SQL. We could care less about performance, considering only 200 rows. At the end, we want customer to run stored procedure, which imports csv, and conducts automatic T-sql calculations.
What is the best option for easy use and simplicity? Seems like more dependency with getting out of the T-SQL window, creating the SSIS package, and then calling the ssis package in t-sql. At the same token, I hear Bulk insert is old and not be used anymore.
I researched few articles SSIS vs bulk insert for performance, but have a question about simplicity.
Best Answer
Using bulk insert is the simplest solution.
Using SSIS will require a development environment with visual studio, installing additional components like SSDT, designing the DTS package and configuring the execution of the DTS (on a SSIS server or directly via dtexec). Although this takes time, it will give you a lot more of flexibility to work later on if your requirements change.
If your task is as simple as loading a CSV or text file to a table in SQL just use bulk insert, you can do any additional transformation with SQL on a staging table and being only 200 rows (even if it increases a little), you shouldn't have any performance issues.