SSIS very slow compared to DTS

  • ahneave

    Say Hey Kid

    Points: 694

    I have a simple package in SSIS that runs extemely slow compared to a DTS package that does the same job – 10 minutes compared to 120 minutes. The packages import a file from AS400 into a new table using ODBC. Both the DTS and SSIS packages were created using the import wizards in SQL Server 2000 and SQL Server 2005 respectively. I have also created an SSIS package in BIDS and get the same results. All use the same ODBC DSN to connect to AS400.

    I have read forum posts about a known problem when using Table or View as the data selection method instead of SQL Query – I am using a simple ‘select * from {table}’ query.

    My problem may be related to hardware, or lack of it. SQL Server 2000 and SQL Server 2005 are running on the same server which has 2.8GHz dual processors, 3.0 GB Ram running Windows Server 2003 R2, SP2. SQL 2005 runs a named instance, but otherwise all default installation settings were used. There are no other users or applications running. When I run the SSIS package with the SQL 2000 service stopped, it still takes 120 minutes.

    Task manager shows CPU usage reasonably constant at about 28% throughout package execution.

    Memory configurations for SQL 2005 are:

    Min server memory: 0

    Max server memory: 2,147,483,647 MB

    Index creation memory: dynamic

    Minimum memory per query: Was 1024 KB, I changed it to 1,024,000 KB but it made no difference.

    Use AWE was not checked but checking it made no noticable difference.

    The package itself, created in BIDS (summary):

    Source Connection: Right-click Connection Managers -> New Connection-> ODBC -> New (select DSN from list)

    Destination Connection: New OLEDB Connection, enter server name

    Preparation Task: Create table statement.

    DataFlow Task:

    Source Query: SELECT * FROM {table}

    Destination: Data Access Mode=Table or View: Keep identity=unchecked: Keep nulls=unchecked: Table lock=checked: Check constraints=unchecked: Rows per batch=blank: Maximum insert commit size=0

    Can anyone help me? I was hoping to get a 30-40% reduction in process time using SSIS. There must be something I'm missing.

    Thanks

    AlisterN

  • ahneave

    Say Hey Kid

    Points: 694

    After further investigation I noticed that the database was in SQL Server 2000 compatibility level. Changing it to SQL Server 2005 hasn't made any difference.

  • Tim Mitchell

    SSCoach

    Points: 15652

    This is unlikely to make a huge difference, but try replacing your SELECT * with SELECT [column1, column2, ... columnN].

    Are you running SSIS directly on the server, or are you running it from your workstation? If you run it on a different machine, you have to consider network speed/latency along with hardware limitations.

    hth,

    Tim

    Tim Mitchell, Microsoft Data Platform MVP
    Data Warehouse and ETL Consultant
    TimMitchell.net | @Tim_Mitchell | Tyleris.com
    ETL Best Practices

  • ahneave

    Say Hey Kid

    Points: 694

    Even later...

    I created a linked server to the AS400 using the same ODBC DSN and can return all records in 13 minutes.

    This indicates to me that something is amiss with my SSIS package that takes 120 minutes.

  • ahneave

    Say Hey Kid

    Points: 694

    Thanks Tim...

    I'm running it directly on the server.

    Putting the fields individually into the query hasn't made any difference either - but thanks for the suggestion.

    I'm open to any suggestions at this point.

    It's comforting to know I have a workable solution using a linked server, but it would be nicer to do it with SSIS.

  • diego.piccolboni

    Old Hand

    Points: 368

    Hi,

    I have the same problem on AS400 with SSIS 2005...

    The performace compared to 2000 are very poor...

    so I have used dts in the SSIS...

    Have you other solutions in addition to the linked server?

    Thanks

    Diego

  • ahneave

    Say Hey Kid

    Points: 694

    No I don't have any other solutions at this stage. I fiddled a bit with OPENROWSET but didn't persist with it because it is academic only. I'll use linked-server if all else fails.

    This is what I used to create the linked-server:

    EXEC sp_addlinkedserver

    @server = 'MyLinkedServerName',

    @srvproduct = '',

    @provider = 'MSDASQL',

    @datasrc = 'MyDSN',

    @provstr = 'DRIVER={SQL Server};SERVER=AS400ServerName;UID=MyUserNameOnAS400;PWD=MyPwdOnAS400;'

    This site gave me some ideas: http://blogs.techrepublic.com.com/datacenter/?p=133

    BTW, how are you running your DTS packages from SQL Server 2005?

    Cheers

    AlisterN

  • chris.stuart

    SSCarpal Tunnel

    Points: 4965

    You mentioned that the select of records take 13 mins but the import 120. Have a look at using OLD Destination and set "Data Access mode" to "fast load"

    I know, this makes a huge difference, fast loads uses the bulk copy.

  • ahneave

    Say Hey Kid

    Points: 694

    Update...

    I have tracked the problem down to the transfer rate. The DTS package transfers at about 700 KB/sec but the SSIS transfers at about 75 KB/sec. They both run off the same box, across the same network to the same AS400 so I am thinking that the difference is in the ODBC connection.

    DTS uses an ODBC data source "Client Access ODBC Driver (32-bit)" using CWBODBC.DLL that has a description "iSeries Access ODBC Driver".

    SSIS uses a connection created as "ODBC" with the same data source as DTS (selected from a drop-down). However, when the connection is edited, the provider is displayed as ".Net Provider/ODBC Data Provider".

    Maybe the ".Net" layer is causing the bottleneck?

    Can anyone comment on this?

    I have also created a post on MSDN. See http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/162e55e5-b64b-423e-94c1-dd764ca1f683

  • Ol'SureHand

    SSCrazy

    Points: 2744

    Thanks for posting this - transfer rate may be the thing to watch for.

    I am in a similar but desperate situation, so any ideas will be appreciated.

    We have a data warehouse ETL in DTS that imports millions of records from 35 tables from iSeries AS400 in 30 minutes running on SQL2005 in "Legacy" mode. It selects fields and populates identical SQL tables.

    I rebuilt all the pumps into tasks on SSIS on a better SQL2005 server with faster disks and more RAM. It does the exactly same job in 5 hours.

    Attempts to run 3 concurrent dataFlow tasks resulted in "Connection busy" errors and abandoned INSERTS.

    SSIS uses OLE DB with the IBM-supplied Client Access drivers IBMDASQL (latest version) and Native SQL OLEDB. I cannot use DataReaders because the target tables have some "tinyint" fields, and for some strange reason I cannot use "bulkloading" SQL Server Destinations so I use the SQL OLEDB ones, but in other experiments using these it still took 2.5 hours to import 25,000 iSeries records....

    So what's the verdict?

    Should I waste another hundred hours flogging a dead horse or fall back to DTS ???

  • OzYbOi d(-_-)b

    Hall of Fame

    Points: 3944

    As yet, we have not migrated our ODBC source DTS packages...

    You may have already tried this, but to determine if the ODBC source is really the bottleneck, you could try something along the lines of this:

    1) Run package as is to determine baseline time for execution with the ODBC source and OLE DB Destination

    2) Remove the OLE DB Destination and replace it with a row counter

    3) Re-run the modified package (this will allow you to see where the performance loss is based on the change in execution time)

    on a second note, if you are transferring large records (high number of columns or large char/varchar columns), you may be able to squeeze a little performance out the package by modifying the FastLoadMaxInsertCommitSize...

  • ahneave

    Say Hey Kid

    Points: 694

    You've probably picked up my response to your post on the MSDN forum but for the benefit of other readers, I have decided to stick with SQL Server 2000 and my original DTS packages.

    Incidently, your estimate of '100 hours' is similar to the amount of time I have spent on this.

    Cheers

    Alister

  • Ol'SureHand

    SSCrazy

    Points: 2744

    I am running the DTS packages on a SQL2005 server - no need to keep the 2k one running! In fact it's the same SQL2005 server that takes 5 hrs to import the same data that DTS in Legacy mode imports in 30 minutes.

    And yes, it took 100 hours and 60 builds to get the SSIS package written AND running with the same 35 data pumps it had in its DTS counterpart.

  • ian.hadlington

    SSC Veteran

    Points: 232

    I had losely related problem with exporting records (using DTS, SQL Server 2000) to access.

    Using the 'default' access DB driver, the export took 40-50 mins.

    Setting up and using a standard ODBC driver, took under one minute.

    ... I'd suggest checking out the DB driver.

  • Edwin Silva-327074

    Valued Member

    Points: 60

    We have the same issue. We are installing SSIS 2012 in order to use ODBC. In our testing it gave us a drastic improvement in the execution time

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply