We have upgraded to SQL Server 2005 recently and I am in the process of
migrating the DTS packages to SSIS.

I have resigned myself to the fact that the creation of SSIS packages is more
difficult and complicated, but that this hopefully is made up for by having a
more capable and powerful tool. However; I am having a hard time justifying the
raw data transfer differences.

I have one particular table that is just over 2 million rows. My DTS package
pulls that table over in around 12 minutes. The SSIS equivalent package takes
over an hour and a half to pull this same table.

What could account for such a large difference? We are pulling this from an IBM
AS400 during low usage periods and the two boxes are connected over 1GB fiber
Ethernet.

We have to include a data transform step on EVERY text field because the middle
layer always protests about a conversion between Unicode and non-Unicode types.
Are these transforms (not required by DTS) the reason for the slow down? Are
there any other "known tweaks" that could speed this up?

TIA

Hello Rick,

There certainly should not be that much difference in performance.

Is this an upgraded package or a clean build?

Yes you will need to do conversions on the data.

Can you do this using a CAST on the Source Adapter rather than in the pipeline
itself?

If you look in the output window do you see any complaints about columns
not being used and their removal could speed things up?

Which part of the package is slow? Is it the extract from the source to
the Data Conversion or is it the Data Conversion to the destination?

You can use this transform to give you the metrics.

Row Count Plus Transformation
(http://www.sqlis.com/default.aspx?77)

Allan

Allan Mitchell wrote:
A can build. The only steps are to execute a SQL Statement to make a "start"
entry in a log table, then pull the table with "SELECT * FROM...", then a SQL
Statement to log "end".

I tried that on a different table that was complaining of errors in the data and
it seemed to me that the meta-data (type) of the source field was still being
used regardless of how I tried to cast the data in my SQL Statement. In this
case where they are simple Text fields I'm not sure how I could cast them
anyway.

At least with respect to pulling from an AS400 box this appears to be a bug in
SSIS. ALL text fields are interpretted as being Unicode (even if they're not)
and all of them need to be transformed to non-unicode (even if the destination
table is set up as nVarChar).

No. These initial extracts are all "raw" in that we want exact copies of the
tables on the AS400. A subset of these extracted tables are then processed
further with other packages.

I don't know how to tell that. The rowcount numbers on the two steps always
move in unison. Does that tell me something? I can monitor the rows being
accessed on the file on the AS400 and they are definitely processed much slower
from the SSIS package.

Thanks, I will look at that.

I should add that these are "typical" AS400 legacy tables that were designed for
processing via RPG programs. They are not in normal form and are much wider
than a proper relational database table would be. The fact that they are slower
than the narrower tables also being pulled is not a surprise, but I would expect
that to be similar between DTS and SSIS.

How much overhead does the Unicode to non-Unicode conversion incur? The fact
that these tables are very wide with lots of text fields is what makes me
suspicious that the conversion is where the bottle-neck is.