Sql-server – Upgrading SQL Server with Integration Services

There will be no issues as SSIS 2012 will work with previous versions of SQL Server. I recently upgraded from 2008R2 to 2012 with 45 SSIS packages and all are working fine. As Always Test all your packages before hand on 2012.

from this link :

As you may already know, the SQL Native Client OLE DB provider is being deprecated. This doesn't affect other OLE DB providers or the OLE DB API. Also, the SSIS OLE DB components are not being deprecated. In SQL Server 2012 SSIS, you can continue to use OLE DB connections and will need to for components such as the Lookup transformation and the Slowly Changing Dimension transformation that require OLE DB. In the post-Denali release, you’ll be able to upgrade and continue to operate these packages without needing to do additional work to explicitly remove OLE DB from the packages.

That's a really big question so let's break it up a bit.

What can I do in advance?

Start with some required reading.

• 2008 R2 Backwards Compatibility
• 2008 Backwards Compatibility

These links have links to further information such as

• Deprecated SQL Server Features
• Discontinued SQL Server Features
• Breaking Changes
• Behavior Changes to SQL Server Features

Read each of them to see what major stuff is changing. Pay particular attention to features you are using.

In addition you should use the Upgrade Advisor. It checks for installed components and identifies those you will need to fix either before or after the install.

Lots of pro's and con's here on both sides.

In-Place

Pros

• MUCH easier. All of your configurations stay the same for example. Also the connection strings for your applications probably won't need to be changed.
• Cheaper. No second set of hardware required.

Cons

• Backout is difficult to impossible. If something goes wrong you are going to have to power through and finish because backout involves creating a whole new server and reinstalling SQL then restoring backups of your tables.

Side-by-side

Basically the pros & cons are the opposite of In place.

Pros

• Safer - If something goes wrong you kill the new version and just continue on with the old one. Then you can try again later.

Cons

• It's more expensive because you have to create a new set of instances probably on new servers.
• It's more difficult because you have to change connection strings, make sure all of your configurations are the same, etc.

Now you can mitigate the expense of the side-by-side by creating a new instance on the same server, moving everything to it, then uninstalling the old instance. It works and depending on your situation might be the best idea.

Honestly the move from 2005 - 2008 R2 isn't that bad. It's nothing compared to 2000 - 2005 or 2008 R2 - 2012 (mostly SSIS changes). I'd say with careful planning and reading you should be in good shape.