March 2, 2009 at 6:24 pm
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
March 3, 2009 at 3:31 pm
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.
March 3, 2009 at 7:00 pm
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
March 3, 2009 at 9:51 pm
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.
March 4, 2009 at 3:39 am
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.
March 4, 2009 at 5:05 am
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
March 4, 2009 at 9:28 pm
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
March 6, 2009 at 3:16 am
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.
April 19, 2009 at 3:39 pm
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
April 29, 2009 at 8:38 am
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 ???
April 29, 2009 at 1:00 pm
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...
May 3, 2009 at 4:42 pm
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
May 3, 2009 at 9:37 pm
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.
May 14, 2009 at 6:34 am
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.
January 31, 2013 at 9:19 am
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 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply