SSIS vs. SQL Stored Procedure

Recently I was asking by the customer to redesign a sequence of stored procedures that were used to load and transform data. This because the performance was not as expected due to the increasing of the mount of data.

Here the observations I will share with you:

• Use SSIS to extract and load data from one or mutliple sources to one destination or multiple destinations.
• Use a stored procedure to update the data instead of using e.g. a SCD component.
• Use SQL Statement for grouping, filtering or sorting a query result instead of e.g. a Sort component
• If the data to join comes from different sources then extract and load first the data in tables of the same e.g Staging area and then use a SQL Select statement where the join conditions will be indexed.

A right indexation and statistics will be the key of success!