Event-based integration - the missing SSIS capability
Microsoft SQL Server Integration Services provides powerful mechanisms for application and data integration. One of the main strengths of the platform is its advanced batch processing, which makes it capable of efficiently handling large amounts of data. However, SSIS doesn’t come with the necessary infrastructure for handling event based processing and implementing near real-time data integration. There are no standard ways to trigger package execution as a response to an external event - e.g. a notification via a webhook. The simplistic workaround of scheduling regular executions at some fixed time interval, is often not a good option, as it requires a compromise between having significant latency (i.e. slow reaction time) and overloading the servers (i.e. putting too much burden on the infrastructure while checking for new/updated data).SSIS NoW for Near Real-time Integration
SSIS NoW (which stands for “SSIS with No Wait”) offers a much more efficient and robust solution to this problem. It enables SSIS developers to build near real-time data integration solutions using a familiar technology and easily hook up SSIS packages to be run in response to events. The key characteristics of the solution are:- Available at no additional cost to any COZYROC SSIS+ Ultimate subscription customer.
- Enables event-triggered execution (as opposed to the standard scheduled one). Mechanisms for connecting to webhooks and for subscribing to Salesforce Streaming API events are provided out-of-the-box. Other event interception mechanisms can be easily integrated by using the provided APIs for submission of execution jobs.
- Reliable handling of events - the intercepted events are delivered to the package execution infrastructure in the desired order (with the help of SQL Server Service Broker).
- Flexibly distributed - the different parts of the solution (the event interception and the corresponding SSIS package execution) can run on different servers.
- Scalable package execution - capable of distributing package executions on multiple machines with installed SSIS.
- No overhead in package execution startup - support of “warm startup” of the SSIS package execution units (i.e. runners). By loading and initializing the .NET assemblies of SSIS and third-party components just once, the runners can execute subsequent packages without any additional overhead (important for handling a large number of events).
SSIS NoW for building Web APIs
Besides enabling SSIS to be used in building near real-time integration solutions, SSIS NoW also enables developing Web APIs on top of SSIS packages. Instead of having to master a general purpose programming language and a web API framework, SSIS developers can leverage their existing knowledge and easily expose the packages they develop as web APIs. To support this use case, SSIS NoW offers the following additional capabilities:- Multiple authentication mechanisms - several methods for user authentication are supported.
- Data streaming - allows processing of big data volumes without the need for an intermediate persistent storage. Data is delivered to the package execution units directly from the event intercepting party, in batches.
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:
- Package/Project parameters - can be used only as inputs for the package.
- Public properties of a task, component or connection manager - can be used as inputs for the package execution.
- Connection strings of any Connection manager are a special sub-case of the above, because they are used during a streaming process.
- 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 theMinNumberOfRunners
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 bothlocal
and64bit
tags defined. - The same queue (
TestTasks
) will receive factorial-local jobs, because they require the execution unit to havelocal
defined. - Jobs associated with the factorial-catalog can be queued either on
64BitTasks
or32BitTasks
queues, in a random fashion. - Similarly factorial-64bit jobs can be sent to
64BitTasks
orTestTasks
queues, because the corresponding execution units both have the64bit
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:
- 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.
- 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.
- 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. - 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 profileDefault
and unitDefault64
). NOTE: Only the attributesQueue
andMaxMemory
of the correspondingUnit
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
- Open appsettings.json from the installation folder in your favorite json editor..
- Locate the section Kestrel. It is commented out by default.
- 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.
-
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.
- To force SSL-only connections in the Web Bridge instance, locate the
Hosting
section in appsettings json and setForceSsl
totrue
:
"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.
- Finally, restart the Web Bridge Windows service to apply the setting changes.
Hosting behind IIS
- Download and install the .NET Core 3.1 Hosting Bundle.
- Open the SSIS NoW Web Bridge folder (
C:\Program Files (x86)\CozyRoc\SSIS NoW\Web Bridge
by default). - The
web.config
located there is already prepared for in-process IIS hosting. - Ensure that the
appsettings.json
file has its wholeKestrel
section commented out andHosting:ForceSsl
set tofalse
. It should look like this:
{ "Hosting": { "ForceSsl": false }//, //"Kestrel": { // "Endpoints": { // "HTTP": { "Url": "http://*:80" }, // "HTTPS": { // "Url": "https://*:443", // "Certificate": { // "Path": "certificate.pfx", // "Password": "Test123" // } // } // } //} }
- 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 theweb.config
and add a--config
argument with the appropriate path:
<aspNetCore processPath="dotnet" arguments=".\CozyRoc.Bridge.AspNetCore.dll --config "PATH_TO_YOUR_CONFIGURATION_FILE_HERE"" stdoutLogEnabled="false" stdoutLogFile=".\logs\stdout" hostingModel="inprocess" />
- Create a new IIS website and set its physical path to the SSIS NoW Web Bridge folder.
- 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 tohttp://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"}]}}}
- 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 withReadFrom=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 whenWaitResult
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 valueanywhere
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 areDefault
,Normal
orPassive
. Default isDefault
. 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 areFile
(default),SQL
,DTS
, orCatalog
(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 aPassword
at theService
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.
* 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
- 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.
- If there is no token attached, the user must go through the login process to obtain a valid token.
- 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. - 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. - Next, the Web Bridge generates a Java Web Token using the extracted
user id
and thesecret
from the configuration file. The token is sent back to the user. - 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 arequerystring | 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 arequerystring | 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:
- Create a new .NET Core 3.1 project in Visual Studio.
- Add
Microsoft.AspNetCore.Authentication.Abstractions
reference to the project (via NuGet). - Implement
IAuthenticationHandler
- 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:
- 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.
- Ability to provide the data for a certain Job in batches, without requiring the Bridge to maintain any state during the whole process.
- 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.
Knowledge Base
Quick Start
In this task, we will show you step-by-step how to create near real-time Account Integration from Salesforce to Zoho CRM using COZYROC's SSIS NoW product.
Prerequisites:
- Installed SSIS Plus
- Installed SSIS NoW
- Accessible SSIS NoW Web Bridge app
- Salesforce account with sufficient access rights to Create/Edit "Callouts" (Webhooks)
- Zoho account
- SalesforceWebhook sample project
- Save and build the SalesforceWebhook sample project
- After
<Routing>
tag add the following definition.
<Route Path="/salesforce-to-zoho-account" Service="salesforce-to-zoho-account" WaitResult="false" Method="post"> <RequestBody ServiceParameterName="User::JsonContent" ParameterType="text" /> </Route>
Step 3. Create Salesforce Callout after account is created
- Login into Salesforce account.
- Navigate to "Setup -> Custom Code -> Apex Classes".
- Create new class by selecting "New".
3.1 Paste the following code inside the opened editor.
3.2 Press "Save".
public class Webhook implements HttpCalloutMock { public static HttpRequest request; public static HttpResponse response; public HTTPResponse respond(HTTPRequest req) { request = req; response = new HttpResponse(); response.setStatusCode(200); return response; } public static String jsonContent(List<Object> triggerNew, List<Object> triggerOld) { String newObjects = '[]'; if (triggerNew != null) { newObjects = JSON.serialize(triggerNew); } String oldObjects = '[]'; if (triggerOld != null) { oldObjects = JSON.serialize(triggerOld); } String userId = JSON.serialize(UserInfo.getUserId()); String content = '{"new": ' + newObjects + ', "old": ' + oldObjects + ', "userId": ' + userId + '}'; return content; } @future(callout=true) public static void callout(String url, String content) { if (Test.isRunningTest()) { Test.setMock(HttpCalloutMock.class, new Webhook()); } Http h = new Http(); HttpRequest req = new HttpRequest(); req.setEndpoint(url); req.setMethod('POST'); req.setHeader('Content-Type', 'application/json'); req.setBody(content); h.send(req); } }
- Create another class by selecting "New".
4.1 Paste the following code inside the opened editor.
4.2 Replace {{SSIS NoW Web Bridge address}} with the Web Bridge host address.
4.3 Press "Save".
@isTest public class AfterAccountInsertWebhookTriggerTest { static SObject mock(String sobjectName) { SObjectType t = Schema.getGlobalDescribe().get(sobjectName); SObject o = t.newSobject(); Map<String, Schema.SObjectField> m = t.getDescribe().fields.getMap(); for (String fieldName : m.keySet()) { DescribeFieldResult f = m.get(fieldName).getDescribe(); if (!f.isNillable() && f.isCreateable() && !f.isDefaultedOnCreate()) { if (f.getType() == DisplayType.Boolean) { o.put(f.getName(), false); } else if (f.getType() == DisplayType.Currency) { o.put(f.getName(), 0); } else if (f.getType() == DisplayType.Date) { o.put(f.getName(), Date.today()); } else if (f.getType() == DisplayType.DateTime) { o.put(f.getName(), System.now()); } else if (f.getType() == DisplayType.Double) { o.put(f.getName(), 0.0); } else if (f.getType() == DisplayType.Email) { o.put(f.getName(), 'foo@foo.com'); } else if (f.getType() == DisplayType.Integer) { o.put(f.getName(), 0); } else if (f.getType() == DisplayType.Percent) { o.put(f.getName(), 0); } else if (f.getType() == DisplayType.Phone) { o.put(f.getName(), '555-555-1212'); } else if (f.getType() == DisplayType.String) { o.put(f.getName(), 'TEST'); } else if (f.getType() == DisplayType.TextArea) { o.put(f.getName(), 'TEST'); } else if (f.getType() == DisplayType.Time) { o.put(f.getName(), System.now().time()); } else if (f.getType() == DisplayType.URL) { o.put(f.getName(), 'http://foo.com'); } else if (f.getType() == DisplayType.PickList) { o.put(f.getName(), f.getPicklistValues()[0].getValue()); } } } return o; } @isTest static void testTrigger() { SObject o = mock('Account'); Test.startTest(); insert o; update o; delete o; Test.stopTest(); System.assertEquals(200, Webhook.response.getStatusCode()); System.assertEquals('{{SSIS NoW Web Bridge address}}/salesforce-to-zoho-account', Webhook.request.getEndpoint()); if (Webhook.request != null) { Map<String, Object> jsonResponse = (Map<String, Object>) JSON.deserializeUntyped(Webhook.request.getBody()); System.assertNotEquals(null, jsonResponse.get('userId')); } } }
- Navigate to "Objects and Fields -> Object Manager" and select "Account" from the list.
- Navigate to "Triggers" and select "New".
6.1 Delete everything from the editor.
6.2 Paste the following code.
6.3 Replace {{SSIS NoW Web Bridge address}} with the Web Bridge host address.
6.4 Press "Save".
trigger AfterAccountInsertWebhookTrigger on Account (after insert) { String url = '{{SSIS NoW Web Bridge address}}/salesforce-to-zoho-account'; String content = Webhook.jsonContent(Trigger.new, Trigger.old); Webhook.callout(url, content); }
Optional
Step 4. SSIS NoW Web Bridge app is hosted locally behind router
- You have to assure that router's port on which Web Bridge app is listening is forwarded to the host machine internal IP address.
Guide how to forward router's port you can find here
- You have to assure that port which Web Bridge app is using is open in Windows Firewall.
Guide how to open Windows Firewall port you can find here
In this task, we will show you step-by-step how to create near real-time Account Integration from Dynamics CRM to Zoho CRM using COZYROC's SSIS NoW product.
Prerequisites:
- Installed SSIS Plus
- Installed SSIS NoW
- Accessible SSIS NoW Web Bridge app
- Dynamics CRM account with sufficient access rights to Create/Edit Webhooks
- Zoho account
- DynamicsWebhook sample project
Step 1. Setup SSIS NoW configuration
- Open SSIS NoW configuration file, if default is used it is located at "%ALLUSERSPROFILE%\CozyRoc\SSIS NoW\Configurations\default-config.xml".
- After
<Services>
tag add the following definition and replace {{Path}} with the actual path where DynamicsWebhook sample project is placed.
<Service Name="dynamics-to-zoho-account" Timeout="120" BatchSize="1024"> <Package Path="{{Path}}DynamicsToZoho.dtsx" /> <Inputs> <Input ChannelType="Variable" Name="User::JsonContent" /> </Inputs> </Service>
- After
<Routing>
tag add the following definition.
<Route Path="/dynamics-to-zoho-account" Service="dynamics-to-zoho-account" WaitResult="false" Method="post"> <RequestBody ServiceParameterName="User::JsonContent" ParameterType="text" /> </Route>
Step 2. Setup DynamicsWebhook sample project
- Open DynamicsWebhook sample project with Visual Studio.
- Save and build the DynamicsWebhook sample project.
Step 3. Create Dynamics Webhook after account is created
- Download and install on your local machine Plug-in Registration tool from here.
- Start Plug-in Registration tool and select Create new connection.
Optional
Step 4. SSIS NoW Web Bridge app is hosted locally behind router
- You have to assure that router's port on which Web Bridge app is listening is forwarded to the host machine internal IP address. Guide how to forward router's port you can find here
- You have to assure that port which Web Bridge app is using is open in Windows Firewall.
Guide how to open Windows Firewall port you can find here
In this quick-start we will setup a database trigger to automate Salesforce Account creation when a row is inserted in a SQL Server table.
Provide Salesforce credentials
Open the provided package solution with the Microsoft Visual Studio. The solution file is located in C:\SSIS Now\SQLServerBridge\SalesforceCreateAccount\SaleforceCreateAccount.sln
.
Open the SalesforceCreateAccount.dtsx package.
Fill your Salesforce credentials in the Salesforce Connection Manager 1
connection manager. Press the Test Connection button to ensure the credentials are correct.
Save the package and close the Visual Studio.
Detailed instructions and troubleshooting
Detailed instructions and troubleshooting guide is located in C:\SSIS Now\SQLServerBridge\Install and run SQL Bridge Example.md
.
In this quick-start we will trigger SSIS package execution upon reading messages from a Kafka topic.
NOTE: Although the scenario functionally similar to continuous reading from Kafka via SSIS+ Message Queue Task Plus in a For Loop, SSIS NoW provides a more easily scalable and robust artchitecture
Prerequisites:
- Installed SSIS Plus
- Installed SSIS NoW
- Confluent account
- The demo archive now-kafka-bridge.zip
Step 1:
Extract the demo files in a folder on your PC.
Step 2:
- Create Confluent Cloud API key using this guide.
- On last step download the API key file and save it as
kafka.properties
in the demo folder replacing the existing properties file. - Create a new topic in Confluent Cloud called
ssisnow
.
Step 3:
- Open
ssis-now-config.xml
from the demo folder in your favorite text editor. - Change the connection string on line 4 to your SSIS NoW database connection string.
- Change the package path on line 15 to
Demo folder path\Factorial\FactorialJS.dtsx
substituting with the folder path where you extracted the demo files.
Step 4:
- Run the
ssis-now-confluent-demo.ps1
script in PowerShell. SsisNowConfigPath
parameter - absolute path to thessis-now-config.xml
config fileSsisNowLogPath
parameter - absolute path to a file to store SSIS NoW logs, e.g.Demo folder path\ssisnow.log
KafkaConfigPath
parameter - absolute path to thekafka.properties
fileKafkaTopic
parameter - the name of topic from Step 2ssisnow
KafkaGroupId
parameter - consumer group id, usessisnowdemo
- Wait until the script is ready. Two additional console windows will open for SSIS NoW and you should see a line
Waiting for events ...
in the PowerShell output.
Step 5:
- Produce a new message in the
ssisnow
topic using this guide. - Use the following content for the message:
{"Argument":3,"ExpectedResult":6}
. - In the PowerShell output you should see the message being consumed.
- In one of the SSIS NoW windows you should see the
FactorialJS.dtsx
package being triggered.
Knowledge Base
Knowledge Base
COZYROC SSIS+ Components Suite is free for testing in your development environment.
A licensed version can be deployed on-premises, on Azure-SSIS IR and on COZYROC Cloud.