How can I connect to a SQL Server 2016 database from BIDS / SSDT 2012?

I have a server running windows 2008 and we installed SSDT 2012. We created an SSIS package from another workstation with SSDT 2016.

When we saved the package as 2012 then opened it on the 2008 server, the data source to the SQL Server 2016 database does not work. We installed the SQL Client Tools 31.1 and we are able to create an ODBC connection to the 2016 SQL Server using the "ODBC Drive 13 for SQL Server", however SSDT won't see the new driver and cannot connect to the SQL 2016 database.

Help please experts....
LVL 1
sqlagent007Asked:
Who is Participating?
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Oh, that's because the "Microsoft® ODBC Driver 13 for SQL Server" is not supported on Windows Server 2008. It is supported from Windows Server 2008 R2 and above.

Here's the extract from the download page: https://www.microsoft.com/en-us/download/details.aspx?id=50420
Supported Operating System
Linux, Windows 10 , Windows 7, Windows 7 Service Pack 1, Windows 8, Windows 8.1, Windows Server 2008 R2, Windows Server 2008 R2 SP1, Windows Server 2012 R2

Sorry, but the only option is to use a newer OS.
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
The SSDT runs under the Visual Studio IDE which, if I am not mistaken, is a 32-bit process. On a 64-bit machine, you will need to install both versions of the driver - the 64-bit and the 32-bit one (both available at: https://www.microsoft.com/en-us/download/details.aspx?id=53339).  

Which one did you install?
0
 
Pawan KumarDatabase ExpertCommented:
Package created in SSDT 2016 will not open in SSDT 2012. As far as I know downgrading is not possible.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Agree with Pawan. If it's connectivity that's broken, driver versions could be a problem. If you can't even open the package, then you need to upgrade the SSDT because downgrading of packages is not possible (just as is the case with any Visual Studio project/solution).
0
 
sqlagent007Author Commented:
I can open the package, but it will not connect to a data source with SQL 2016. I will try installing the 32 bit driver. Thanks experts!
0
 
sqlagent007Author Commented:
Windows won't let me install the 32 bit driver....
unable-to-install-32bit-driver.jpeg
0
 
sqlagent007Author Commented:
I am not sure what I need to do for Native Client 13 to show up here. (see attached screen shot)
I-dont-see-native-client-13.jpeg
0
 
Pawan KumarDatabase ExpertCommented:
Why you are going back ?
0
 
sqlagent007Author Commented:
Thank you. I got it working using an ODBC and then specifically calling that ODBC in the connection string. The SSIS connection manager uses the ODBC that uses the SQL 13.1 64 bit driver. I got the package to run from SSDT, now I will just test with a scheduled job.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.