THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is an author and engineer who enjoys building and automating data integration solutions. Andy is co-host of the Data Driven podcast. Andy is no longer updating this blog. His current blog is AndyLeonard.blog.

How Many SSIS Packages Should I Create?

This blog has moved! You can find this content at the following new location:

http://andyleonard.blog/2009/12/28/how-many-ssis-packages-should-i-create/

Published Monday, December 28, 2009 8:00 AM by andyleonard

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Steve said:

for DB2, this is what I do :

http://blog.stevienova.com/2009/05/20/etl-method-fastest-way-to-get-data-from-db2-to-microsoft-sql-server/

I have 2 packages, an incremental and full load. And then about 5-6 other packages that just execute the full/incremental and pass in the db2 table name and it pulls it down. I group the packages by db2 tables in JDE (that is the source system I pull from), so something like, Sales, Inventory, Purchasing, GL, etc

December 28, 2009 7:52 AM
 

ken ambrose said:

I would add that SSIS/ETL programming shares similiar design issues with conventional procedural language programming: Support reuse, architect for reliability, maintainability, testability.

For example, in the past I have had to create SSIS processing that I was certain could be reused with simple variable or config file changes. In those cases, I put that code in a seperate package so it can be reused easily in many ETL solutions.

December 28, 2009 4:42 PM
 

brucem said:

Andy,

In the example above where you would have several packages, do you have a "main package" that runs all the other packages sequentially or do you schedule each package to run independently?

Thanks,

Bruce

December 29, 2009 1:15 PM
 

andyleonard said:

Hi Bruce,

  That is an excellent question and the answer is "it depends." I definitely automate some way to execute the packages in a prescribed order. I sometimes use a Parent Package (main package) to execute the Child Packages, sometimes I use a scheduler like SQL Agent and call each Child Package in its own job step.

:{> Andy

December 29, 2009 2:35 PM
 

Bruce said:

Hi Andy .. I've almost similar question.

I need to extract data from 15 tables.Few tables are big tables (having millions of rows). There is no FK constraint between the tables in source DB. Do i need to add all the flows in one data flow task or should i break them for improved performance.

Thanks

November 30, 2011 4:11 AM
 

Vikas said:

This is a very old page but still I want to understand what if my package with 8 data flow task(with some transformations) takes 10 mins to complete(as you have mentioned if it take 3 hrs). but still if I want to go ahead with parent child design then is it ok?. Personally I prefer modular and clean code approach so i would like to use single package per table approach.

What would you suggest?  

October 6, 2015 11:52 PM
 

andyleonard said:

Hi Vikas,

  Did operations and support, yep. Create a package for each data flow.

:{>

October 7, 2015 8:34 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

News

My Latest Book:

Community Awards



Friend of Red Gate

Contact Me

Archives

Privacy Statement