Hi ,

Can anyone tell me the detailed steps to upgrade the SSIS packages. We are migrating from SQL 2005 Enterprise Edition to SQL 2014 Standard Edition. Database migrations are happening smoothly , but i am facing difficulty in SSIS package migration.

What are the best practices for migrating/ upgrading the SSIS packages ? Does the Editions of SQL (2005 Enterprise to SQL 2014 Standard) impact the package upgrade.

Your help would be much Appreciated. Thanks in Advance :)

What difficulty do you face?

You just open the SSIS 2005 project in SSDT-BI downloaded (or Visual Studio 2013 with SSDT-BI installed) and it takes care of the upgrade.

Once the packages have been migrated when executed can throw error messages as for example there components are not part of SSIS Enterprise

The advanced transforms only available with Enterprise edition:

• Data Mining Training Destination
• Data Mining Query Component
• Fuzzy Grouping
• Fuzzy Lookup
• Term Extraction
• Term Lookup
• Dimension Processing Destination
• Partition Processing Destination

The advanced tasks only available with Enterprise edition:

• Data Mining Query Task

source http://www.sqlis.com/sqlis/post/SQL-Server-Editions-and-Integration-Services.aspx (SSIS 2008 but remains true onwards)

for more specifications see https://msdn.microsoft.com/en-us/library/cc645993%28v=sql.110%29.aspx?f=255&MSPPError=-2147217396

Hi Ramya,

To upgrade packages that were created in earlier versions of Integration Services to the Integration Services format that SQL Server 2014 uses. SQL Server provides the SSIS Package Upgrade Wizard to help in this process. We can double-click the Integration Services project to open the project in SSIS 2014, Integration Services automatically opens the SSIS Package Upgrade Wizard, then select the desired packages to upgrade.

Besides, some custom SSIS Tasks can always throw error after upgrading packages from SQL Server 2005 to SQL Server 2014. After going through the upgrade wizard you need to do several things:

1. Change your custom tasks and components to target the .NET 4.0 Framework in each of the project properties, in the Application Tab, under Target framework.
   
2. Update the assembly references in each of your projects to point to the appropriate SqlServer dlls.
   
3. Build and copy your project dlls to the appropriate directory, depending on what kind of components you are updating, i.e. Program Files (x86)\Microsoft SQL Server\120\DTS\PiplelineComponents
   
4. Register your components in the GAC.
   
5. Close Visual Studio and then reopen; open your SSIS packages and refresh the SSIS Toolbox, by right clicking in the tool box and selected Refresh Toolbox.

If some of your script task throw errors to you, we can open the script task in VSTA, insert Breakpoint to debug the scripts, then find the root cause. For more details, please see:
Debug a Script by Setting Breakpoints in a Script Task and Script Component

The following documents is for your reference:
Upgrade Integration Services Packages Using the SSIS Package Upgrade Wizard
http://nobrainerlab.com/2013/02/19/ssis-script-task-upgrade-to-ssis-2012/

Thanks,
Katherine Xiong

Hi Arthur/ Katherine,

Thanks for your reply. Please find attached the error message I am getting when I try to run my package. Please guide me on this .