Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Importing SQL Server Data Using SSIS - Which Option is Fastest


By:   |   Last Updated: 2012-07-13   |   Comments (28)   |   Related Tips: More > Integration Services Development

Problem

This article is useful for SSIS developers who do not know which tasks are best to use in an SSIS projects. The main problem is that at the end of development if performance is slow then you will need to rebuild the project and change components. This article shows various ways of importing data and shows which types of components perform best within SSIS. The contest will be between the following components:

  • ODBC Tasks
  • ADO NET Tasks
  • OLEDB Task
  • SQL Server Destination
  • T-SQL Tasks
Solution

I created different SSIS packages in order to test performance. In this demo I used SSIS 2012 and the database Adventureworks 2012.

In this demo I am going to import the table [AdventureWorks2012].[Sales].[SalesOrderDetail] to the test2 database which is on the same instance of SQL Server. SalesOrderDetails is the table with more rows in AdventureWorks2012.

In order to create the database test2 and the destination table dbo.OrderDetails, use this T-SQL code:

CREATE DATABASE Test2
GO
USE Test2
GO
CREATE TABLE [dbo].[OrderDetails](
 [SalesOrderID] [int] NULL,
 [SalesOrderDetailID] [int] NULL,
 [CarrierTrackingNumber] [nvarchar](25) NULL,
 [OrderQty] [smallint] NULL,
 [ProductID] [int] NULL,
 [SpecialOfferID] [int] NULL,
 [UnitPrice] [money] NULL,
 [UnitPriceDiscount] [money] NULL,
 [LineTotal] [numeric](38, 6) NULL,
 [rowguid] [uniqueidentifier] NULL,
 [ModifiedDate] [datetime] NULL
) ON [PRIMARY]
 

Test 1 - ODBC Tasks

The first example will use ODBC Source and ODBC Destination as shown below:

the ODBC Source and the ODBC Destination

When we run the package we notice the average time is 5 minutes 57 seconds to import the rows:

it takes 5 minutes 57 seconds to import the rows

Test 2 - ADO NET Tasks

As noticed, ODBC is pretty slow. Let's try another approach. We are going to truncate the destination table first:

TRUNCATE TABLE test2.dbo.OrderDetails
 

Let's try ADO tasks to import the same data and verify if these components are faster:

ADO tasks

The average elapsed time in my testing was 11 seconds. This is much better.

Test 3 - OLEDB Tasks

This time we are going to import the same data using the OLEDB Tasks. Again we will truncate the table in the test2 database first.

OLEDB tasks

The average elapsed time is 5 seconds.

Note that I am using the "fast load" option with the "Table Lock" option in the OLE DB Destination Task:

I am using the fast load option with the tablelock option in the OLE DB Destination Task

If we do not use the fast load option, the average elapsed time was 2 minutes and 21 seconds:

If we do not use the fast load option, the elapsed time would be 2 minutes and 21 seconds

OK. The fast load option really improves performance. I will return to that configuration. What about the OLE DB Source. By default I am using the option "Table or view" in the OLE DB Source as shown below:

OLE DB Table or view

Let's use a "SQL Command" instead as shown below.

SQL Command

The average elapsed time is 2.85 seconds.

Test 4 - SQL Server Destination

Now, let's try to use the SQL Destination as the destination instead of OLE DB Destination:

SQL Destination

The average elapsed time is 2.5 seconds.  At this point it is the best option.

Test 5 - Execute T-SQL Task

Finally, some people think that the best option is to use the Execute T-SQL Task:

T-SQL

I am using a simple insert statement to import data from one source to another:

USE [TEST2]
GO
INSERT INTO [dbo].[OrderDetails]
     ([SalesOrderID]
     ,[CarrierTrackingNumber]
     ,[OrderQty]
     ,[ProductID]
     ,[SpecialOfferID]
     ,[UnitPrice]
     ,[UnitPriceDiscount]
     ,[rowguid]
     ,[ModifiedDate])
SELECT 
     [SalesOrderID]
     ,[CarrierTrackingNumber]
     ,[OrderQty]
     ,[ProductID]
     ,[SpecialOfferID]
     ,[UnitPrice]
     ,[UnitPriceDiscount]
     ,[rowguid]
     ,[ModifiedDate]
FROM [AdventureWorks2012].[Sales].[SalesOrderDetail]
 

The average elapsed time is 1.8 seconds! 

Finally I've been told that if the query runs inside a stored procedure it is even faster:

Let's create a stored procedure:

 CREATE PROCEDURE insertOrderDetails 
as INSERT INTO [dbo].[OrderDetails]
([SalesOrderID]
,[CarrierTrackingNumber]
,[OrderQty]
,[ProductID]
,[SpecialOfferID]
,[UnitPrice]
,[UnitPriceDiscount]
,[rowguid]
,[ModifiedDate]) SELECT
[SalesOrderID]
,[CarrierTrackingNumber]
,[OrderQty]
,[ProductID]
,[SpecialOfferID]
,[UnitPrice]
,[UnitPriceDiscount]
,[rowguid]
,[ModifiedDate]
FROM [AdventureWorks2012].[Sales].[SalesOrderDetail]

After creating the stored procedure we are going to call it in the Execute T-SQL Task:

T-SQL properties

The average elapsed time is 2.12 seconds. The stored procedures does not improve performance.

Summary

Let's review the table with the results:

Position

Time in seconds

ODBC Tasks 357
ADO Tasks 11
OLEDB 2.85
OLEDB, SQL destination 2.5
T-SQL 1.8

The winner is: T-SQL

You may think the moral of the story is to use the Execute T-SQL Task instead of other SSIS tasks.  In this example we were importing data on the same instance, but this will not always be the case.

So the moral of the story is that there are many alternatives when creating a SSIS project and we have to carefully study the alternatives in different scenarios. There are great SSIS tools and we do not always use the best options. With each new version of SSIS new tasks are added and performance may be improved with existing tasks. The main changes in SSIS for SQL 2008 and 2012 are related to performance improvements.

Next Steps

 If you are working in a SSIS project make sure you are using the best tasks and also verify if there are other SSIS tasks that can be used in your project.

Also make sure you are following the best practices recommended by the experts:



Last Updated: 2012-07-13


next webcast button


next tip button



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, March 16, 2018 - 4:44:37 AM - dhaval patel Back To Top

 This tip really helped me.

 

Thanks a lot!!!!


Friday, May 30, 2014 - 11:41:45 AM - karl Back To Top

Nice article however, you do not get a performance gain on a stored procedure until subsequent runs or executions.  SQL Server will put its execution plan in the procedure cache and it will get faster over time.  Not the first time it is run...


Tuesday, April 22, 2014 - 8:13:31 AM - Olawale Back To Top

hi Daniel,

Thank you so much for this article. I have a question though.

 

I have being trying to load data into a databse i created but having difficulties when it comes to the contraints in the table.

 

Please can u explain the order in which a database data can be loaded into a database putting in the consideration the referential and contraint keys

 

Thanks and looking forward to you replying


Friday, January 03, 2014 - 9:56:40 AM - Omiel Back To Top

Thanks. The SQL Command worked for me


Monday, May 20, 2013 - 10:40:05 PM - Vani Dornadula Back To Top

How to execute insert,delete,update statements using Execute sql-task with example?


Monday, May 20, 2013 - 10:37:03 PM - Vani Dornadula Back To Top

How to send data to sqlserverDestination using OLEDBSOurce with example database?


Tuesday, April 02, 2013 - 4:33:24 PM - Johanna Carolina Tarazona M. Back To Top

excelente articulo, Daniel me resolviste una gran duda q poseia

 

excelled article, Daniel solved a big doubt that possessed


Thursday, October 11, 2012 - 10:35:05 AM - Daniel Calbimonte Back To Top

I would prefer a linked server.

If you are using Oracle I heard great things about the Attunity Connectors.

People said that the performance is really great.

 


Thursday, October 11, 2012 - 4:25:43 AM - Sherlee Back To Top

 

Hi Daniel,

Nice article just in time to my needs for now.

Does the source and destination of data matters ?

Like for example if my source is from an Oracle database then I need to update or insert record to MS SQL database.

Which is much better to use in my TSQL stored procedure using OPENQUERY()(linked server connection)  or using OpenRowSet() (without using linked server).

I would highly appreciate any advice on this.

 Thanks.

 

 


Friday, September 28, 2012 - 1:04:14 PM - Akinja Back To Top

 

Great read and analysis, but I have one caveat to add.  If you need to move a large amount of data, you need to take care of the transaction log growth.  This is not a much of a concern using SSIS.  For instance, I needed to move 1.3 billion rows (15 columns) and began using TSQL which quickly filled my logs.  However, using OLE DB Source and Destination (Bulk Inserts) with fast load, there was little impact to the log file. 

 


Thursday, September 20, 2012 - 9:19:12 AM - vinodhkumar Back To Top

Its very useful. great job.Thanks


Monday, August 27, 2012 - 10:54:42 AM - Orlando Colamatteo Back To Top

I agree with some others that the testbed is a bit contrived. If you're looking to move data from one table to another on the same instance then SSIS will rarely be a viable option. Some form of T-SQL will almost certainly outperform an SSIS operation. A more realistic scenario is moving data between two disparate data sources.

It is surpising how poorly the ODBC Destination performs, especially in light of what Microsoft has publicly said in that they will be moving away from OLE DB interfaces and standardizing on ODBC in future products:

http://social.technet.microsoft.com/Forums/en/sqldataaccess/thread/e696d0ac-f8e2-4b19-8a08-7a357d3d780f

In the ODBC Destination I expected Microsoft to implement the loading of data via the bulk load API as they did with the FastLoad option of the OLE DB Destination.

----

On a separate note regarding loading data into MySQL with SSIS:

In the past I did some performance tests with the Cherry City OLE DB driver for MySQL and it is horribly slow as it only inserts one row at a time. This is not to mention the fact that it crashed BIDS regularly when developing with it. Given the lack of a benefit I would stick with the tools built into SSIS and avoid the hassle of installing and configuring a third party driver. 

If you're using SSIS 2005 I would recommend using a Script Component as a Destination and issuing batch-inserts against a connection made using the MySQL ODBC Driver: http://msdn.microsoft.com/en-us/library/ms135939.aspx

If you're using SSIS 2008 I would recommend using an ADO NET Destination with the MySQL ODBC Driver. In my tests it was only able to achieve about 240 rows/minute throughput to MySQL which is quite disappointing: http://msdn.microsoft.com/en-us/library/bb895291(v=sql.105).aspx

If you're using SSIS 2012 I would recommend using an ODBC Destination with the MySQL ODBC Driver. In my tests it outperformed the ADO NET Destination over 3 to 1 but still only achieved about 800 rows/minute throughput, which was still quite disappointing: http://msdn.microsoft.com/en-us/library/hh758691(v=sql.110).aspx

 

Thursday, July 26, 2012 - 6:38:11 AM - Rajesh S. Chandan Back To Top

Excellent analysis. Really enjoyed your article. Good work !


Wednesday, July 25, 2012 - 12:09:38 AM - Daniel Calbimonte Back To Top

I've heard that this provider is great:

http://cherrycitysoftware.com/ccs/providers/ProvMySQL.aspx

 


Sunday, July 22, 2012 - 11:16:15 PM - rockwell Back To Top

Boris Tyukin

thanks for the info,  yes i have tested the attunity connector but i want to use mysql as the database, any pinput on mysql will be helpful in this case.

 


Thursday, July 19, 2012 - 11:30:05 PM - Daniel Calbimonte Back To Top

There are great OLEDB connectors on the web. ODBC is the worst option. 


Thursday, July 19, 2012 - 8:16:22 PM - Boris Tyukin Back To Top

rockwell,

google "Microsoft Connector for Oracle by Attunity with SQL Server Integration Services" - it is totally free and based on our tests it was 10-15 times faster than Microsoft oledb oracle connector. I could not believe my eyes when i first tried it. Also works great on x64 servers.

As for mysql, I have no experience with mysql and ssis - sorry.


Thursday, July 19, 2012 - 6:58:40 PM - Daniel Calbimonte Back To Top

Use the native connectors


Thursday, July 19, 2012 - 4:42:37 PM - rockwell Back To Top

@boris 

that would be great to know if you are doing the testing currently that is what iam trying to do.

oracle -->mysql any idea?

odbc is too slow

.net connector seems to have some connectivity bug which they havent fixed in their release.

can anyone suggest me any other way in this case.


Thursday, July 19, 2012 - 12:10:55 PM - boris Back To Top

great post but what would be best here is too have your targer and source database on two different servers and using linked server to to T-SQL scenario.

Often times you will load data from another server - I can hardly remember when was the first time I needed to pull data from one database to another on the same server.

In that scenario, most people will be torn between using a linked server via T-SQL or using SSIS. That would be a great test and will compliment this post for sure


Wednesday, July 18, 2012 - 3:12:50 AM - laxman Back To Top

It's very useful   thanks Daniel


Tuesday, July 17, 2012 - 2:30:49 AM - Shamas Back To Top

It is enjoyment reading this but in the end we learn a lot. Thanks Denial


Saturday, July 14, 2012 - 9:39:50 AM - harry Back To Top

I think if U try on more the 1 mil rows with alot of fieldx


Friday, July 13, 2012 - 7:33:41 PM - Duncan Back To Top

 

Very insightful, Thanks Daniel


Friday, July 13, 2012 - 4:22:33 PM - klauss Back To Top

Nice Round Up.

Thanks for you test.


Friday, July 13, 2012 - 12:24:35 PM - Pavel Back To Top

Great analysis and demonostration. Enjoyed reading this.

I think the ultimate winniner is "Test 4 - SQL Server Destination" because most likely we will export or import data to different instance.

 


Friday, July 13, 2012 - 8:36:50 AM - John Back To Top

THANKS Daniel


Friday, July 13, 2012 - 8:28:20 AM - sifiso ndlovu Back To Top

great read, thanks Daniel


Learn more about SQL Server tools