SSIS very slow compared to DTS

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » SSIS very slow compared to DTS

15 posts, Page 1 of 2

1 2 »»

SSIS very slow compared to DTS

Rate Topic

Display Mode

Topic Options

Author

Message

ahneave

Posted Monday, March 02, 2009 6:24 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, January 31, 2013 11:43 PM
Points: 36, Visits: 233

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

Post #666993

ahneave

Posted Tuesday, March 03, 2009 3:31 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, January 31, 2013 11:43 PM
Points: 36, Visits: 233

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.

Post #667710

Tim Mitchell

Posted Tuesday, March 03, 2009 7:00 PM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 9:12 PM
Points: 1,034, Visits: 2,611

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
SQL Server MVP
www.TimMitchell.net
@Tim_Mitchell

Post #667780

ahneave

Posted Tuesday, March 03, 2009 9:51 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, January 31, 2013 11:43 PM
Points: 36, Visits: 233

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.

Post #667826

ahneave

Posted Wednesday, March 04, 2009 3:39 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, January 31, 2013 11:43 PM
Points: 36, Visits: 233

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.

Post #667984

diego.piccolboni

Posted Wednesday, March 04, 2009 5:05 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, September 15, 2009 9:06 AM
Points: 20, Visits: 224

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

Post #668021

ahneave

Posted Wednesday, March 04, 2009 9:28 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, January 31, 2013 11:43 PM
Points: 36, Visits: 233

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

Post #668874

chris.stuart

Posted Friday, March 06, 2009 3:16 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:11 AM
Points: 198, Visits: 635

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.

Post #670037

ahneave

Posted Sunday, April 19, 2009 3:39 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, January 31, 2013 11:43 PM
Points: 36, Visits: 233

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

Post #700270

Ol'SureHand

Posted Wednesday, April 29, 2009 8:38 AM

Old Hand

Group: General Forum Members
Last Login: Monday, June 10, 2013 1:35 AM
Points: 357, Visits: 706

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 ???

Post #706890

« Prev Topic | Next Topic »

15 posts, Page 1 of 2

1 2 »»

Permissions