0

I am working on a project to upgrade a SQL Server instance from 2008 R2 to 2016. This server is also running SSIS 2008. Based on the MS documentation, it appears that different versions of SSIS can co-exist on the same server. I would like to take advantage of this by leaving SSIS 2008 in place, yet also installing SSIS 2016 side-by-side and slowly migrating the packages to the new version.

What I am unclear on is this:

Can an SSIS 2008 instance/package connect to a SQL 2016 database (or 2012 or 2014)? I’ve seen a lot about backwards compatibility, but not forwards. Is anyone else doing this type of thing?

Any help would be greatly appreciated. Thanks!

2 Answers 2

1

SSIS can connect to many different servers based on the connection type used in the SSIS package. Since even SQL Server 2016 SSIS still uses Native Client 11 (and has since 2012), you should not have any issues connecting from SSIS 2008 R2 to SQL Server 2016. SQL Server 2008 R2 uses Native Client 10 or 10.1 but has no issues connecting to SQL Server 2016 either.

In the connection manager in SSIS, you choose the way to connect to SQL Server. It could be ODBC, OLE DB, or any number of connections types. I say this to say SSIS itself does not care what version of SQL Server you are connecting to but the task you use will determine the connection type. I would assume you are using OLE DB and as such you may choose the provider in the connection string.

0

As long as you are only using SQL 2008 R2 features, with a compatible provider (SNAC 10 or ADO.Net SQL Client for example), your 2008 instance will be able to work with SQL 2016, 2014 or 2012; TDS protocol has been extended but since it supports backward compatibility, your SQL 2016 will still understand "2008 R2 requests".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.