NoW

• Software Requirements
• Basic Concepts
  • Job
  • Bridge
  • Runner
  • Queue
  • Configuration
• Infrastructure Setup
  • Single server deployment
  • Multi server deployment
  • Web Bridge command-line arguments
  • Runner command-line arguments
  • Post-Installation setup
    • Web Bridge setup
    • Runners setup
• Configuration Setup
  • Factorial sample SSIS project
    • Route section
    • Service section
  • Salesforce Streaming Setup
  • Protecting sensitive data
• Advanced Topics
  • Troubleshooting
    • Logging
    • Runner database table
    • Job database table
  • Authentication in Web Bridge
    • Api-Key Authentication
    • ASP.NET Core Identity
    • OAuth2-like Flow
    • Custom logic in an external .NET Core Assembly
  • Data streaming

Software Requirements

The software requirements for SSIS NoW 1.1 are:

• Microsoft SQL Server 2012 or later + SQL Server Service Broker enabled
• ASP.NET .NET Core 3.1 Runtime (download the hosting bundle here) - for the server where Web Bridge is installed
• Microsoft SQL Server Integration Services (2012-2019) - for the server(s) where Runners are installed
• COZYROC SSIS+ - for the server(s) where Runners are installed (an SSIS+ trial license can be used while evaluating SSIS NoW)
• .NET Framework 4.6.2 or later (download here) - for the server(s) where Runners are installed

Basic Concepts

This is a simplified logical diagram of the main components of the SSIS NoW system that enable event-based processing. The main concepts explained below are Job, Bridge, Runner, Configuration.

Job

The unit of work in SSIS NoW is called a “job”. A single job should trigger a single SSIS package execution by a Runner.

A job includes the following data:

• The location of the package to be executed - as a path, either on the Runner’s file system, or in the SSIS catalog;
• All the necessary information for the runner to be able to execute the package (32/64 bit mode of execution, decryption passwords etc.;)
• Values for certain SSIS parameters and variables, task/component public properties, connection manager’s connection strings, etc.
• A globally unique identifier (GUID), used for progress tracking, statistics, streaming, etc.

A Job definition is maintained by the system internally. In order for a Runner to be able to execute a job, an SSIS NoW administrator must configure a Service element that describes the inputs and outputs for the job and how they are going to be populated.

NOTE: When a package is referenced and executed via an SSIS catalog (instead of DTSX file), variables and task/component public properties are not accessible for setting.

NOTE: The Job definition also includes information on how to set up data streaming - a mechanism of constantly feeding a package with data, without the need for intermediate persistent storage. It is provided by a proprietary engine, called - virtual files. It is explained in more detail in a dedicated section.

Bridge

In the context of SSIS NoW, the term Bridge is a short name for “event interceptor” - a gate through which external events enter the system and then trigger package execution.

SSIS NoW comes with a Web Bridge, which is an implementation of a bridge that allows subscribing to webhooks. It defines:

• the web endpoints that trigger SSIS package execution
• how the HTTP request attributes are translated into Job inputs and outputs.

It is based on the standard ASP.NET Core facilities, and functions either as a standalone, Kestrel-based web server (the default deployment) or it is possible to deploy it as an IIS module.

To create execution jobs and, optionally, to start the processing units, the Web Bridge uses a functionality packaged as a .NET Standard 2.0 assembly CozyRoc.Services.dll, which offers an easy-to-use API with which it’s easy to develop custom bridges via PowerShell or from any managed code.

Runner

An SSIS NoW Job Runner is a lightweight command-line executable, installed on an SSIS enabled system, which is capable of executing any package which is accessible from that system (either as an SSIS package/project file, or from an SSIS catalog). It’s important to note that:

• Each Runner is capable of executing only one package at a time.
• The SSIS infrastructure stays loaded into the Runner memory (a warm start) to minimize load times (unlike using DTEXEC or SQL Agent).

A Runner is responsible for listening to a queue for execution jobs and, based on the job definition, loading and running the SSIS package with the necessary input data. There are several attributes of the package that can be used for providing inputs and outputs for the specific execution:

1. Package/Project parameters - can be used only as inputs for the package.
2. Public properties of a task, component or connection manager - can be used as inputs for the package execution.
3. Connection strings of any Connection manager are a special sub-case of the above, because they are used during a streaming process.
4. SSIS Variables - can be used both as inputs and outputs. NOTE: they cannot be used during catalog execution because they are not accessible.

The Job definition, which triggers specific execution, provides the references to these entities and the values for the input ones.

Queue

A queue serves as a mediator between a Bridge and Runners. Once a Bridge intercepts an event, it constructs a Job definition and queues it (i.e. schedules it for execution).

SSIS NoW uses SQL Server Service Broker as a Queue. It guarantees that:

• Messages are not lost - a message stays in the queue until the other party (i.e. the Runner) retrieves it and processes it.
• The order of messages/events is preserved.

The queues need to be accessible from both the Runner and the Bridge. For the events-processing functionality, the Queue is the only mechanism used for communication between a Bridge and a Runner. This allows for an isolation of components (e.g. a dedicated server to be used for events interception and other server(s) to be used for hosting Runners).

Configuration

SSIS NoW is easily configurable by using a single Configuration file, which describes all entities and the relationships between them.

The main sections include:

• Queues section lists all Queues that are to be used in this Configuration. They all have the Name attribute, which is used for referencing them.
• Services section lists Job definition templates in Service elements. At the job creation stage, the templates are populated with values from the event.
• Profiles section lists sets of execution units (i.e. Runner launch configurations, described later in this chapter).
• Routes section (applicable only to a Web Bridge) describes all the web endpoints and how they are bound to Services (i.e. the Job definition templates).

NOTE: Although the Runners and the Bridge share the same configuration format, it is not required that they share the same configuration file. Actually, having them on separate servers would make reusing the same configuration file impossible, unless it resides on a shared network drive. What’s important is that the Queues section in the config files should reference the same physical queues in the Service Broker.

NOTE: A single configuration file can describe many Queues, processing units (i.e. Runners), and Services.

NOTE: The following XML Schema files are used for editing and validating the configuration files: now-web-bridge-config-1.0.xsd and now-service-config-1.0.xsd.

Profile

The Configuration allows defining multiple execution profiles. Those profiles specify how many Runners are expected to handle the workloads via the Profile element and how they should be configured. During execution, a Bridge is associated with a single Profile.

An excerpt from a sample configuration file is provided below, representing the definition of two profiles - Default (with enabled automatic launching of runners) and Manual (with disabled automatic launching of runners):

    <Profiles>
     <Profile Name="Default">
       <Unit MinNumberOfRunners="2" MaxMemory="1024" Queue="32BitTasks" SqlVersion="2017" Tags="32Bit" />
       <Unit MinNumberOfRunners="2" MaxMemory="1024" Queue="64BitTasks" SqlVersion="2017" Tags="64bit" />
     </Profile>
     <Profile Name="Manual">
       <Unit MinNumberOfRunners="0" MaxMemory="1024" Queue="64BitTasks" SqlVersion="2017" Tags="manual" />
     </Profile>
    </Profiles>

Some notes about the Profile element:

• There should be at least one Unit element (or the system won’t know which Queue to use for scheduling the Jobs). If you do not want the Bridge to auto-launch Runners, then the MinNumberOfRunners should be set to 0 (zero). In this case, we assume the Runners have already been launched (and it is even possible that they are running on different servers).
• Each Unit definition includes not only the Runner launch parameters, but also how many Runners of this type are expected to be running in this Profile.
• Each Unit definition has a reference to a Queue from the Configuration, which will be used by the Runners to listen for execution jobs.
• The SqlVersion determines which runner executable will be run (there is a separate runner executable for each SQL Server version).

Tags and Job-to-Runner matching

The SSIS NoW configuration supports a tags-based mechanism to provide fine-grained control over Job scheduling on execution Runners. Each execution unit defines the keywords (a.k.a. tags) that it can handle, and each Job definition (Service element) defines which tags it expects the execution unit (a.k.a. Runner) to handle. If all the required tags are supported by the Runner, it can be given the task of executing this Job.

<Profile Name="Default">
 <Unit Queue="32BitTasks" ... Tags="catalog,32bit" />
 <Unit Queue="64BitTasks" ... Tags="catalog,64bit" />
 <Unit Queue="TestTasks" ... Tags="manual,local,64bit" />
</Profile>
<Services>
 <Service Name="factorial-debug" Tags="local,64bit" />
 <Service Name="factorial-local" Tags="local" />
 <Service Name="factorial-catalog" Tags="catalog" />
 <Service Name="factorial-64bit" Tags="64bit" />
</Services>

A few scheduling rules are set in the example section of configuration code provided above:

• There are three execution units (i.e. sets of Runners) that are listening on three different queues.
• The factorial-debug jobs will always be queued on TestTasks, because it is associated with the only unit that has both local and 64bit tags defined.
• The same queue (TestTasks) will receive factorial-local jobs, because they require the execution unit to have local defined.
• Jobs associated with the factorial-catalog can be queued either on 64BitTasks or 32BitTasks queues, in a random fashion.
• Similarly factorial-64bit jobs can be sent to 64BitTasks or TestTasks queues, because the corresponding execution units both have the 64bit tag defined.

When more than one execution unit has a positive match for the specified tags, the execution unit that will schedule the Job is chosen randomly.

Infrastructure Setup

The SSIS NoW MSI Installer includes all components of the system. It’s possible to have everything installed on a single machine, but using separate servers for the Web Bridge and the Runners would also be a common deployment scenario.

There are three main modules that can be installed:

• Runners of SSIS packages, which require the machine to have MS SQL Server with Integration Services installed. There are different Runner executables for different SQL Server versions (the oldest supported is MS SQL Server 2012).
• Web Bridge, which is based on ASP.NET Core 3.1 and can be run as a standalone, lightweight Kestrel web server.
• The Setup scripts & tools module provides a simple configuration file, XSD files for authoring/validating the configuration, a factorial calculation sample SSIS project and several PowerShell scripts.

Single server deployment

Having all components installed on a single machine is the easiest way to get started with SSIS NoW. Here are the available components:

By default, all components are included in the installation. If the “Setup script & tools” feature is selected, an extra screen will be displayed in the installer (see below), where some extra configurations (mostly database-related) can be specified.

This configuration can be postponed for later (can be invoked again either by doing a Repair or by manually executing the setup-ssisnow-db.ps1 and setup-ssisnow-config.ps1 under %ALLUSERSPROFILE%\CozyRoc\SSIS NoW\Scripts).

After configuring all required database objects, you can directly run the shortcut Start Web Bridge. For verifying if everything is set up correctly, please run the Test Factorial shortcut that invokes a sample HTTP endpoint. NOTE: The Runners that would process the request need to be licensed first.

Multi server deployment

For more complex scenarios, where maximum scalability is required, a multi-server deployment approach is recommended:

1. Designate a MS SQL Server with a Service Broker enabled to facilitate reliable communication between SSIS NoW components. The server doesn’t need to have Integration Services installed, nor does it need SSIS NoW to be installed, but it needs to be accessible from the machines where the Runners and the Bridge are running.
2. Install SSIS NoW with Web Bridge on the machine which will serve as an entry point for the web requests. It does not need to have MS SQL Server installed. During the installation, provide the authentication credentials for the designated MS SQL Server from [1]. Also, enable Setup scripts & tools during installation to configure the necessary settings.
3. Install SSIS NoW with the Job Runners module on one or more machines with SQL Server Integration Services installed. During the installation, provide the authentication credentials for the designated MS SQL Server from [1]. Set up automatic execution of as many Runner instances as desired using the --daemons command line option.
4. Make sure the SSIS packages created to perform the desired tasks are accessible by the machine(s) in [2]. This most often means copying them there, or putting them on a shared network drive which is accessible in an identical way from all machines with runners from [3].

Diagram for a sample multi-server deployment

Web Bridge command-line arguments

The Web Bridge is a .NET Core 3.1 application, which can be started via a command such as dotnet.exe CozyRoc.Bridge.AspNetCore.dll

The supported arguments are:

• --urls - the urls where the application will listen for requests (e.g. http://*.80)
• --config - path to the configuration file that defines the queues, profiles, etc. (if skipped, default-config.xml will be used)
• --profile - the profile in the configuration file (if skipped, the profile “Default” will be used).
• --logconfig - path to the log4net configuration file (if skipped, log.config will be used).

Runner command-line arguments

The Runner is a command line executable, which reads job definitions from a queue and executes the corresponding SSIS packages. It supports the following command-line arguments:

• --config - path to the configuration file that defines the queues, profiles, etc.
• --unit - the profile and the unit in the configuration file (e.g. Default/Default64 will try to locate a profile Default and unit Default64). NOTE: Only the attributes Queue and MaxMemory of the corresponding Unit element are relevant in the context of the Runner.
• --logconfig - path to the log4net configuration file (if skipped, log.config will be used)
• --basedir - root directory for the SSIS packages (if stored on the file system).
• --daemons - number of Runner instances to launch (the newly created Runners will be provided with all the other command-line arguments)

Example: CozyRoc.Runner.2017.exe --config default-config.xml --unit Default/Manual

Post-Installation setup

Web Bridge setup

The default locations related to Web Bridge installation are as follows:

• Installation folder - C:\Program Files (x86)\CozyRoc\SSIS NoW\Web Bridge
• Scripts folder - %ALLUSERSPROFILE%\CozyRoc\SSIS NoW\Scripts

Configure Web Bridge to run as a Windows Service

In a production deployment, it’s recommended that the Web Bridge is automatically started upon starting the server. To set up a Web Bridge as a Windows Service, you can create (or reuse) a local service user (see instructions at setup-user-web-bridge-service.md) and use the provided install-web-bridge-service.ps1 Powershell script (both available in the “Scripts” folder).

Windows service installation:

• Execute the following cmdlet from the script folder to get detailed instructions for script usage:

  get-help ./install-web-bridge-service.ps1 -detailed

• Example usage:

  ./install-web-bridge-service.ps1 -user myServer/serviceUser

Windows service uninstallation:

• Execute the following cmdlet from the script folder to get detailed instructions for script usage:

  get-help ./uninstall-web-bridge-service.ps1 -detailed

• Example usage:

  ./uninstall-web-bridge-service.ps1

Configure Web Bridge external connectivity

1. Open appsettings.json from the installation folder in your favorite json editor..
2. Locate the section Kestrel. It is commented out by default.
3. To set the normal HTTP listening URL and port, uncomment the Kestrel:Endpoints:HTTP section and set your preferred URL and port. Example result:

{
                //...
                "Kestrel": {
                        "Endpoints": {
                                "HTTP": { "Url": "http://*:80" }
                        }
                }
}

The setting http://*:80 of will cause the Web Bridge to listen to all HTTP requests sent to the current server on port 80.

4. To enable SSL and use a secure HTTPS connection, please copy your certificate (e.g. certificate.pfx) to the install directory of the Web Bridge and uncomment the Kestrel:Endpoints:HTTPS section in appsettings.json. Example result:

   {
           //...
   "Kestrel": {
                   "Endpoints": {
                           "HTTP": { "Url": "http://*:80" },
                           "HTTPS": {
                                   "Url": "https://*:443",
                                   "Certificate": {
                                           "Path": "your_certificate_file_name.pfx",
                                           "Password": "Your certificate password here"
                                   }
                           }
                   }
   }
   }
   
   

By specifying https://*:443 the Web Bridge will listen on all HTTPS requests to the current server on port 443.

5. To force SSL-only connections in the Web Bridge instance, locate the Hosting section in appsettings json and set ForceSsl to true:

       "Hosting": {
                "ForceSsl": true
        }

This setting will redirect all HTTP requests that are not secure to your secure HTTPS endpoint. It will also set the Strict-Transport-Security header on all responses.

6. Finally, restart the Web Bridge Windows service to apply the setting changes.

Hosting behind IIS

1. Download and install the .NET Core 3.1 Hosting Bundle.
2. Open the SSIS NoW Web Bridge folder (C:\Program Files (x86)\CozyRoc\SSIS NoW\Web Bridge by default).
3. The web.config located there is already prepared for in-process IIS hosting.
4. Ensure that the appsettings.json file has its whole Kestrel section commented out and Hosting:ForceSsl set to false. It should look like this:

{
        "Hosting": {
                "ForceSsl": false
        }//,
        //"Kestrel": {
        //      "Endpoints": {
        //              "HTTP": { "Url": "http://*:80" },
        //              "HTTPS": {
        //                      "Url": "https://*:443",
        //                      "Certificate": {
        //                              "Path": "certificate.pfx",
        //                              "Password": "Test123"
        //                      }
        //              }
        //      }
        //}
}

5. By default you should place the SSIS NoW configuration file default-config.xml in this folder. If your SSIS NoW configuration file is located somewhere else, you should modify the web.config and add a --config argument with the appropriate path:

<aspNetCore processPath="dotnet" arguments=".\CozyRoc.Bridge.AspNetCore.dll --config &quot;PATH_TO_YOUR_CONFIGURATION_FILE_HERE&quot;" stdoutLogEnabled="false" stdoutLogFile=".\logs\stdout" hostingModel="inprocess" />

6. Create a new IIS website and set its physical path to the SSIS NoW Web Bridge folder.
7. Start the website. If you used the SSIS NoW example config (C:\ProgramData\CozyRoc\SSIS NoW\Configurations\default-config.xml), you can directly test by navigating to http://localhost:YOUR_PORT/factorial?Argument=4&ExpectedResult=24. You should see a response similar to:

{"Status":"success","Job":{"Id":"b21c4a91-04b3-ef11-9edc-00155d17c5ed","Status":"Completed","StartTime":"2024-12-05T14:29:28.0869145+02:00","EndTime":"2024-12-05T14:29:34.4239138+02:00","Error":"","Service":{"Inputs":[],"Outputs":[{"Name":"User::myResult","ChannelType":"Variable","Value":"24"}]}}}

6. Remember to restart the IIS website every time you modify the SSIS NoW configuration file in order for the changes to take effect.

Runners setup

In a production deployment, where the Runners are independently started (i.e. not auto-launched by a Bridge), we recommend that you make them auto-start by putting a corresponding command in the Windows Startup Folder.

Configuration Setup

As part of the standard installation, there is a sample configuration default-config.xml which offers a starting point for configuring everything necessary to automate an end-to-end scenario. Besides setting up the connectivity between the different infrastructure components (SQL Server, Bridge, Runners) in order to configure the execution of a particular package in response to an HTTP request, the following steps will be necessary:

• Create a Service entry for the corresponding SSIS package
• Create a Route entry for setting up the required HTTP endpoint

A demonstration showing how to set up those configuration entries is available as part of the installation, based on a pre-configured SSIS package (see below):

Factorial sample SSIS project

A simple package is provided as an example, containing only a Script Task which calculates the factorial of a given Argument, and comparing it against a given expected result, (i.e. failing the package execution) if they are not equal. Argument, and ExpectedResult are package parameters, and the calculated result, regardless of its matching with the expected result, is stored in an SSIS variable called User::myResult.

Here is what is necessary to enable triggering the execution of the package from the SSIS NoW infrastructure by exposing it as an endpoint:

The Route section

<Route Path="/factorial" Service="factorial" WaitResult="true">
    <Parameters>
        <Parameter Name="Argument" />
        <Parameter Name="ExpectedResult" />
    </Parameters>
    <Response>
        <Properties Format="json">
            <Property Name="User::myResult" />
        </Properties>
    </Response>
</Route>

The routing configuration is where you configure HTTP endpoints that will trigger SSIS package execution. An endpoint definition consists of specifying the URL and the method, the corresponding service (i.e. SSIS package definition) and, eventually, parameters through which the user of the endpoint can attach data to an HTTP request, which later can be mapped to service parameters. There are different ways parameters can be attached to the incoming HTTP request (for instance: querystring, http header, cookies, form-data etc).

Attributes of the Route element:

• Path - Route URL.
• Method - Route method ( get | post | put | delete | trace | connect | options | head ).
• Service - Name of the corresponding SSIS service.
• InheritFromService - When set, all parameters from the corresponding service are automatically exposed as route parameters ( true | false ). All inherited parameters from the service come by default with ReadFrom=anywhere and any of the parameters can be redefined in the parameters section.
• WaitResult - When set, the Web Bridge waits until the execution of the SSIS package completes and can return a result produced by the package execution.
• WaitResultTimeout - Specifies how many seconds to wait before timing out when WaitResult is set to true.

The Route element may have a list of parameters, which are defined in Parameter elements.

Attributes of the Parameter element:

• Name - Parameter name.
• DefaultValue - If set, this value will be used if the parameter element is not present in the incoming request.
• ReadFrom - This option specifies how to retrieve the parameter from the incoming request (querystring, form-data, url-path, http-header, cookies, anywhere).The special value anywhere tests all other readfrom locations in the order they are listed above.
• ServiceParameterName - Allows mapping of a route parameter with one name to a service parameter with another name. If not set, the route parameter name is used as the service parameter name.

The Service section

<Service Name="factorial" Tags="32Bit">
    <Package Path="FactorialScript.dtsx" Use32RuntimeOn64="true" />
    <Inputs>
        <Input ChannelType="Parameter" Name="Argument" />
        <Input ChannelType="Parameter" Name="ExpectedResult" />
    </Inputs>
    <Outputs>
        <Output ChannelType="Variable" Name="User::myResult" />
    </Outputs>
</Service>

The services configuration defines the package to be executed, including the expected package inputs and outputs. If the event originates from the Web Bridge, the route points to a corresponding service element. This service configuration is then used to create a job execution, populating the service inputs from the route parameters. The output can then get mapped back to the response from the web request, if specified.

Attributes of Service element:

• Name - Used to reference the service from other configuration elements. Should be unique.
• Tags - One or more tags, comma separated. Used to match the service to an execution unit. The first unit having all the service tags is selected.
• Timeout - Service timeout in seconds; overrides the Profile timeout.
• BatchSize - Batch size in kilobytes; overrides the Profile batch size.
• Concurrency - Defines a limit on how many jobs of the current service type can be active at the same time. If the limit is reached, new jobs will be dropped.
• StreamingMode - Allowed values are Default, Normal or Passive. Default is Default. If another value is specified, it overrides the Unit streaming mode.

Attributes of the Package element:

• Path - Path to the package to be executed
• Use32RuntimeOn64 - Indicates whether to use 32 bit runtime for execution on a 64-bit server ( true | false )
• ConnectionString - Connection string to the database instance with an SSIS Catalog (setting up "Integrated Security" is recommended to avoid having plain-text passwords in the config)
• ConnectionType - Allowed values are File (default), SQL, DTS, or Catalog (for SSIS catalog).
• Password - Password to decrypt the package (if the package is protected)

Attributes of Input & Output elements:

• Name - Unique name
• ChannelType - Possible values: Parameter, Property, Variable, Connection, or Stream

Salesforce Streaming

SSIS NoW has a built-in connectivity to Salesforce Streaming API. Streaming API enables streaming of events using push technology and provides a subscription mechanism for receiving events in near real-time. The Streaming API subscription mechanism supports multiple types of events, including PushTopic events, generic events, platform events, and Change Data Capture events. For more information visit Salesforce Streaming API Documentation.

Salesforce Streaming configuration is located under the SalesforceStreaming node.

...
 </Services>
 <SalesforceStreaming>
    <Settings
         ClientId="peoSItfnacthspBO5ozdbnRrrg8x8TGAwfOwKYdxaSZqqije0g7arIP5tbEZEEdCZ3mdD2qCeYnxpf0t4l0RL" 
           ClientSecret="R3LOZJUWA051MNQNAIACIWU3JED3VSWV4BP947ZF8NR26ZLQYBL4N63YFFRJ7INJ"
           RefreshToken="fjsdyi4ytewut3985_skfjlfjsgljfgkdjfglkdfld.dfkjsdlkfjsdlfk"
           LoginUrl="https://login.salesforce.com" 
           OAuthUri="/services/oauth2/token"
           CometDUri="/cometd/52.0"
           Retry="3" />
     <Channels>
        <Channel Name="/topic/Factorial" ReplayId="-1" Service="factorial">
           <EventBody ServiceParameterName="JsonInput" />
           <Parameters>
              <Parameter Name="data.sobject.Argument__c" ServiceParameterName="Argument" />
              <Parameter Name="data.sobject.ExpectedResult__c" ServiceParameterName="ExpectedResult" /> 
           </Parameters>
         </Channel>
         <Channel Name="/topic/OrderUpdates" ReplayId="-1" Service="factorial">
            <EventBody ServiceParameterName="JsonInput" />
            <Parameters>
               <Parameter Name="Order.Item.Name" ServiceParameterName="OrderItem" />
            </Parameters>
         </Channel>
         ... 
      </Channels>
   </SalesforceStreaming> 

Salesforce Streaming configuration consists of two main parts - Settings and Channels.

Settings

• ClientId - Salesforce client id (see Salesforce application setup, step 6).
• ClientSecret - Salesforce client secret (see Salesforce application setup, step 6).
• RefreshToken - Salesforce refrech token (see Salesforce application setup, step 11).
• LoginUrl - Salesforce login URL (usually 'https://login.salesforce.com').
• OAuthUri - Salesforce OAuth token endpoint relative URL (usually '/services/oauth2/token').
• CometDUri - Salesforce CommetD API version relative URL. All available versions can be seen at this page.
• Retry - Specifies how many times will retry in case of connection issue.

Channels

A list of Salesforce channel definitions as well as their binding to an SSIS NoW Service.

Channel

A stream of events to which a client can subscribe to receive event notifications.

• Name - The Name (aka push-topic) of the channel.

• ReplayId - -1 (default if no replay option is specified) - subscriber receives new events that are broadcast after the client subscribes. -2 - subscriber receives all events, including past events that are within the retention window and new events. Specific Id - subscriber receives all stored events after the event specified by its replayId value and new events. For more information visit https://developer.salesforce.com/docs/atlas.en-us.api_streaming.meta/api_streaming/using_streaming_api_durability.htm.

• Service - The name of an existing service, present in the Services section.

Each Salesforce Streaming API event comes with a JSON payload. Channels allow you to bind a JSON property and/or the whole JSON payload, to a service parameter.

EventBody (optional)

Allows you to set the whole JSON payload as a service parameter.

• ServiceParameterName - The name of the service parameter, that the JSON Body is mapped to.

Parameters (optional)

A list of one or more bindings to service parameters. A Parameter has the following attributes:
- Name - a JSON path specifying a property of an incoming event's JSON payload (e.g. { parent: { child: { x: 3 } } } => parent.child.x property ).
- ServiceParameterName - The name of the service parameter, that the JSON property is mapped to.

Protecting sensitive data

Some SSIS packages might need to handle sensitive data (e.g. passwords, often in connection managers). The guidelines below explain how to manage sensitive data using SSIS built-in methods for access control.

If the packages are stored in the SSIS catalog, we recommend that you execute the Runners under a user account which uses Windows Authentication for connecting to the SSISDB and has the necessary access level for packages and folders (enforced via the standard means).

If the packages are stored on the file system, for each server where runners would be executing the packages, there has to be a copy of the package DTSX file (unless they are on a shared network drive). One of the following two policies are recommended:

• Use EncryptAllWithPassword settings and specify a Password at the Service node.
• Use EncryptSensitiveWithUserKey and consider using dtutil for making sure packages are properly encrypted on the machines that host the runners.

Advanced Topics

Troubleshooting

To facilitate troubleshooting the SSIS NoW Web Bridge and Runner, use detailed logging to give extra insight into the inner workings of the components. In addition, some important system information is maintained in a couple of DB tables (see below).

Logging

Both the Web Bridge and the Runner use log4net-based logging that can be configured with an external XML configuration file. The default log.config file activates file-based logging and also logs to the console. The log config file can be specified as a command-line argument --logconfig (if a relative path is used, it is first looked for in the folder of the configuration file used by the Web Bridge and _Runner).

Runner database table

To troubleshoot issues with Runners configuration or availability, one can inspect the Runner table, which has the following fields:

• [Id] - the Runner ID (a combination of a machine ID and process ID)
• [Status] - Shutting Down, Running or Zombie
• [StartTime] - timestamp showing when the Runner process started
• [LastTime] - timestamp showing the last time the Runner updated its status
• [Processed] - number of jobs processed by the Runner process
• [MemoryAtExit] - memory that was used by the Runner process when exiting (in MB)
• [QueueId] - ID of the queue where the Runner is listening

Job database table

To troubleshoot issues with problematic jobs or to analyze data about job execution status, one can inspect the Job table, which has the following fields:

• [Id] - the job unique ID
• [Status] - numerical representation of status
• [StartTime] - timestamp of job creation
• [EndTime] - timestamp of job completion
• [Service] - name of the service
• [QueueId] - ID of the queue where the job has been sent
• [Runner] - ID of the runner which started the job execution
• [SubmittedAtHost] - the host machine from which the job was submitted
• [Error] - error details, in case of failed execution

Authentication in Web Bridge

Setting up authentication in a SSIS NoW Web Bridge configuration file allows you to secure the exposed endpoints. It requires the users of the Web API to obtain an access token by passing through a login procedure to obtain a token and then using this token in the HTTP requests.

NOTE: Some webhooks offered by SaaS products might not support customizing the HTTP request to pass user authentication information. Authentication is configured globally for all endpoints. Therefore, in order to integrate with these SaaS services, an instance of the SSIS NoW Web Bridge with no authentication would need to be set up.

The following authentication mechanisms are supported:

• Api-Key Authentication (use a secret key as API token)
• ASP.NET Core Identity Database (self-hosted)
• OAuth2-like flow (e.g. when using external authentication services like Okta, Auth0, Azure Active Directory)
• Custom authentication (via an external .NET Core Assembly)

Api-Key Authentication

Api-Key authentication offers a simple to setup mechanism to protect SSIS NoW Web Bridge from processing unwanted HTTP requests, i.e. requests not initiated by the configured webhooks. It requires setting up one or more secret keys and passing one of them in the webhook requests.
The SHA-256 hashes of the secret keys are stored in the SSIS NoW Web Bridge configuration file like this:

<Authentication> 
  <ApiKeyAuthentication SecretKeyPropertyName="SecretKey" ReadSecretKeyFrom="querystring"> 
    <SecretKey Value="9f86d081884c7d659a2feaa0c55ad015a3bf4f1b2b0b822cd15d6c15b0f00a08" /> 
  </ApiKeyAuthentication> 
</Authentication> 

When an HTTP request arrives, the web bridge will try to extract the secret key from a property (named value of property SecretKeyPropertyName) contained in the incoming request. Various parts of the requests can be used for that (see ReadSecretKeyFrom below). Then the SHA-256 hash of the secret key is compared against the values specified in SecretKey elements. If there is a match with any of them, the request is considered authorized and will be further processed, otherwise HTTP response with status code 401 (Unauthorized) is returned.

NOTE: For extra security the SecretKey values in the configuration files are not the secrets in plain text, but their SHA-256 hashes. In the configuration example above, the secret key being used is "test", and in the webhook a sample request with a secret, passed as a query string, would be https://{your address}/{your route}?SecretKey=test.

Configuration attribute ReadSecretKeyFrom - specifies which part of the HTTP request carries the secret. The following values are supported for this configuration options:
* querystring - reads the secret key from the query string (e.g. https://{your address}/{your route}?SecretKey=test). Usually, the easiest option to setup.
* http-header - reads the secret key from HTTP headers.
* url-path - reads the secret key from incoming request URL path (e.g. https://{your address}/{your route}/SecretKey/test).
* form-data - reads the secret key attached to a form data in a put/post request.
* xml-body - reads the secret key from an HTTP request body with a XML content (e.g. test).
* json-body - reads the secret key from an HTTP request body with a JSON content (e.g. { SecretKey: "test" }).

Configuration attribute SecretKeyPropertyName - specifies the name of property that is being used for storing the secret key. In the example we used SecretKey, but if replaced by AnyOtherKey the request would look like this: https://{your address}/{your route}?AnyOtherKey=test)

ASP.NET Core Identity

With this mechanism enabled, SSIS NoW Web Bridge relies on having users’ profile data saved in an ASP.NET Core Identity database. In the configuration file, you only need to provide a connection string to the Identity database (for more info see the docs) :

<Authentication>
    <AspNetCoreIdentity ConnectionString="Server={ … server name … }; UID={ … username … }; Password={ … your password … }; Database={ … asp.core identity database name … };" />
</Authentication>

ASP.NET Core Identity will then require an .AspNetCore.Identity.Application cookie, which could be obtained via the identity login procedure.

OAuth2-like Flow

1. In OAuth2-like flow the user sends a request with a JWT (Java Web Token or simply token) attached. The Web Bridge then validates the token against the user id and the secret that is provided in the Web Bridge configuration file.
2. If there is no token attached, the user must go through the login process to obtain a valid token.
3. The user makes a request to the login endpoint, providing login information as parameters to the request. The Web Bridge, in turn, sends a request to an external auth service, attaching the provided login information.
4. Then, the Web Bridge receives and examines the response from the external auth service. If the response matches the criteria described in the configuration file, SSIS-NoW extracts a user id from the response and the login is considered successful.
5. Next, the Web Bridge generates a Java Web Token using the extracted user id and the secret from the configuration file. The token is sent back to the user.
6. After receiving the token, it can be used in subsequent requests to any protected endpoint.

An example configuration using Google email as an external auth service:

<Authentication>
    <OAuth2 Secret="{ ... JWT secret ... }" >
      <AccessToken PropertyName="oauth2-access-token" Lifetime="3600" SigningAlgorithm="" Source="http-header" />
      <Login Method="get" Path="/login">
        <AuthRequest Url="https://oauth2.googleapis.com/tokeninfo" Method="get">
          <Parameter Name="id_token" Value="{customEncyptedToken}" WriteTo="querystring" />
        </AuthRequest>        
        <AuthResponse UserIdPropertyName="email" UserIdSource="body" Format="json">
           <RequiredProperty Name="iss" ReadFrom="body" ExpectedValue="https://accounts.google.com" />
           <RequiredProperty Name="typ" ReadFrom="body" ExpectedValue="JWT" />
           { ... list any other criteria here ... }
        </AuthResponse>
      </Login>
    </OAuth2>
</Authentication>

NOTE: Other popular authentication services are Okta, Auth0, Azure Active Directory, etc.

OAuth2 Configuration Properties

OAuth2

• @Secret - a text that is used to cryptographically sign the token when it is generated and to perform token validation when the token is present.

AccessToken - describes the JWT, how to extract it and the token lifetime upon JWT generation.

• @PropertyName - the name of the property which contains the JWT.
• @SigningAlgorithm (optional) - The algorithm used for the cryptographic signature (for more info see jwt.io).
• @Source (optional) - specifies where to search for the token to extract. Supported values are http-header | cookies | querystring.

Login - describes the details of the login process.

• @Path - login endpoint path.
• @Method (optional, post is used by default) - login endpoint method.

AuthRequest - describes the external auth request.

• @Url - external auth service URL.
• @Method - external auth service method.
• @Format (optional, json is used by default) - specifies the format of the request to the external auth. Possible values are json | xml.

Parameter - each parameter coming from the user request carries some login info, which is redirected to the external auth service. That’s why each parameter consists of ReadFrom, WriteTo, Name and AuthParameterName (optional). If AuthParameterName is not specified, Name is used instead.

• @ReadFrom (optional, anywhere is used by default) - where to read the parameter value from. Supported values are querystring | formdata | http-header | cookies | anywhere.
• @Name - name of the incoming parameter.
• @WriteTo (optional, querystring is used by default) - how to attach the parameter to the external auth service request. Supported values are querystring | body-template | http-header.
• @AuthParameterName (optional) - name of the outgoing parameter
• @Value - used for making static requests. When value is provided, @ReadFrom is ignored and the value is used instead.

AuthResponse - describes the response that is received from the external login system, as well as user Id property information. It contains a list of required properties which, together, form the response criteria. You can think of each required property as criteria which must be satisfied in order to satisfy the overall response criteria. Each required property consists of property name, read from, and expected value.

• @UserIdPropertyName - name of the property which carries the user id.
• @UserIdSource - source of the property which carries out the user id.
• @Format - the format of the external auth response.

RequiredProperty - describes single criteria of the external auth response.

• @Name - required property name.
• @ReadFrom - where to read the required property value from.
• @ExpectedValue - expected value of the required property.

Custom logic in an external .NET Core Assembly

If the previously described built-in authentication methods are not applicable for your use case, you can implement entirely custom authentication logic via a .NET Core 3.1 assembly. This can be achieved by the following steps:

1. Create a new .NET Core 3.1 project in Visual Studio.
2. Add Microsoft.AspNetCore.Authentication.Abstractions
   reference to the project (via NuGet).
3. Implement IAuthenticationHandler
4. Specify the assembly and the typename that implements IAuthenticationHandler in the configuration file.

<Authentication>
   <Custom AuthAssemblyName="{ … absolute or relative path … }/MyCustomAuth.dll" AuthImplementationType="MyCustomAuthNamespace.MyCustomAuthHandler, MyCustomAuthHandler, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" />
  </Authentication>

Here is an example of a custom implementation of IAuthenticationHandler:

public class MyCustomAuthHandler : IAuthenticationHandler
{
    private HttpContext _context;

    public Task InitializeAsync(AuthenticationScheme scheme, HttpContext context)
        {
            _context = context;
            return Task.CompletedTask;
        }

        public Task<AuthenticateResult> AuthenticateAsync()
        {
            var authenticated = { … some custom authentication logic … };
            if (!authenticated)
               return Task.FromResult(AuthenticateResult.Fail("Not Authenticated."));
            var username = { … extract username from the incoming request … }; 
                        // Create authenticated user
            var identities = new List<ClaimsIdentity>
            {
                new ClaimsIdentity( new[] {new Claim( ClaimTypes.Name,  username )  }, "bridge-jwt-auth-service" )
            };
            var ticket = new AuthenticationTicket( new ClaimsPrincipal( identities ), "DefaultScheme" );
            return Task.FromResult( AuthenticateResult.Success( ticket ) );
        }

        public Task ChallengeAsync(AuthenticationProperties properties)
        {
            return Task.CompletedTask;
        }

        public Task ForbidAsync(AuthenticationProperties properties)
        {
            return Task.CompletedTask;
        }
    }

Data streaming

Data streaming is an advanced feature of the SSIS NoW infrastructure that allows you to send and receive large volumes of data over HTTP. Its key features include:

1. Ability to feed large volumes of data to a running package without requiring any of the intermediate parties to persist it on disk - neither the Bridge, nor the Runner itself.
2. Ability to provide the data for a certain Job in batches, without requiring the Bridge to maintain any state during the whole process.
3. Ability to work with standard SSIS package connection managers, which, in most cases, means FILE Connection Managers.

All three aspects are interrelated, with [2] assisting [1] in cases where it is impossible to maintain one connection for the whole duration of data feeding - e.g. web requests which have certain client-side timeouts.

Data streaming is supported only for the following SSIS NoW configuration:

• When packages are stored on the file system as DTSX files (i.e. packages in the SSIS catalog are not supported);
• When the Runner and Bridge can communicate via named pipes (they can be on different machines, but the machines should be accessible from each other).

How it works:

The Job’s GUID becomes crucial for the stateless processing from [2]. Any subsequent batches, after the first one, are identified by this Job’s ID, and the system delivers them, through the streaming mechanism, to the Runner as part of the same package execution. In the most common scenario of web endpoints processing, a single package execution may involve many web requests, with the first one initiating the execution, and all the rest just delivering batches of data to the package. The package, internally, would behave as if the data is read from a single, big file (see below).

Providing the capability described in [3], above, is achieved by a proprietary mechanism called a Virtual File, which involves low-level IO handling capable of exposing a stream of data as a normal file within the filesystem. As a result, a normal FILE Connection Manager can be used inside the SSIS package, and the Runner takes care of setting its ConnectionString property to the correct value (path) of the virtual file.

Since the data stream travels directly from the Bridge to the Runner using named pipes, the machines running these entities should be accessible from one another. There are two modes of initiation. The mode used is dependent on which function is being performed by the initiator of the connection:

• Normal - the Runner listens for an incoming pipe connection, and the Bridge connects to it. More suitable for situations where the Bridge machine has more restrictive firewall rules and cannot open the required ports for the other side to connect to.
• Passive - the Bridge listens for an incoming pipe connection and the Runner makes the connection to it. Suitable for situations where the Runner machine has more restrictive firewall rules and cannot open the required ports for the other side to connect to.

When the two entities (Runner and Bridge) run on the same machine, the firewall rules don’t matter because the named pipes are using shared memory mechanisms internally. However, where the connection needs to happen across a network, named pipes use the TCP/IP protocol: 137 and 139 for UDP and, potentially, 445 for TCP.

The low-level IO system tweaks for virtual files are the reason that data streaming is not available during SSISDB catalog execution; it happens in a different process, which is out of the Runner’s control.