Creating dynamic SSIS package [Object model] and using OleDBSource & OleDBDestination internally fails in SSIS 2016

 

Issue:

While dynamically creating SSIS packages using the object model and referencing the following SSIS libraries, you may receive the following exception thrown

SSIS Libraries referenced:

C:\Program Files (x86)\Microsoft SQL Server\130\SDK\Assemblies\

1. SqlServer.DTSPipelineWrap.dll
2. SQLServer.ManagedDTS.dll
3. SQLServer.DTSRuntimeWrap.dll

 

Error Message:

System.Runtime.InteropServices.COMException' occurred in ConsoleApplication1.exe

Additional information: Exception from HRESULT: 0xC0048021

{"Exception from HRESULT: 0xC0048021"}

   at Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSDesigntimeComponent100.ProvideComponentProperties()

   at ConsoleApplication1.Program.Main(String[] args) in c:\Users\Administrator\Documents\Visual Studio 2013\Projects\ConsoleApplication1\ConsoleApplication1\Program.cs:line 27

   at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)

   at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)

   at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()

   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)

   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)

   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)

   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)

   at System.Threading.ThreadHelper.ThreadStart()

 

Steps to reproduce the issue:

1. Use the below C# code in a Console Application:

---------------------------------------------------------------------------------------------------------------------------------------------------

using System;  

using Microsoft.SqlServer.Dts.Runtime;  

using Microsoft.SqlServer.Dts.Pipeline;  

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

namespace ConsoleApplication1

{

    class Program

    {

        static void Main(string[] args)

        {

            Package package = new Package();

            Executable e = package.Executables.Add("STOCK:PipelineTask");

            TaskHost thMainPipe = e as TaskHost;

            MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;

            // Create the source component.    

            IDTSComponentMetaData100 source =

              dataFlowTask.ComponentMetaDataCollection.New();

            source.ComponentClassID = "DTSAdapter.OleDbSource";

           CManagedComponentWrapper srcDesignTime = source.Instantiate();

            srcDesignTime.ProvideComponentProperties();

            // Create the destination component.  

            IDTSComponentMetaData100 destination =

              dataFlowTask.ComponentMetaDataCollection.New();

            destination.ComponentClassID = "DTSAdapter.OleDbDestination";

            CManagedComponentWrapper destDesignTime = destination.Instantiate();

            destDesignTime.ProvideComponentProperties();

             // Create the path.  

            IDTSPath100 path = dataFlowTask.PathCollection.New();

            path.AttachPathAndPropagateNotifications(source.OutputCollection[0],

              destination.InputCollection[0]);

        }

    }

}

---------------------------------------------------------------------------------------------------------------------------------------------------

2. Add the reference from:

•           C:\Program Files (x86)\Microsoft SQL Server\130\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll
•           C:\Program Files (x86)\Microsoft SQL Server\130\SDK\Assemblies\Microsoft.SQLServer.DTSRuntimeWrap.dll
•          C:\Program Files (x86)\Microsoft SQL Server\130\SDK\Assemblies\Microsoft.SQLServer.DTSPipelineWrap.dll

3. Debug the code and you may receive the above exception in the function : srcDesignTime.ProvideComponentProperties();

 

Cause :

The reason for the exception was that the version independent COM ProgID was not registered to point to the latest version, so loading of OLEDB SOURCE connection manager threw above error The code is using version independent ProgIDs:

"DTSAdapter.OleDbSource" &  "DTSAdapter.OleDbDestination".The COM spec says, the version independent  ProgIDs should always load the latest version. But these ProgIDs are not registered.

 

Resolution/Workaround:

As workaround, modify the ProgIDs to the names of SSIS 2016 IDs and use version specific ProgIDs. wiz.

DTSAdapter.OleDbSource.5 & DTSAdapter.OleDbDestination.5 rather than DTSAdapter.OleDbSource & DTSAdapter.OleDbDestination in the above code sample.

 

You may find the information of these ProgIDs from the System registry.

For e.g.

The ProgID "DTSAdapter.OleDbSource.5" is registered to point to SSIS 2016 OLEDB Source.

under HKEY_CLASSES_ROOT\CLSID\{657B7EBE-0A54-4C0E-A80E-7A5BD9886C25}

Similarly, the ProgID “DTSAdapter.OLEDBDestination.5” is registered to point to SSIS 2016 OLE DB Destination are under

HKEY_CLASSES_ROOT\CLSID\{7B729B0A-4EA5-4A0D-871A-B6E7618E9CFB}

 

If you still have the issues, then please contact Microsoft CSS team for further assistance.

 

DISCLAIMER:

Any Sample code is provided for the purpose of illustration only and is not intended to be used in a production environment.  ANY SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.

 

 

Author:        Ranjit Mondal – Support Engineer, SQL Server BI Developer team, Microsoft

Reviewer:    Krishnakumar Rukmangathan – Support Escalation Engineer, SQL Server BI Developer team, Microsoft