Issues upgrading an SSIS solution/package from Visual Studio 2012 to VS 2013

Microsoft SQL Server 2012 and 2014 has feature parity for SSIS and from a general/development/deployment point of view there is not a lot of exciting things to write about. A main difference is that for SQL Server 2012 the development environment is based on Visual Studio 2012 and for SQL Server 2014 the development environment is based on Visual Studio 2013.

Even though the feature set is the same the compatibility is limited, as described in this blog post from Microsoft: http://blogs.msdn.com/b/analysisservices/archive/2014/04/03/sql-server-data-tools-business-intelligence-for-visual-studio-2013-ssdt-bi.aspx meaning it is still important to use the right tool and right version for the right version of deployment server.

If you are looking for the SSIS BI tools for SQL Server 2012 they are available here: http://www.microsoft.com/en-us/download/details.aspx?id=36843, and for SQL Server 2014 they are available here: http://www.microsoft.com/en-au/download/details.aspx?id=42313. Remember that the plain SSDT SQL Server data tools are for development of database schemas etc. and not for BI work in SSRS/SSIS/SSAS. for BI work the similarly named SSDTBI tools needs to be installed.

It is possible to develop and run the SSIS packages in Visual Studio 2012 and target a SQL Server 2014 environment but for actual deployment to be able to run it scheduled using DTExec.exe or through the SSIS catalogue the project has to be in Visual Studio 2013/SQL Server 2014 format.

The upgrade process seems straightforward; just open the 2012 solution in Visual Studio 2013 and run though the upgrade wizard. However some times there are issues in the upgrade process. This blog post describes one such scenario where the upgrades failed and error messages indicates all sorts of issues.

It turns out that the code/xml parsing of the existing 2012 packages are making some interesting assumptions and that by simply moving the version tag to the top of the xml document the upgrade will work as expected.

Example walk-through:

1, open existing SSIS Visual Studio 2012 solution in Visual Studio 2013.

The open process will pick up the version difference and suggest an upgrade.

image

 

2, run through the upgrade process.

In this case the upgrades failed for all of the SSIS packages in the solution.

image

3, try to open a failed package and read through the error list.

Opening the package will give the following error:

image

 

The design view window shows the main error and there is a list of other errors in the error window/list

image

Main error:

Error    42    Error loading ‘packagename.dtsx’ : The package failed to load due to error 0xC0010014 “One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.”. This occurs when CPackage::LoadFromXML fails.  .    C:\path\packagename.dtsx    1    1

Some other errors in the list:

Error    41    Error loading packagename.dtsx: Failed to load task “DFT – name”, type “”. The contact information for this task is “Performs high-performance data extraction, transformation and loading;Microsoft Corporation; Microsoft SQL Server v10; (C) 2007 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1″.      C:\path\packagename.dtsx    1    1

 

Error    33    Error loading packagename.dtsx: Element “Package\SCT – Placeholder.EventHandlers[OnPreExecute]\Pre-Initialise Event” does not exist in collection “Executables”.      C:\path\packagename.dtsx    1    1

 

Error    1    Error loading packagename.dtsx: Cannot create a task from XML for task “EST – Set VARIABLE”, type “Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” due to error 0xC001F430 “An error occurred while accessing an internal object. This could indicate a custom extension built for Integration Services 2005 is being used.”.      C:\path\packagename.dtsx    1    1

 

 

4, Open the package using the code view to see the underlying xml document.

image

use the view menu or press F7 to open the Code XML view of the SSIS package.

 

5, Locate and move the version tag (PackageFormatVersion) to the top.

Place the <DTS:Property DTS:Name=”PackageFormatVersion”>6</DTS:Property> on the row after the <DTS:Executable…/> statement

search for the text and remove the existing line that in this case was towards the bottom of the file and place it on the 3rd line.

 

6, Save and reopen the design view and the package will work as expected.

Once the package is saved the upgrade engine will automatically and properly upgrade the package to the new version and the package will display the new xml structure in the xml view. (SQL Server 2014 and Visual Studio 2013 uses PackageFormatVersion 8 as version definition.

reopening the package in design view will now work as expected.

 

image

9 thoughts on “Issues upgrading an SSIS solution/package from Visual Studio 2012 to VS 2013

    1. Stefan Johansson Post author

      The normal/standard structure is/should be visible directly from any new, empty SSIS package created from Visual Studio. The XML structure is visible by choosing view/code in the menu or by pressing F7

      This is the standard structure:

      < ?xml version=”1.0″?>
      <dts:executable xmlns:DTS=”www.microsoft.com/SqlServer/Dts”
      DTS:refId=”Package”
      DTS:CreationDate=”mm/dd/yyyy hh:mm:ss XM”
      DTS:CreationName=”Microsoft.Package”
      DTS:CreatorComputerName=”ComputerName”
      DTS:CreatorName=”DOMAIN\UserName”
      DTS:DTSID=”{GUID}”
      DTS:ExecutableType=”Microsoft.Package”
      DTS:LastModifiedProductVersion=”12.0.2456.0″
      DTS:LocaleID=”3081″
      DTS:ObjectName=”Package”
      DTS:PackageType=”5″
      DTS:VersionBuild=”1″
      DTS:VersionGUID=”{GUID}”>
      <dts:property DTS:Name=”PackageFormatVersion”>8</dts:property></dts:executable></code>

      below these lines Visual Studio will/should place any created variables and transformations.

      This specific issue might have a different root cause and therefore another solution. A suggestion would be to remove more and more of the existing package before updating to see if it is related to a specific component/transformation. Or use WinMerge or similar to compare a working version with a non-working version.

      Please feel free to share more information around the issue either here or to me privately.

      Reply
  1. Andy C

    Looks like I had a bad install/contention with other versions. I use VM’s to run the dev environment. Building a new image with a clean install got rid of the problem.

    Reply
  2. klz blz

    Yea. Same failure behavior, but none of my packages were malformed, so, I’m guessing I’ve got the same issue as Andy C. Thanks Andy for posting your solution. I’ll give it a try.

    Reply
  3. Arc

    Hi,

    Can someone tell me estimated time to upgrade 100 packages from ssis 2012 to 2014 through upgrade wizard. Including debugging the packages if there are any issues. Just estimation to plan the upgrade.

    Thanks,
    Arc

    Reply
  4. Jan

    Great Workaround, Stefan! I am testing a Migration path for our SQL 2012 SP3 to 2016 SP1 and you saved me from getting any additional headaches…

    Thank you very much!

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *