Upgrade SSIS 2005 to SSIS 2014
We are currently running SQL Server 2005 and are in the process to upgrade to SQL Server 2014. All SSIS projects were created with Visual Studio 2005. What is the best way to upgrade the 2005 projects to 2014 and which Visual Studio project is the best to use? Will the community edition do what we need, or do we require VS2015 Professional? Because we need to make some changes to some of the connections, we cannot just do a straight upgrade, we need to upgrade and then edit the projects as well before deploying. Any help will be highly appreciated.
August 19th, 2015 1:23pm

Hi Eis007,

We recently upgraded our SQL Server from 2005 to 2012, this includes the SSIS and SSRS.

We upgraded our Database Engine first but prior of doing that we ran several checks (Use Upgrade Advisor to Prepare for Upgrades and Identifying Deprecated SQL Server Code with Profiler ). I posted a question about this from this link 

To answer your questions:

What is the best way to upgrade the 2005 projects to 2014  - You can use the VS2013 to upgrade the packages all at once but bear in mind that some of it will break. The way we did it is we upgrade the package  1 by 1 and deploy them by stages/release. In this way we can maximize the new features in SSIS (project deployment, project parameter and etc). Also we can test them properly. 

which Visual Studio project is the best to use - Visual Studio 2013 with SSDT for BI 2014

Will the community edition do what we need, or do we require VS2015 Professional - I think you can but there some feature's that's not available in community

 


Free Windows Admin Tool Kit Click here and download it now
August 20th, 2015 12:53am

use Upgrade Adviser

1.Install a stand alone copy of SQL server 2014

2. Move you databases first to this new sql server

3. Move all the packages using ssis import package ( this should upgrade most of the packages except script tasks)
dont forget to migrate your configurations (update them)

4 you may have to open each package in visual studio Data tools (BIDS in 2005) to recompile any script task.

5 run the all these new packages side by side for few days to check everything is working fine.

once you are satisfied you deploy this newly upgraded packages to your new SSIS production server.

The use the Visual Studio which comes with 2014 thats more than sufficient and recommended to make sure you packages get updated properly. ( Why use any other :-) ).

August 20th, 2015 1:18am

Hi,

In SSIS 2014 there are any changes but there were changes in SSIS 2008 and 2012 version .Please check all that before migration

The package upgrade wizard will help point these things out...
http://blogs.msdn.com/mattm/archive/2008/03/21/package-upgrade-wizard.aspx
http://msdn.microsoft.com/en-us/library/cc280547.aspx


BOL has some of the concerns listed
http://msdn.microsoft.com/en-us/library/cc280546.aspx


Sensitive areas are
- Script Tasks in control flow
- Script Components in Data flow
- Lookup component changed a bit in SSIS 2008
- 3rd party components and tasks in SSIS

Couple of others not mentioned in BOL:
- Order in which configuration files are applied changed slightly in 2008:
http://dougbert.com/blogs/dougbert/archive/2009/04/07/understand-how-ssis-package-configurations-are-applied.aspx

- Connection managers with connection strings that reference SQLNCLI since that is replaced with SQLNCLI10 
http://blogs.msdn.com/mattm/archive/2008/03/21/some-notes-about-sql-native-client-in-2008.aspx

Here's the general list of what's new in SSIS  https://msdn.microsoft.com/en-us/library/bb522534(v=sql.120).aspx

Free Windows Admin Tool Kit Click here and download it now
August 20th, 2015 2:38am

Thank you. Much appreciated
August 20th, 2015 3:40am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics