locked
Upgrade SSIS 2005 to SSIS 2014 RRS feed

  • Question

  • 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.
    Wednesday, August 19, 2015 5:21 PM

Answers

  • 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

     


    Thursday, August 20, 2015 4:52 AM
  • 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.



    • Edited by Idr_p Friday, August 21, 2015 12:05 AM
    • Marked as answer by Katherine Xiong Tuesday, August 25, 2015 7:25 AM
    Thursday, August 20, 2015 5:16 AM
  • 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


    Please Dont forget to mark as answer and Helpful Post. It helps others to find relevant posts to the same question. Milan Das

    Thursday, August 20, 2015 6:36 AM

All replies

  • 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

     


    Thursday, August 20, 2015 4:52 AM
  • 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.



    • Edited by Idr_p Friday, August 21, 2015 12:05 AM
    • Marked as answer by Katherine Xiong Tuesday, August 25, 2015 7:25 AM
    Thursday, August 20, 2015 5:16 AM
  • 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


    Please Dont forget to mark as answer and Helpful Post. It helps others to find relevant posts to the same question. Milan Das

    Thursday, August 20, 2015 6:36 AM
  • Thank you. Much appreciated
    Thursday, August 20, 2015 7:38 AM
  • Thank you. Much appreciated
    Thursday, August 20, 2015 7:38 AM
  • Thank you. Much appreciated
    Thursday, August 20, 2015 7:39 AM