SSIS OLE DB deprecation... what to do about it?

Question

Hi

I know that Microsoft announced deprecation of OLE DB for SQL Server but what does it mean for SSIS Developer? I've started doing SSIS Tutorials YouTube and thought I will find this out and mention it... as it sounds like an important one ;)

Questions I have:

1) Is OLE DB is deprecated in next release than would I have to upgrade existing connections?

2) I think ADO.NET runs on top of OLE DB? If yes than I have the same question as question 1 for ado.net as I suspect the answer will be the same?

3) What about Lookups? I thought they support only OLEDB?

4) What connections should I use? ODBC? Is there anything else that can connect to SQL Server? 

5) What are drawbacks of ODBC for SSIS? Any extra considerations for development / deployment?

Many thanks in advance!

Emil

Answer 1

This post from Matt Mason of the SSIS team answers your concerns:

http://blogs.msdn.com/b/mattm/archive/2012/01/09/sql-server-ole-db-deprecation-and-integration-services.aspx<o:p></o:p>

"In SQL Server 2012 SSIS, you can continue to use OLE DB connections and will need to for components such as the Lookup transformation and the Slowly Changing Dimension transformation that require OLE DB. In the post-Denali release, you’ll be able to upgrade and continue to operate these packages without needing to do additional work to explicitly remove OLE DB from
the packages."<o:p></o:p>

---

Marco Schreuder IN2BI DWH Deck

Answer 2

Hi

I'm not sure if this answers all my questions... Upgrade? Upgrade to what? Is it going to "upgrade" to ODBC? It seems currently there are issues and limitations that I described by Jamie blog post and in comments http://sqlblog.com/blogs/jamie_thomson/archive/2012/03/08/odbc-in-ssis-2012.aspx

Also in my questions I have next release of SSIS in mind... the one after SSIS 2012... Currently it seems that we can "migrate" to ODBC (existing or new packages) but it has limitations and sounds like it is better just to wait and see what's will be available in next release and just continue using ole db or ado.net

Regards

Emil 

Answer 3

I thought I'll share my findings.... Microsoft suggested to use ODBC instead of OLE DB but I wonder maybe this recommendation is not for SSIS...Has Microsoft explicitly mentioned SSIS?

Microsoft mentioned they will deprecate OLE DB but this is applicable only to SQL Server and in SSIS we also have .net SQLClient that connects to SQL Server only and does not use OLE DB (if I'm not mistaken?).... maybe SQLClient should be used to extract data from SQL Server.... which this forum comments seems to indicate http://forums.asp.net/t/1728247.aspx/1?Which+one+is+best+Odbc+or+Sql+Client+

... and we get @Param support with SQLCLient instead of ? which is what I liked about ado.net (ole db)... this is getting confusing for me ;)

Regards

Emil

Answer 4

Don't switch to ODBC yet... it will be the (renewed) standard in the future versions of SQL, but at the moment OLE DB to SQL Server is much, much faster than ODBC to SQL Server. And a lot of components still only work on OLE DB (like the Lookup you mentioned). I think it will take a couple of years before we need to switch...

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Answer 5

Hi 

Many thanks for the blog post. 

What about .net SQLClient? The blog talks about ODBC in general terms (not specifically ODBC for SQL Server?). In that case maybe SQLClient should be deprecated as well? If not what would the purpose of it?

I just typed in Google... SQLClient deprecated.... and I think I got my answer which probably conflict a little bit with the other comments but it seems to make more sense to me ;) Deprecation Announcement

"If you use SQL Server as your RDBMS, we encourage you to use SqlClient as your .NET Provider.  In case you use other database technologies, we would recommend that you adopt their native .NET Providers or Managed ODBC in the development of new and future versions of your applications.  You don’t need to change your existing applications using OLE DB, as they will continue to be supported, but you may want to consider migrating them as a part of your future roadmap. Microsoft"

Regards

Emil

Answer 6

Hi

I think I got most of my answers... My approach will be most likely to use .net sqlclient (instead of OLE DB.. mainly as I prefer @Param) and just see what happens...and below are answers from my point of view.

1) Is OLE DB is deprecated in next release than would I have to upgrade existing connections?

Most likely... but no details yet.

2) I think ADO.NET runs on top of OLE DB? If yes than I have the same question as question 1 for ado.net as I suspect the answer will be the same?

.net can use OLE DB but also there is sqlclient (which I will use) and .net odbc (which I won't use)

3) What about Lookups? I thought they support only OLEDB?

Just have to wait and see?

4) What connections should I use? ODBC? Is there anything else that can connect to SQL Server? 

I will use sqlclient from now on and in the next several years I will find out if that was good decision or not ;)

5) What are drawbacks of ODBC for SSIS? Any extra considerations for development / deployment?

It seems there are... slower than OLE DB? Does not support all features... require DNS but can be made DNS-less (preferred for me).

Many thanks for your input!

Emil

Answer 7

This thread should be made a sticky, because I can imagine a lot of people having the same questions in the future.

---

MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

Answer 8

Hi

I made simple tests and it seems I will stick to .net sqlclient in most cases.

Different Connections simple read performance test

Different Connections simple write performance test

Take care

Emil