SQL Server SSIS 2016 – ODBC Not Compatible with DataFlow Version

edit: found the solution: https://msdn.microsoft.com/en-us/library/mt238486.aspx

SSMS can only connect to SQL Server 2016 Integrated Services (SSIS 2016) instances. There is a known compatibility limitation with SQL Server Integration Services that prevents connecting to previous versions. As a workaround for this problem, you can connect to your SQL Server Integration Service instance by using the SSMS release aligned with your SSIS instance.

The issue here is that I had to create a System DSN, which is actually odd because the SSIS job is scheduled to run with the account for which the User DSN exists, and it does work as long as I am logged into the server with that account. Anyway I am way too tired to try to understand why the User DSN didn't work. So I decided to go for a System DSN instead.

It is not that I am against System DSN (on the contrary!), but it wasn't me that set this User DSN and I do not know the password for the credentials used in order to create a System DSN. So I had to be a little creative.

I had to use Registry Editor and notepad to convert a User DSN into a System DSN. Here is how I did it:

1. Log in with the user that has the User DSN entries that you with to convert to System DSN
2. Open the Registry Editor and navigate to HKEY_LOCAL_CURRENT\SOFTWARE\Microsoft\ODBC\
3. Right-click ODBC.INI and select export to save it as a file on the desktop (or anywhere else you fancy)
4. Open the .reg file with a text editor such as Notepad
5. Replace the text HKEY_CURRENT_USER with HKEY_LOCAL_MACHINE. Save your changes
6. Double click the .reg file and proceed to import it into the registry.

The process outlined above basically copies all User DSN entries as System DSN entries. If you to have just one entry copied over, then you will need to delete their corresponding keys under .\ODBC.INI as well as their corresponding entries in .\ODBC.INI\ODBC Data Sources.