|
|
|
SSC Rookie
 
Group: General Forum Members
Last Login: Tuesday, February 23, 2010 3:43 PM
Points: 32,
Visits: 160
|
|
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
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Tuesday, February 23, 2010 3:43 PM
Points: 32,
Visits: 160
|
|
| 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.
|
|
|
|
|
SSC Eights!
Group: General Forum Members
Last Login: Wednesday, March 03, 2010 7:42 PM
Points: 965,
Visits: 2,186
|
|
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
twitter.com/Tim_Mitchell
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Tuesday, February 23, 2010 3:43 PM
Points: 32,
Visits: 160
|
|
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.
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Tuesday, February 23, 2010 3:43 PM
Points: 32,
Visits: 160
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Tuesday, February 23, 2010 3:43 PM
Points: 32,
Visits: 160
|
|
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
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, February 18, 2010 9:42 PM
Points: 100,
Visits: 243
|
|
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.
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Tuesday, February 23, 2010 3:43 PM
Points: 32,
Visits: 160
|
|
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
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Today @ 6:04 PM
Points: 230,
Visits: 473
|
|
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 ???
|
|
|
|
