[Resolved] Issues with Building Packages Programmatically after Upgrading SQL Server/SSIS from 2008 to 2014

• prasad.perini

  SSChasing Mays

  Points: 604

  More actions

  February 12, 2016 at 6:38 pm

  #311429

  Problem:

  We needed to upgrade SQL Server from 2008 R2 to 2014. In addition to using couple of static SSIS packages, we use SSIS packages that are programmatically generated by VB.NET component based on the input-file settings that are stored in the database. The VB solution was developed using Visual Studio 2008 with target .net version of 3.5.

  Since .Net 4.0 is the minimum for SQL Server 2014, we needed to upgrade our VB solution to 4.0 as well.

  Since the highest .Net framework supported in Visual Studio 2008 is 3.5, we needed to change IDE as well.

  Installed SQL Server and SSIS 2014; uninstalled SQL Server & SSIS 2008; ported VB solution to Visual Studio 2013; changed target version to 4.5.1; and recompiled the solution. Everything seemed normal until when I tried to load a file. The VB component stated throwing the following exception:

  Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: Cannot create a task from XML for task "", type "" due to error 0x80070057 "The parameter is incorrect.".

  ---> System.Runtime.InteropServices.COMException: Cannot create a task from XML for task "", type "" due to error 0x80070057 "The parameter is incorrect.".

  at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSExecutables100.Add(String bstrMoniker)

  at Microsoft.SqlServer.Dts.Runtime.Executables.Add(String moniker)

  --- End of inner exception stack trace ---

  at Microsoft.SqlServer.Dts.Runtime.Executables.Add(String moniker)

  Solution:

  I spent many hours looking (with no positive outcome) for any documentation regarding the changes related to issues going from 2008 to 2014 especially in the area of programmatically generating SSIS packages on the fly. I converted one of the packages that were generated using SSIS 2008 into Visual Studio 2013, compared DTSX (XML) files, and found that the new format was quite different from the old one. I searched for “componentClassID” in both files and noticed that they are different. I updated my VB code to use new IDs, and I was able to load files again. Given below are the Component Class IDs for 2008 and 2014 SSIS components. If you have used other components in your code/package, you will need to find newer IDs for them.

  SSIS 2008 --> SSIS 2014

  "SSIS.Pipeline.2" --> "Microsoft.Pipeline"

  "DTSAdapter.FlatFileSource.2" --> "Microsoft.FlatFileSource"

  "DTSTransform.DerivedColumn.2" --> "Microsoft.DerivedColumn"

  "DTSTransform.ConditionalSplit.2" --> "Microsoft.ConditionalSplit"

  "DTSTransform.UnionAll.2" --> "Microsoft.UnionAll"

  "DTSAdapter.OLEDbDestination.2" --> "Microsoft.OLEDBDestination"

  By the way, several posting recommended changing IDTS…100 names to IDTS….110 or IDTS….120 for SQL Server 2012 and 2014, but Microsoft did not change those interface names.

  I am not sure how many people might have experienced the above problem, but I was completely surprised to no documentation about this issue anywhere on the MSDN or on the Web.

  I hope this helps at least a few people looking for a solution.