THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Destination Adapter Comparison : SSIS Video Nugget

In this, my latest video nugget, I demonstrate and compare three ways that you can insert data into a SQL Server table from the SSIS DataFlow:

  • OLE DB Destination without Fast Load
  • OLE DB Destination with Fast Load
  • SQL Server Destination

The demo peels back the covers to see how these three methods actually operate against the database. The video is embedded below however at the time of writing we are still having issues with getting videos to show up on SQLBlog meaning that the video may appear too small to view hence in the meantime I’ll refer you to view the video on Vimeo at http://vimeo.com/9806121 (it is 7m19s long).

If after watching this you want to know any more then you may glean some useful information from my blog post SSIS: Destination Adapter Comparison from August 2006 or feel free to post any questions in the comments below and I’ll do my best to answer them!

Hope this is useful!

@Jamiet

Published Tuesday, March 02, 2010 8:11 AM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

David Charron said:

Thanks! Great tutorial :)

March 2, 2010 10:32 AM
 

ihafidh said:

I noticed that the Maximum Insert Commit Size was set to 2147483647 on the OLE DB Fast Load. I thought that maybe you set that number to get better performance. After looking into it a bit, it seems that number is the default on SQL 2008. In SQL 2005 it defaults to 0.

Do you know if this change was to increase performance?

March 2, 2010 12:52 PM
 

jamiet said:

ihafidh

That is a very very good question and I will endeavour to find out the answer.

-Jamie

March 2, 2010 12:56 PM
 

jamiet said:

ihafidh is already aware of this but anyway....

If anyone wants to know *why* the max commit size has changed to 2147483647, take a read of this: http://sqlblog.com/blogs/jamie_thomson/archive/2010/03/06/default-value-for-ole-db-destination-fastloadmaxinsertcommitsize-in-sql-server-2008.aspx

May 4, 2010 4:21 AM
 

SSIS Junkie said:

Introduction The Dataflow task is one of the core components (if not the core component) of SQL Server

January 13, 2011 5:04 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement