Executing DTS Package from C# error Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass'

Question

I am working on a Console application where I am executing a SSIS package from c#.

Below is my code for the execution

private bool ExecutePackage(int _sid, int _pid, int _envid, string _connection, int _scheduleId)
        {
            bool isSuccess = false;

           try
            {
                var dtPackagePath = HandlerFunctions.GetPackageInformation(_sid, _pid, _envid);
                var DtsxPath = dtPackagePath.Rows[0]["dtsx_path"].ToString();

                Application app = new Application();
                Package package = null;
                //Load the SSIS Package which will be executed
                package = app.LoadPackage(DtsxPath, null);
                //Pass the varibles into SSIS Package

                package.Variables["User::mEnvID"].Value = _envid;
                package.Variables["User::mPID"].Value = _projectid;
                package.Variables["User::mSID"].Value = _sponsorid;
                package.Variables["User::mConnectionString"].Value = _connection;
                
                var results = package.Execute();
                //Check the results for Failure and Success
                if (results == DTSExecResult.Failure)
                {
                    var err = "";
                    foreach (var localDtsError in package.Errors)
                    {
                        var error = localDtsError.Description;
                        err = err + error;
                    }
                }

                if (results == DTSExecResult.Success)
                {
                    isSuccess = true;
                }

            }
            catch (Exception ex)
            {
                isSuccess = false;
                HandlerFunctions.WriteExceptionLog(_scheduleId, _sponsorid,
                    _projectid,
                    "Execute DTSX", ex.Message, ex.InnerException != null ? ex.InnerException.ToString() : string.Empty,
                    ex.StackTrace,
                    Convert.ToDateTime(DateTime.Now));

                throw;
            }
           return isSuccess;
        }

I have referenced Microsoft.Sqlserver.managedDTS.dll with this project and used the below in using attribute

using Microsoft.SqlServer.Dts.Runtime;
using Application = Microsoft.SqlServer.Dts.Runtime.Application;

I have also registered the ManagedDTS.dll using the GACUTIL in the system.

However, when i run the porgram I am getting error in the Application app = new Application(); and the error is as below

Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass' to interface type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSApplication130'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{77A93073-6272-4FAC-BDB5-0C589385701C}' failed due to the following error: Library not registered. (Exception from HRESULT: 0x8002801D (TYPE_E_LIBNOTREGISTERED)).

Can anyone help me with this issue?

Answer 1

The problem is two-fold. The first is that you are attempting to copy the DLLs for Integration Services piecemeal which is not how the installer would handle it. You would need to reproduce all the same actions of the SQL Server installer to get SSIS into a working state on the client machine.

The much, much bigger problem is that what you're trying to do is a violation of the licensing agreement. You have rights to develop SSIS packages for free. You do not have rights to run SSIS packages unless that server has been licensed for SQL Server. Even if you managed to solve the first problem, you have created a solution that puts your employer or the customer at a legal liability from Microsoft Licensing and that's not going to be an inexpensive resolution. Not Oracle licensing order of magnitude bad but still not pleasant.

To get the developer machine into the proper state, you need to get a copy of the Developer Edition of SQL Server. On the Instance Features screen, select "Integration Services"

This will ensure all the correct assemblies are installed in the places they expect to be found.

Answer 2

Based on the error, it looks like the machine you are running this program does not have the SQL Server Integration Services installed.

You need SSIS to run SSIS packages ! (You can't run SSIS packages without SSIS)

You don't need to copy / register DLLs, you should try to refer the C# Assemblies installed by the SQL Server installer.

See: https://learn.microsoft.com/en-us/sql/integration-services/integration-services-programming-overview?view=sql-server-ver15

In my machine, I can see the C# assemblies at this location: