Data Warehousing and Business Intelligence

28 February 2011

SSIS: SQL Server Destination or OLEDB

Filed under: SSIS — Vincent Rainardi @ 12:28 am
Tags:

We need to insert or update a SQL Server table using a Work Flow. Should we use SQL Server destination or OLEDB destination? This is one of the most frequently asked question among SSIS developers. OLEDB is used more than SQL Server, which doesn’t make sense. If the target is an Oracle table we can understand that. But this is a SQL Server table, of course it is better to use SQL Server Destination right?

A SQL Server destination has some things that a OLE DB destination doesn’t. The left one is SQL Server destination, the right one is OLE DB destination:

One of the most important things to notice here is that in terms of features, there’s not much difference. Both of them are suitable for loading data into a SQL Server table. If your table has triggers, then yes it matters. But who in data warehousing would create a dimension or fact table with a trigger anyway? Using the SQL Server Destination we can specify the last row, which is useful when you need to import only the first N rows (see my post here). But as the source is a SQL table anyway we can of course the Top N on our select statement. So again, there’s not much difference.

So the most crucial question is: which one is faster? To answer that I imported a file with 1 million rows, 2 columns int into a SQL Server table. First using a OLE DB Destination, then using a SQL Server Destination. Properties settings are all like above.

OLE DB Destination: 1st run: 3.229, 2nd run: 3.464, 3rd run: 3.978 seconds (without tablock it is 8.284, 8.330, 8.331)

SQL Server Destination: 1st run: 2.637, 2nd run: 2.574, 3rd run: 2.870 seconds.

So SQL Server Destination is faster than OLE DB Destination. SQL Server average is 2.694s, 24% faster than OLE DB which is 3.557s.

This runs on my Dell Inspiron 1545 laptop, Pentium Dual Core CPU T4500 @2.30 GHz each, 4 GB RAM, Windows 7 Home Premium 64-bit. Hard drive: WD5000BEVT, 500GB, 2.5″ x 1/8H SATA-300, 5400 RPM, external Data Transfer Rate 300MBps, average latency 5.5ms, seek time 2ms.

The main issue why SQL Server Destination is not used is not performance. The main issue is that we can’t edit the SSIS package on our PC. We have to edit the SSIS package on the target SQL Server, which is rediculous. If we edit the package on our PC we get an error message: “The selected data source is on a remote computer. The bulk insert operation can be executed only on the local computer.”

This Connect post explains it. On that post Bob Bojanic mentioned that Microsoft would be fixing it in SQL 2011, enabling us to use SQL Server Destination when editing the SSIS package on our PC. But a later post dated 13/6/08 from Microsoft said that they were not sure if the fix will be done in SQL 2011 (“open for triage”).

I still don’t understand why this restriction was there in the first place. And why it wasn’t issued as a 2008 patch, as to me the fix seems to be trivial: as Jonathan Bays said, “I would recommend removing this validation during design time and only put this restriction in at run time. If the package tries to load to a remote server, then it can throw an exception.”

Advertisements

1 Comment »

  1. […] an OLE DB Destination or SQL Server Destination (see my post here re why practically speaking we can’t use SQL Server Destination). But create the table first […]

    Pingback by Importing a File with Dynamic Columns « Data Warehousing and Business Intelligence — 5 March 2011 @ 9:25 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: