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

ODBC in SSIS 2012

In August 2011 the SQL Server client team published a blog post entitled Microsoft is Aligning with ODBC for Native Relational Data Access in which they basically said "OLE DB is the past, ODBC is the future. Deal with it.". From that blog post:

We encourage you to adopt ODBC in the development of your new and future versions of your application. You don’t need to change your existing applications using OLE DB, as they will continue to be supported on Denali throughout its lifecycle. While this gives you a large window of opportunity for changing your applications before the deprecation goes into effect, you may want to consider migrating those applications to ODBC as a part of your future roadmap.

I recently undertook a project using SSIS2012 and heeded that advice by opting to use ODBC Connection Managers rather than OLE DB Connection Managers. Unfortunately my finding was that the ODBC Connection Manager is not yet ready for primetime use in SSIS 2012. The main issue I found was that you can't populate an Object variable with a recordset when using an Execute SQL Task connecting to an ODBC data source; any attempt to do so will result in an error:

"Disconnected recordsets are not available from ODBC connections."

I have filed a bug on Connect at ODBC Connection Manager does not have same funcitonality as OLE DB. For this reason I strongly recommend that you don't make the move to ODBC Connection Managers in SSIS just yet - best to wait for the next version of SSIS before doing that.

I found another couple of issues with the ODBC Connection Manager that are worth keeping in mind:

  • It doesn't recognise System Data Source Names (DSNs), only User DSNs (bug filed at ODBC System DSNs are not available in the ODBC Connection ManagerUPDATE: According to a comment on that Connect item this may only be a problem on 64bit.
  • In the OLE DB Connection Manager parameter ordinals are 0-based, in the ODBC Connection Manager they are 1-based (oh I just can't wait for the upgrade mess that ensues from this one!!!)

You have been warned!

@jamiet

Published Thursday, March 08, 2012 4:18 PM 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

 

Rafael Salas said:

Good information. Too bad that only user DSNs can be used. Heading to connect to add my vote...

March 8, 2012 2:48 PM
 

Peter Schott said:

Thanks for the info, Jamie. Voted.

March 8, 2012 3:53 PM
 

David Cohen said:

I've been using odbc on Sql 2008 and jump between 64 bit and 32 bit quite a bit.

Im 99% sure that the problem you are hitting is that visual studio (devEnv.exe/ Bids / whatever you want to call it)  is running as a 32 bit application.  Because it is a 32 bit app, it looks for the System DSNs created in ODBCad32.  

However, when you switch over, and execute the same package using dtsexec on your production server it will in fact run in 64 bit mode (assuming its in program files, not program file(x86) ).

The end result of all of this is as follows:

On dev Machines the DSN must be entered as a system dsn using odbcad32.

On Prod machines where the scripts will eventually be executed you'll need the DSN in odbcad (the 64 bit version).  

Remember 64 bit DSNs are different than 32 bit ones.

For simplicities sake, I always enter my DSNs in both places with identical names, so it will work both from DevEnv and DTSexec (regardless if I need to run dtsexec in 32 or 64 bit mode)

March 9, 2012 12:38 PM
 

jbooker said:

Havn't tried ODBC in SSIS, but David is right: on 64-bit machine the control panel gets you to ODBC admin tool for 64-bit.  32-bit apps will not see these DSNs.  

To add 32-bit DSNs on 64-bit machine you go here:

C:\Windows\SysWOW64\odbcad32.exe

Adding them with identical names in both places works fine.

Josh

March 13, 2012 1:32 PM
 

BloomF said:

Have you tried the same with ADO ODBC ?

April 11, 2012 8:51 AM
 

Andre Vella said:

Hi all, quick question ... has the use of parameters in an ODBC source (as with an OLE DB source) been "fixed" in SSIS2012 or I still have to use the expression builder from outside Data Flow?

April 25, 2012 12:14 PM
 

Emil said:

What about SSIS Lookups? I thought they support only OLEDB?

December 8, 2012 7:05 AM
 

Chris M said:

Hitting same issues myself.

I have to get data from MAS90 via an ODBC driver.

So using SSIS data flow task with ODBC source and SQL Server Destination.

I can issue sql commands like;

Select * From myTable Where Date > {d '2014-01-01'}

This works.

But there appears to be absolutely no way to parameterize that date.

Parameterizing the data flow task itself doesn't work - it just loses the variable name I enter.

Trying to use an Execute SQL task tells me I can't use disconnected record sets with ODBC. So it appears I'm screwed.

Doesn't look good when they actually ditch OLEDB.

If anyone has advice on how to achieve this in SSIS, I'd be grateful - haven't found anything that works online yet.

I'm guessing I'll try it as a linked server and do it all in T-SQL.

Every time I use SSIS I hit stupid limitations and find I can do the task much easier in T-SQL.

September 4, 2014 5:41 AM
 

Tom H said:

Running into the same issues as Chris M.  

No options to use a parameter or SQL Command from variable for an ODBC Source?  I don't understand how Microsoft can claim they're supporting ODBC with 2012 when they left off the basics.  My only practical option is to use OLEDB and OpenQuery.

September 10, 2014 10:19 AM
 

Dave Clark said:

Chris and Tom, you can set an expression in the properties of the data flow task.  (It is set on the data flow task on the control flow tab, not the odbc source on the data flow tab)

The property that you would want to set to use the expression is  [SqlCommand].

September 11, 2014 5:54 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

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