SQL Bulk import vs SSIS in Simplicity

Question

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.

https://www.mssqltips.com/sqlservertip/4241/data-import-performance-comparison-tsql-vs-ssis-for-large-import/

Answer 1

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.