Currently working on SSIS. Now want to know which one of the "SQL Server Destination" and "OLE DB Destination" is faster? I think we can not use SQL Server Destination until and unless our target server and deployment server is same. So 'OLE DB Destination' looks like a more generic option. But still want to confirm if they can have significant performance difference. If SQL Server destination is much more faster than OLE DB, then have to change my package accordingly. Also as we have the option for bulk insert in Sql server destination, where we can specify to lock the table during insert, I could not find that kind of options in OLE DB. Is there any other technique through which we can make OLE DB destination runs fast. Any insight will be of great help. Thanks !!!

That is an interesting question and I would like to get the lowdown on this. Historically I had a bad experience with SQL server destination. Looks like it is bug prone, What can work on dev might not necessarilly keep working reliabily when deployed on prod. Will fail some or all the time. Even if all the SQLbuilds are the same. Given this bad experience I simply stay away from SQL Server destination. Never use it, never get hard to debug bugs. Every single package where I tried to use SQL Server destination where sooner or later bogus, I reverted them to OLEDB. OLEDB works just fine all the time in all servers. I am not sure there would be any noticeable diffrerence of speed between the two. OLEDB will move data very fast, even for multimillion rows extract and withno bugs. That said, the OLEDB destination let you lock the table while writing to it and you can also set the Rows per batch and Maximum insert row commit size (limit transaction log size) property. I think it even a must in SQL 2008. BTY, What is supposed to be SQL Server destination for? Is that something one would use in conjunction with specific recordsets? and why is not it working?

The SQL server destination uses shared memory to connect to the SQL server database. It also has some SQL Server specific optimizations that allow pushing data faster to the database (using insert Bulk). As a result, it will be faster than the OLEDB provider, but has more restrictions. Most important restriction is that it will only work on a local server. So if your production environment has SSIS package running on a different server than the destination server, use Oledb.

When the destination is on the same machine as the source, then use SQL Server Destination else always use OELDB destination. SQL Server Destination is quite fast as compared to OLEDB but only in the case I mentioned.

The gains that you get in speed (Some, but not a lot) are generally not worth the gains that you get from the portability of the package using the OLE DB connections.

As stated, in order to use the SQL Server Destination you need to be loading the data on the same server. If you use this destination type it wont make porting this package in the future as easy since you would have to change your destination to OLE DB if the SQL Server destination is not the same server anymore. Iread a blog posting by Donald Farmer a while back stating that you can get a 5 to 10% increase in performance using this destination type. Unfortunately I no longer can track down this blog posting to post the reference link here. You can see the reference of the posting in searches on the Internet, but the posting doesn't appear to be available anymore. You can simply use the 'Fast Load' data access mode on the OLE DB destination type and perform your bulk load operation this way. You need to be careful though because if it does get a lock on the table to perform this operation then the table will not be available for users to access during the load process. If it can not get a lock on the table it will still load the data. Just something to be aware of.

Fluffy Bunnyfeet wrote: So if your production environment has SSIS package running on a different server than the destination server, use Oledb. That's it. I cannot get ssis to work with Oracle in 64 bits soI have ssis on another server that is a 32 bits, hence SQL Server destination does not work for me. BTY Did anyone tried to pull Oracle data with ssis on a SQL2008 64 bits installation? I do not have this yet however I am considering it. I tried Oracle 11 g data provider on aSQL2008 32bits installand this is only crashing BIDS 2008. http://www.oracle.com/technology/tech/windows/odpnet/index.html

* Install Oracle 32 and 64 bit drivers, 11g i think, maybe first install 64 bit then 32 bit * Install Attunity Oracle provider 64 bit * Copy Visual Studio directory to new dir: C:\ProgFilesX86\ Should work now both in designtime (BIDS) and runtime (SSIS). Oracle is sensitive to () in the path of the calling app so everything under Program Files (x86) will fail. After normal installation of SQL Server, simply copy VS dir and start devenv.exe from there. Use Attunity:s .NET provider in SSIS. Really fast and stable. // Martin

Yeah, Got all the 64 bit stuff in check. Just that Attunity connector is not allowing variables as SQL Source, only direct code or expression so I cannot use it when I have queries over 4000 Chars (expression limit) and queries tend to get bigger overtime, even if they only have facts and keys. As for the SQL Server destination, I am back on a situation where it is local so I just have to maintain a ddl shell of the database on my development workstation so I can develop these packages. When ready, I do the final test with data load directly on the dev server.Thanks/Regards, Philippe Cand

Hi All, I have a package with some conditional split transformations. When I used OLEDB Destinations as Data Flow Destination, I got this error in the middle of execution :The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020 But If I change OLEDB Destinations with SQL Server Destinations (And Set the time out to 500), it solves the problem (I don't get any error messages and all the records are inserted to the destination table). Does anybody know why it can be happen? and Can we set timeout in OLEDB Destination? Thank you Regards, Ratna

Here is the blog link http://www.bidn.com/blogs/BrianKnight/ssis/770/why-not-use-an-ssis-sql-server-destination text from the blog "So, the main reason that all experts including myself have preached the use of the SQL Server Destination was for performance. Thanks to some recent performance tests and input from the dev team at Microsoft, we've found that the performance results can be erratic with a 5% margin on the postitive or negative. Going forward, this destination may even be removed in later versions. Make sure you no longer use this fairly popular destination."