CODEWITHSUNDEEP
sql-serverstored-proceduresssissql-server-2012data-warehouse
Justin
Justin

Reputation: 403

Loading procedure for Datawarehouse SSIS or Stored procedure?

We are working on Data warehousing project where we get data from different sources. We use SSIS packge to get data from source to warehouse server and load dimension and fact using Stored procedures. We may append more databases to existing warehouse that we are creating.

What would be easier process to manage SSIS packages and Stored procedures ? Would it be faster to call sp from SSIS package or deploy SSIS package to server and call both using SQL Agent Job ?

What we do in order to maintain SSIS package and SP's which will execute daily, weekly, Monthly ?

Upvotes: 2

Views: 900

Answers (1)

Marcus D
Marcus D

Reputation: 1144

As a general principle we use SSIS packages to manage execution logging, but to only be a 'wrapper' to call stored procedures - even in SQL 2012 & 2014.

Advantages are

  • SSIS is XML and doing code comparison/merging is impossible, so keep the complexity out of SSIS
  • SSIS is generally not optimal at set based operations. Native T-SQL is superior as a general rule
  • SPs can be code controlled very easily and doing diff/merge can be done quickly
  • Automated creation of SPs is very simple using meta data, as compared to automated generation of the same types of logic within SSIS (we are very much into meta-data generated systems)

As far as execution, we call the 'Master SSIS' package, which has a set of meta data that determines what child packages can be called when and what degree of parallelism you want. The meta data also contains information as to the schedule of the 'Master SSIS' package and what type of load it is daily, weekly, monthly, adhoc, etc.

Upvotes: 2

Related Questions