Data Flow Task Error: The version of ODBC Source is not compatible with this version of the DataFlow

Question

Data Flow Task Error: The version of ODBC Source is not compatible with this version of the DataFlow

bikerdadHLV

SSCommitted

Points: 1567
More actions
August 30, 2017 at 9:07 am

#345899

So, that is the error I am getting when I attempt to execute my extremely simple SSIS package. How simple? Grab data from NetSuite (Oracle) table (ODBC Source), load it into SQL Server table (OLE DB Destination). No transforms, no lookups, nothing but grab and load. The Connection Manager is using the same ODBC connection as some other processes we have that are working.
The package works fine in the SQL Server Data Tools design/dev environment. The package has been built using SSDT in Visual Studio 2015, built for and deployed to SQL Server 2016. I get the same problem whether I build the package on my workstation and deploy it to the Integration Services Catalog on the server, or build the package on the server and then deploy it to the Integration Services Catalog. I am running the package using 32bit runtime.
DataFlow Task:Error: The version of ODBC Source, clsid{9EAC76F9-8D61-4F12-9E7B-529CF31765A1} is not compatible with this version of the DataFlow.

So basically, I can't run it in Integration Services Catalgo using 64 bit runtime on the server, and I can't run it NOT using 64 bit runtime. I can run it in SSDT using 32bit runtime, fails on 64bit. I am stumped....

Tom_Hogan Hall of Fame Points: 3873 More actions · Answer 1

Is your database not on the 2016 compatibility level?

At my last place, we ended up installing the NetSuite driver on the Integration Services box, creating a DSN, and leveraging the DSN for the ODBC source. We had no issues in SQL 2012 or when we upgraded to 2016.

bikerdadHLV SSCommitted Points: 1567 More actions · Answer 2

I'm not sure what you mean by "Is your database not on the 2016 compatibility level?" The database is SQL Server 2016, the package is built for TargetServerVersion SQL Server 2016. The SchemaBuild info for the SSISDB is 13.0.1601.5. Our NetSuite drivers are version 7.20.54

We have both the 32 bit and 64 bit NetSuite drivers installed on this server. If I deploy and run the package with Run64bitRruntime = True, I get this error:

Sue_H SSC Guru Points: 90860 More actions · Answer 3

bikerdadHLV - Thursday, August 31, 2017 11:02 AM
I'm not sure what you mean by "Is your database not on the 2016 compatibility level?" The database is SQL Server 2016, the package is built for TargetServerVersion SQL Server 2016. The SchemaBuild info for the SSISDB is 13.0.1601.5. Our NetSuite drivers are version 7.20.54
We have both the 32 bit and 64 bit NetSuite drivers installed on this server. If I deploy and run the package with Run64bitRruntime = True, I get this error:

DataFlow Task:Error: The version of ODBC Source, clsid{9EAC76F9-8D61-4F12-9E7B-529CF31765A1} is not compatible with this version of the DataFlow.

So basically, I can't run it in Integration Services Catalgo using 64 bit runtime on the server, and I can't run it NOT using 64 bit runtime. I can run it in SSDT using 32bit runtime, fails on 64bit. I am stumped....

You can set the compatibility level of a database to be compatible with lower versions, similar to the database being on a lower version of SQL Server. You can execute the following to check the compatibility level of the database:
select name, compatibility_level from sys.databases

Sue

bikerdadHLV SSCommitted Points: 1567 More actions · Answer 4

Thank you Sue. The Compatibility level for the SSISDB on the server running the package is 130. Compatibility level of the destination database (on another server) is 100. The latter shouldn't matter, as it's the ODBC Source that's the failure point.

If, as it claims, SSDT is building the package for SQL Server 2016, then I don't see how there could be a compatibility level problem. On the upside, I did get to find out what compatibility levels mean and how they could possibly impact some other stuff we have here.

Tom_Hogan Hall of Fame Points: 3873 More actions · Answer 5

How exactly are you running the package on the server?

When you say you're running the package using 32bit runtime, you mean that the box is checked on the Advanced tab of either the SQL Agent job step or if you right-clicking on the package in the Catalog and selecting Execute?

bikerdadHLV SSCommitted Points: 1567 More actions · Answer 6

The latter, right-clicking the package where it resides in the Integration Services Catalogs\SSISDB in Object Explorer. The above screenshot is exactly what I'm using. No point in setting up an Agent job unless I can get it to run.

Tom_Hogan Hall of Fame Points: 3873 More actions · Answer 7

bikerdadHLV - Friday, September 1, 2017 2:42 PM
The latter, right-clicking the package where it resides in the Integration Services Catalogs\SSISDB in Object Explorer. The above screenshot is exactly what I'm using. No point in setting up an Agent job unless I can get it to run.

So the box next to "32-bit runtime" is NOT checked? You want to run it with the box checked (same for any agent job).

bikerdadHLV SSCommitted Points: 1567 More actions · Answer 8

Sorry I was unclear, I meant that was the interface I was using. Yes, it is checked to run 32-bit Runtime.

bikerdadHLV SSCommitted Points: 1567 More actions · Answer 9

bikerdadHLV

SSCommitted

Points: 1567

October 2, 2017 at 5:29 pm

#1961976

bumpity bump. bump.

sgmunson SSC Guru Points: 110639 More actions · Answer 10

I'm pretty sure you can't run SSIS in 32-bit mode AND go after an ODBC source via the 64-bit driver. If your ODBC Connection uses the 64-bit driver, that's quite likely the problem. If you have an ODBC connection for both the 32-bit and 64-bit drivers, and you named them the same, then that is the problem. You would have to name them differently. Ran into this with other ODBC drivers.

EDIT: Correction to first sentence.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Sue_H SSC Guru Points: 90860 More actions · Answer 11

Pretty sure it's what Steve is saying - and your error is for the version of the ODBC driver you are using.
You don't mention which ODBC driver you are using but that is what you need to look into. If you are using Oracles, there is a separate download for the 32 bit drivers. They can be installed side-by-side with the 64 bit drivers.

Sue

David Burrows SSC Guru Points: 65138 More actions · Answer 12

sgmunson - Tuesday, October 3, 2017 7:57 AM
I'm pretty sure you can't run SSIS in 64-bit mode AND go after an ODBC source via the 64-bit driver.

Are you sure you meant that?

Far away is close at hand in the images of elsewhere.
Anon.

sgmunson SSC Guru Points: 110639 More actions · Answer 13

David Burrows - Wednesday, October 4, 2017 6:51 AM
sgmunson - Tuesday, October 3, 2017 7:57 AM
I'm pretty sure you can't run SSIS in 64-bit mode AND go after an ODBC source via the 64-bit driver.
Are you sure you meant that?

Nope... I'm pretty sure I didn't. Corrected my post. What I said about having the ODBC connections named the same also applies to this situation as well. Thanks for pointing that out. Must have had a brain fart...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

wh39727 SSC Veteran Points: 240 More actions · Answer 14

Has anyone figured this out? I have a package that works locally, and on 1 production server, but on another i'm getting the same "The version of ODBC File Source, clsid {xxx} is not compatible with this version of the Data Flow" error on a 2nd production server. Target Version is SQL Server 2016, 32-Bit mode in Visual Studio and when executing from SSISDB on the new server. Same IBMi ODBC driver is installed on both servers. Same exact package, same exacts DSN's. I'm really out of ideas, and help would be great.