SSIS or T-SQL Stored ProcedurePosted: March 4, 2014
I have seen a solution to use dynamic SQL to migrate data into SQL Server database. But I feel the solution is pushing pretty hard in using T-SQL. It needs quite good of mastering of T-SQL skills to handle the customized task and for working with file names and scheduling, it becomes harder.
SSIS is a low cost ETL tool in SQL Server stack. It comes with many versions of SQL Server included into the server licence. It is easy to handle many tasks which may pose some difficulties with pure T-SQL approach.
I came across a summary of the comparison of these two from Infosys, which is a good source to have a quick overview of this topic.
Here is the link to that document: