February 11, 2016 at 3:27 pm
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. (Please excuse the typo in the topic name. I am not able to correct it.)
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.
May 27, 2016 at 12:54 pm
You just made my week! I too had been searching for some documentation on these changes and yours was the only post I found with this info. Worked first time!
May 27, 2016 at 1:10 pm
I am glad that you could resolve the issue. One other note of caution: When SSIS packages are generated dynamically using SSIS 2014, the connection manager's property ColumnNamesInFirstDataRow is set to True by default. Because of this, the package was skipping one row of data. If your file doesn't have any header rows set the ColumnNamesInFirstDataRow property to False.
January 20, 2017 at 4:03 pm
Note that when I received this error when trying to run an Import from within SSMS, I ran a repair on SQL 2016, and that fixed it.
From the SQL install, choose Maintenance, then Repair.
October 12, 2017 at 10:19 am
This was very helpful. We upgraded packages from 2008 to 2016 and they worked for the most part. However, tasks from the third party library from vendor CozyRoc had apparently changed names or DTSIDs so after upgrading to their 2016 library we got the same weird detail in many of the errors. Not being able to identify the old tasks, Microsoft just replaced the Executable's CreationName and ExecutableType with "SSIS.ReplacementTask", which then throws a.... ""SSIS.ReplacementTask" is not registered for use on this computer."
The solution was to create a new package with just the equivalent task from the new library, view the code, and copy the new CreationName, ExecutableType and DTSID values to the old code.
Thanks Prasad!
August 9, 2018 at 4:37 am
Thanks for this useful information. Could anyone please also advice what is there is SQL 2014 for below Component Class IDs. Below ones are from 2008: -
body { margin: 0 0 0 0; padding:0 0 0 0 }td,div { font-family:Segoe UI Semibold;font-size:10pt;vertical-align:top }/* Copyright IBM Corp. 2011 All Rights Reserved. */body { margin: 0 0 0 0; padding:0 0 0 0; overflow:hidden; }.transcript { background-color:#d2d2d2; }.messageBlock { padding-left:10px; padding-right:10px; margin-bottom:3px }.message { padding-left:20px; margin-left:95px; word-wrap:break-word; white-space:-moz-pre-wrap; _white-space:pre; white-space:pre-wrap;}.messageCont { padding-left:20px; margin-left:95px; word-wrap:break-word; white-space:-moz-pre-wrap; _white-space:pre;white-space:pre-wrap;}.other { font-size:11px;color:#39577a;vertical-align:top;font-weight:bold;font-style:normal;float:left; width:95px; }.myself { font-size:11px;color:#da8103;font-style:normal;font-weight:bold;font-style:normal;float:left; width:95px; }.otherCont { font-size:8px;text-align:right; color:#39577a;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; }.myselfCont { font-size:8px;text-align:right; color:#da8103;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; }.system { font-size:11px; word-wrap:break-word;color:#da8103;font-style:normal;font-weight:normal; white-space:-moz-pre-wrap; _white-space:pre;white-space:pre-wrap; }.showTimestamp { padding-left:20px; font-size:11px; float:right; color:#999999;font-style:normal;font-weight:normal; }.other1 { font-size:11px; color:#ac2000;vertical-align:top;font-weight:bold;font-style:normal;float:left; width:95px; }.otherCont1 { font-size:8px;text-align:right; color:#ac2000;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; }.other2 { font-size:11px; color:#3c9fa8;vertical-align:top;font-weight:bold;font-style:normal;float:left; width:95px; }.otherCont2 { font-size:8px;text-align:right; color:#3c9fa8;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; }.other3 { font-size:11px; color:#e25614;vertical-align:top;font-weight:bold;font-style:normal;float:left; width:95px; }.otherCont3 { font-size:8px;text-align:right; color:#e25614;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; }.other4 { font-size:11px; color:#0b6ac8;vertical-align:top;font-weight:bold;font-style:normal;float:left; width:95px; }.otherCont4 { font-size:8px;text-align:right; color:#0b6ac8;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; }.other5 { font-size:11px; color:#b23290;vertical-align:top;font-weight:bold;font-style:normal;float:left; width:95px; }.otherCont5 { font-size:8px;text-align:right; color:#b23290;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; }.other6 { font-size:11px; color:#02e7c7;vertical-align:top;font-weight:bold;font-style:normal;float:left; width:95px; }.otherCont6 { font-size:8px;text-align:right; color:#02e7c7;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; }.other7 { font-size:11px; color:#5b3284;vertical-align:top;font-weight:bold;font-style:normal;float:left; width:95px; }.otherCont7 { font-size:8px;text-align:right; color:#5b3284;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; }.highlight { background-color:#bed6f8; }.datestamp { padding-right:0px; font-size:11px; cursor:default; margin-bottom:1px; background-color:#c0c0c0; width:100%; float:left; text-align:right; color:#ffffff; font-weight:bold; font-style:italic; }#chatAlert { float:left; border-bottom:1px solid #E8D091; padding:6px; width:100%; color:#A5754C; }#chatAlertImage { float:left; }#chatAlertText { float:left; margin-left:6px; }#chatAlertClose { float:right; margin-right:10px; padding-right:6px; margin-top:0px; }#chatAlertText a { color:#A5754C; }#chatAlertText a:hover { color:#A5754C; text-decoration:none; }.tsDisplay { display:block }.dsDisplay { display:block }DTSAdapter.ExcelSource.2
DTSTransform.RowCount
Thanks!
February 28, 2020 at 6:30 pm
Thanks for this post. Together with an article that came after this, I was able to figure out what was going on.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply