SQL Server - SSIS vs DTS Regarding Speed Loading Data

Asked By RMCompute

21-Jan-10 12:16 PM

I run an SSIS package in SQL Server 2008 which loads 39,028 records from an
Access table to a SQL Server 2008 table.  Using DTS from SQL Server 2000, it
runs in 1 minute to load data from the Access table to the SQL Server 2008
table.  When running from SSIS it was still running after 15 minutes and had
only loaded 13,394 records.   I calculated based on the current speed and
remaining records to load, it would have taken 44 minutes total.  In SSIS it
uses an OLE DB Connection for both source and destination.  It is run by
right clicking the package name and clicking Execute Package.  Am I missing
something? Does anyone have any ideas why DTS from SQL Server 2000 runs at a
speed multiples faster than SSIS?  Shouldn???t the more developed technology be
at least as fast or faster?   Also, if DTS works on the SQL Server 2008
database, what is the incentive to switch to SSIS?

22-Jan-10 08:53 AM

Your timing analysis lacks other significant factors or controls:
You were running the SSIS on your workstation, but I am guessing the DTS was
run from a server? Is the hardware comarable? What are the network hops and
limitations involved in each side of the test?

In one of my early attempts at SSIS, I replaced a two-hour DTS process with
20 minutes of SSIS.

Typically, SSIS should run faster than DTS, but there are cases where that
may not be so. Also, I have seen SSIS take a lot longer than a simple T-SQL
statement when moving data from one database to another on the same server.

DTS is not going to be around forever. It is supported in version 2005 and
2008, but there is no guarantee future version will include such support.
SSIS has many 'native' features that in DTYS required extensive coding, such
as Configurations, Logging, Error Handling, and Expressions.

Try this: Deploy your SSIS package to a server running SSIS and execute it
from there.

HTH
--
Todd C
MCTS SQL Server 2005