Best Practice for using multiple Data Flows 1

[M8tt]

I'm in the process of rebuilding a 2000 DTS package in 2005 and am hoping for some advice on the best way to use the Data Flow to do this.

In my 2000 DTS I have around 30 tables which are being transferred from 1 SQL database to another SQL database using 30 seperate Transform Data Tasks. I understand how to replicate this in 2005 but what I'm not clear about is whether it would be best to:
a) have 30 seperate Data Flow tasks (1 per table)
b) have 1 Data Flow task with 30 Transformations in it
c) group a number of Transformations into a number of tasks

Or whether this makes no difference and is just down to personal choice.

There are no dependencies between any of the tables and in the 2000 DTS I run a number simultaneously.

Many Thanks in advance, Matt

 

[MDXer]

Actually this makes a huge difference in that a single Data Flow is limited on either the number of records in a buffer or the amount of memory used by the buffers.

SSIS Has 2 configurations:

DefaultMaxBufferRows - By default this is set to 10,000 rows and is configurable to increase or decrease the number of rows.

DefaultMaxBufferSize - By default is 10MB and is configurable to a Maximum of 100MB, this upper limit is built in to SSIS.

You should not configure either of these values without fully understnading how the relate to eachother and over all performance of your package.

As you can see the more source to destination processes running within a data flow the faster your maximum values will be hit. your best options are 1 data flow per import or 1 package per import then a master package that calls the child packages in a predefined order.

Here are a couple usefull links in understanding SSIS Dataflow optimization.

Integration Services: Performance Tuning Techniques
SQL Server 2005 Integration Services: A Strategy for Performance

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck