5

I'm using Visual Studio 2015 to create a simple SSIS package. The data source is a DB2 database, and I'm using an ODBC driver on my workstation to connect to DB2. The target is SQL Server 2014.

The package runs fine locally, but whenever I run it on the server, I'm having trouble with the ODBC data source. The driver on the server is the exact same one with the exact same name as the one on my workstation.

To get the package into the server, I've imported the dtsx file into SSIS in Stored Packages. I've also tried to deploy the project to the Integration Service Catalog, but I get one of these failures related to the ODBC source when doing so --

  • The version of ODBC File Source, clsid {xxx} is not compatible with this version of the Data Flow.
  • The component is missing, not registered, not upgradeable, or missing required interfaces. The contact information for this component is "ODBC Source;Connector for Open Database Connectivity (ODBC) by Attunity; Attunity Ltd.; All Rights Reserved; http://www.attunity.com

I've also tried using a file based ODBC source instead of a system one with that file in a shared folder on the server. Again, it runs fine in VS but not on the server.

I've looked at the dtsx file (xml based) and I suspect that there is a conflict with the DTSID for the ODBC driver. I'm not sure how that works but it seems like that ID would be unique for each computer and that SSIS is getting is trying to use the workstation's DTSID on the server.

I'm a bit new to SSIS and Visual Studio so I'm hoping I'm assuming there is a straightforward way to run packages developed on a workstation at the server without these hangups. I just can't find anything specific to this problem anywhere.

We do not have VS or SSDT installed on the server.

EDIT: I added a second sentence in the 2nd error message above. It refers a connector provided by Attunity, which is something I don't understand. The ODBC driver installed on the system is from Data Direct. The CLSID returned in the error message is also associated with an Attunity connector in the server's registry.

There are no ODBC drivers from Attunity showing up in either of the ODBC managers, so it's possible that these are somehow part of a default install or were installed when our server used to have SSDT and VS installed directly on it and were never uninstalled. Or, something else?

Tod Meinke
  • 116
  • 1
  • 1
  • 7
  • What's the error message? – David Rushton Dec 23 '16 at 14:22
  • @destination-data there are various msgs. 1) 'The version of ODBC File Source, clsid {xxx} is not compatible with this version of the Data Flow.' 2) 'The component is missing, not registered, not upgradeable, or missing required interfaces.' – Tod Meinke Dec 23 '16 at 14:37
  • @destination-data I have tested the connection on the server and it works, but just not with the package I created on the workstation. – Tod Meinke Dec 23 '16 at 14:41
  • I'm betting on a 32-bit vs 64-bit issue, as hinted at [in this article](http://wikis.openlinksw.com/UdaWikiWeb/MsSqlOn64bitWin). – TallTed Dec 23 '16 at 16:22
  • @TallTed i'll have to give that a shot as it makes sense. – Tod Meinke Dec 23 '16 at 17:17
  • @TallTed Tried it with a 64 bit driver and getting the same error msg. since the error msg refers to a clsid, it makes me think the failure is due to the source odbc being different on the server than the workstation where it was developed. – Tod Meinke Dec 27 '16 at 15:22
  • What do you mean by "the source odbc being different on the server than the workstation"? You must have the same ODBC driver installed on both the Development and Deployment hosts, and the DSNs should be identically configured on both -- same name, connection attribute settings, etc. [Google results](https://www.google.com/search?q="The+version+of"+ODBC+"is+not+compatible+with+this+version+of+the+Data+Flow") strongly suggest this is the path to follow... – TallTed Dec 27 '16 at 16:28
  • @TallTed I mean the workstation and server have different ODBC connections because they are different computers. The workstation is using a 32 bit driver because I'm using Visual Studio. The server is using a 64 bit driver because SSIS is 64 bit. The ODBC connections have the same DSN and configuration. Both drivers are functioning as well (I tested them both independent of the SSIS package problems on the server). – Tod Meinke Dec 27 '16 at 18:34
  • When you say "a 32 bit driver" vs "a 64 bit driver," do you mean 32-bit and 64-bit versions of the same driver? Do you have both 32-bit and 64-bit on the deployment host, as has been recommended, with identically named and configured DSNs based on each? Do your development and deployment hosts have (the same version of) the Microsoft OLE DB Provider for ODBC, which SSIS uses invisibly when told to connect to an ODBC DSN? – TallTed Dec 27 '16 at 22:38
  • @TallTed The deployment machine has both versions installed, but they have different DSN to distinguish between them. Think of it as x and x64. Before x64 was installed, we only had x, which is 32 bit, and which then had the same DSN as the workstation as well. On the workstation, I only have x32 installed because Visual Studio is 32 bit. However, it has the same name there (i.e., x64) as the driver on the server. The drivers are all fo the same version from the same manufacturer and used the same source files. – Tod Meinke Dec 28 '16 at 01:21
  • I'm sorry, but given the issue you're experiencing, short-hand partial answers won't get to success. You need to be explicit about the DSN names, possibly about the driver names, and possibly more. I will also suggest you stop referring to "workstation" and "server", and rather call these "SSIS Development" and "SSIS Deployment" -- because both are *clients* of the SQL Server and DB2 DBMS *server* instances... I suggest also that you edit your question, rather than adding comments with details which must then be mentally integrated into the Q. – TallTed Dec 28 '16 at 01:31
  • @TallTed Thanks for trying to help, but responses are limited in length on this board. Furthermore, I have not given partial answers. Perhaps you don't understand them, but that's a different problem. – Tod Meinke Dec 28 '16 at 14:04
  • Yes, this board is not about discussion/conversation. Details should be edited into Q, not added as comments. "Think of it as x and x64" is not the same as "DB2 Connect and DB2 Connect 64". DTSID is almost certainly a red herring. I wish you luck. – TallTed Dec 28 '16 at 14:49
  • @TallTed You're hung up on a naming convention that is a distinction without a difference among other petty complaints, have argued about it, and now have complained that the dialog has devolved. Not appreciated. – Tod Meinke Dec 28 '16 at 15:51
  • Try changing Properties > Configuration Properties > General > TargetServerVersion. I was trying to troubleshoot the DataDirect ODBC driver and had to move a failing SSIS package into a dev environment to test. The dev environment was an Azure VM with SQL 2016 and the TargetServerVersion was set to SQL Server vNext. Changed to SQL Server 2016 and it worked. – Tom Jul 02 '17 at 18:16

6 Answers6

20

I have resolved this issue by changing the Deployment Target Version of Integration Services project in VS in my case from SQL Server 2017 to SQL Server 2016 (the target SQL version). Hope this helps. Old quesition, but came up first in google when I tried to solve my problem.

Yanek
  • 323
  • 2
  • 9
  • how did you make this change? – TemporaryFix May 20 '19 at 16:03
  • 3
    In the Visual Studio menu Project-->Properties, or just right click on project name in the Solution Explorer and choose Properties. It should be first option in the General properies category named "TargetServerVersion". – Yanek May 22 '19 at 11:51
  • This needs to be the accepted answer! Once I did this I also had to update the Run64BitRuntime to False as well to make it work. – John Grabanski Mar 26 '20 at 21:24
  • Work perfect for me, save my day, thanks! – Dany Apr 13 '22 at 12:56
1

In my case this issue was caused by deploying an SSIS project to a 2016 server using SQL Mgmt Studio 2017. Once I redeployed using SSMS 2016, it resolved the error.

EricG
  • 11
  • 1
0

VS or SSDT by default uses 32-bit drivers, so check there is option when you execute for 32-bit. Or install both 32-bit and 64-bit driver and create ODBC with same name.

This is what i do usually for ODBC connections in SSIS -- create connection in both 32-bit and 64-bit.

So create one with C:\Windows\SysWOW64\odbcad32.exe (32-bit) and one with C:\Windows\System32\odbcad32.exe (64-bit).

TallTed
  • 9,069
  • 2
  • 22
  • 37
Aashish Jain
  • 121
  • 5
0

I had this same problem when executing my package in SQL Server Agent. I resolved it by turning on the Execution Options "Use 32 Bit Runtime" flag in the agent step. Drove me nuts for a few days, hope this helps.

Heather K
  • 1
0

check the version of your solution vs the SQL version of where you are deploying. I had this issue and matching up versions fixed the problem. My solution was 2017 but the SQL box was 2016. I had to change the solution to 2016. cheers!

user5880801
  • 1
  • 1
0

In my situation I was having this error and I switched to using the 32-bit version. My initial test worked, then I redeployed using a the project I was working with and I got this error again, even though I had switched Execution Options "Use 32 Bit Runtime" flag.

It ended up that I was having problems with my ODBC Data Source. I was using IBM Informix driver and it was having an issue with the chosen locale. I changed it to use the server's specific local and tested its connection. That then worked and my SSIS package worked on the server.

In another situation with this same Informix driver, I had the database name, slightly incorrect and that gave me this same error.

Paul Hegel
  • 61
  • 1
  • 6