SQL Server:SSIS vs Stored Procedure

SSIS(SQL Server Integration Service)

An enterprise data integration, data transformation and data migration tool that is built into Microsoft’s SQL Server database. It can be used for a variety of integration-related tasks, such as analyzing and cleansing data and running extract, transform and load (ETL)processes to update data warehouses.

 

SSIS is a great data ETL (Extract – Transform – Load) tool, specially when your business deals with heterogenous sources like reading a budget from Excel,

validating currency rates against an Oracle db, writing financial data to SQL Server, reprocessing an SSAS cube and finally sending a

CSV to your bank. Most of these activities would be -at best- quite difficult if you’re sticking with pure T-SQL code.

On the other hand, T-SQL works best when all your data resides within MS-SQL borders, giving you a higher level of control than SSIS.

T-SQL lets you write the exact SQL statement you want the engine to execute, contrarily to SSIS who hides them under a GUI wizard.

SSIS vs Stored Procedure

  1. Advantages of using SSIS packages over stored procedures
  2. When to use T-SQL or SSIS for ETL

  3. ETL Tool(ex: SSIS)適用於不同RDBMS的資料處理,但SP只適用於SQL Server。
  4. Stored Procedure vs ETL Tool

參考資料

  1. Microsoft SSIS (SQL Server Integration Services)

  2. SSIS Package vs Stored Procedures
Advertisements

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

您的留言將使用 WordPress.com 帳號。 登出 / 變更 )

Twitter picture

您的留言將使用 Twitter 帳號。 登出 / 變更 )

Facebook照片

您的留言將使用 Facebook 帳號。 登出 / 變更 )

Google+ photo

您的留言將使用 Google+ 帳號。 登出 / 變更 )

連結到 %s