Remote SSIS Execution, Windows Service Application

Hi,

Recently stumbling upon the SSIS double hop authentication issue and figuring it out (after a lot of head scratching). The solution I adopted to over come the issue is posted out as follows:

Loading and Running a Remote Package Programmatically (MSDN)

– What is Double Hop Authentication Issue with SSIS ?

This article can provide a great insight into what is above mentioned issue CONFIGURING SQL SERVER KERBEROS FOR DOUBLE-HOP AUTHENTICATION

And to put it in simple words,

If you call your SSIS package (From Computer A, remote client), which is installed on (Computer B, SQL Server Box) and the SSIS package tries to connect to the SSAS Cubes (installed on Computer C).

SSIS package is unable to pass-on the credentials (windows Authentication) which was passed on by

Computer A -> Computer B -> XXX -> Computer C

Hence leading to you SSIS package failure all the time when executed from Computer A, But if you jump on the Computer B it will always succeed. (Confusing and Hard to detect)

Solution, to the rescue…

Now the idea with the following solution, is as follows:

• Custom Windows Service, self hosting WCF web service invoking SSIS package installed
  

  Install a custom windows service on the SQL Server box to kick off the package with the authoritative credentials, to replicate on the box instantiation of the package with authority to make the authenticated connection to the various sub systems like shared network drives, SSAS cubes and others (which uses Windows Authentication).

  

  This custom windows service self-host a WCF web service which when called from the remote systems, invokes the SSIS package on the box.

  

Now the above solution can be called by any client application remotely, with the security logic designed as per your organizational requirement coded in you custom web service.

and Volia…

You can now kick off the SSIS packages remotely.

For your reference, here’s a snippet of the custom windows service code base

ILaunchSsisPackageService.cs

[ServiceContract(Namespace = "http://RemoteSSISExecution")]
    public interface ILaunchSsisPackageService
    {
        [OperationContract]
        int LaunchPackage(string packageId, string folderName, string projectName, string packageName, string[] paramterNames, string[] paramterValues, string[] paramterTypes);
    }

LaunchSsisPackageService.cs

class LaunchSsisPackageService : ILaunchSsisPackageService
    {

        private EventLog _serviceEventLog;

        public LaunchSsisPackageService()
        {
            IntializeEventLog();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="packageId"></param>
        /// <param name="folderName"></param>
        /// <param name="projectName"></param>
        /// <param name="packageName"></param>
        /// <param name="paramterNames"></param>
        /// <param name="paramterValues"></param>
        /// <param name="paramterTypes"></param>
        /// <returns>
        /// -90 Invalid paramter array lengths
        /// -99 Invalid Package Paramters, Package not found or Security (Id) validation failed
        /// -999 System Error Occured during the service call
        /// </returns>
        public int LaunchPackage(string packageId, string folderName, string projectName, string packageName, string[] paramterNames, string[] paramterValues, string[] paramterTypes)
        {
            try
            {
                if (paramterNames != null && paramterValues != null && paramterTypes != null)
                    if ((paramterNames.Length != paramterValues.Length) ||
                        (paramterValues.Length != paramterTypes.Length))
                        return -90;

                using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["OrgKpiDb"].ConnectionString))
                {
                    conn.Open();

                    var packageAuthQuery = String.Format(@"SELECT RemoteSSISExecutionKey FROM OrgKpi.tbl_RemoteSSISExecution
                                        WHERE FolderName = N'{0}'
                                        AND ProjectName = N'{1}'
                                        AND PackageName = N'{2}'
                                        AND PackageId = N'{3}'", folderName, projectName, packageName,
                        Guid.Parse(packageId));

                    using (var cmd = new SqlCommand(packageAuthQuery, conn))
                    {
                        var authResult = cmd.ExecuteScalar();
                        if (authResult == null)
                            return -99;


                        // SSIS server object with connection
                        var ssisServer = new IntegrationServices(conn);
                        // The reference to the package which you want to execute
                        var ssisPackage = ssisServer.Catalogs["SSISDB"].Folders[folderName].Projects[projectName].Packages[packageName];
                        // Add a parameter collection for 'system' parameters (ObjectType = 50), package parameters (ObjectType = 30) and project parameters (ObjectType = 20)
                        var executionParameter = new Collection<PackageInfo.ExecutionValueParameterSet>();
                        // Add execution parameter (value) to override the default asynchronized execution. If you leave this out the package is executed asynchronized
                        //executionParameter.Add(new PackageInfo.ExecutionValueParameterSet { ObjectType = 50, ParameterName = "SYNCHRONIZED", ParameterValue = 1 });

                        // Add execution parameter (value) to override the default logging level (0=None, 1=Basic, 2=Performance, 3=Verbose)
                        executionParameter.Add(new PackageInfo.ExecutionValueParameterSet { ObjectType = 50, ParameterName = "LOGGING_LEVEL", ParameterValue = 1 });

                        for (int i = 0; i < paramterNames.Length; i++)
                        {
                            switch (paramterTypes[i])
                            {
                                case "INT":
                                    // Add a project parameter (value) to fill a project parameter
                                    executionParameter.Add(new PackageInfo.ExecutionValueParameterSet { ObjectType = 20, ParameterName = paramterNames[i], ParameterValue = Int32.Parse(paramterValues[i]) });
                                    break;
                                case "STRING":
                                    // Add a project parameter (value) to fill a project parameter
                                    executionParameter.Add(new PackageInfo.ExecutionValueParameterSet { ObjectType = 20, ParameterName = paramterNames[i], ParameterValue = paramterValues[i] });
                                    break;
                            }
                            
                        }

                        // Get the identifier of the execution to get the log
                        var executionIdentifier = ssisPackage.Execute(false, null, executionParameter);
                        return int.Parse(executionIdentifier.ToString());

                    }
                }
            }
            catch (Exception ex)
            {
                _serviceEventLog.WriteEntry("Error: " + ex.Message , EventLogEntryType.Error);
                _serviceEventLog.WriteEntry("Stacktrace: " + ex.StackTrace , EventLogEntryType.Error);
                _serviceEventLog.WriteEntry("Info: " + ex.TargetSite , EventLogEntryType.Error);
                return -999;
            }
        }

        private void IntializeEventLog()
        {
            _serviceEventLog = new EventLog();
            if (!EventLog.SourceExists("RemoteSSISExecution"))
            {
                EventLog.CreateEventSource(
                    "RemoteSSISExecution", "Application");
            }
            _serviceEventLog.Source = "RemoteSSISExecution";
            _serviceEventLog.Log = "Application";
        }
    }

Note: This entire article is geared if you don’t want to mess with your organisational Active Directory setting up SPN’s and figuring out its not giving you desired results

The solution attached along with this article is a windows service solution (with the windows installer as well) which starts ups the self hosted web service ASMX (configurable via App.Config) providing the function to which the package details can be passed on with naive security via GUID. And the function also provides the features to pass on SSIS parameters by list of Names, Values and Types.

Hope it helps

Remote SSIS Package Execution Windows Service Download