Search

SQL Server Connector Setup

Version 22.0.8473

SQL Server Connector Setup

The SQL Server connector allows you to integrate SQL Server into your data flow by pushing or pulling data from SQL Server. Follow the steps below to connect CData Arc to SQL Server.

Establish a Connection

To allow Arc to use data from SQL Server, you must first establish a connection to SQL Server. There are two ways to establish this connection:

  • Add a SQL Server connector to your flow. Then, in the settings pane, click Create next to the Connection drop-down list.
  • Open the Arc Settings page, then open the Connections tab. Click Add, select SQL Server, and click Next.

Note:

  • The login process is only required the first time the connection is created.
  • Connections to SQL Server can be re-used across multiple SQL Server connectors.

Enter Connection Settings

  1. Enter the connection information:

    • Name—the static name of the connection. Set this as desired.

    • Type—this is always set to SQL Server.

    • Auth Scheme—the authentication scheme to use for the connection.

    • Server—the URL of the SQL server.

    • Port—the port that the SQL server is listening on.

    • Database—the database to connect to.

  2. Enter the connection information for your chosen authentication scheme:

    • Password/NTLM—enter your login credentials in the User and Password fields.

    • Kerberos—enter values for User, Password, Kerberos KDC, and Kerberos SPN.

    • Azure Password—enter your login credentials in the User and Password fields, and enter your tenant in the Azure Tenant field.

    • Azure AD/Azure Service Principal—enter your tenant in the Azure Tenant field, and enter your OAuth information in the OAuth Client Id and OAuth Client Secret fields.

    • Azure MSI—no additional information needed.

  3. If needed, click Advanced to open the drop-down menu of advanced connection settings. These should not be needed in most cases.

  4. Click Test Connection or Connect to ensure that Arc can connect to SQL Server with the provided information. If an error occurs, check all fields and try again.

  5. Click Add Connection to finalize the connection.

  6. In the Connection drop-down list of the connector configuration pane, select the newly-created connection.

  7. Click Save Changes.

Select an Action

After establishing a connection to SQL Server, you must choose the action that the SQL Server connector will perform. The table below outlines each action and where it belongs in an Arc flow.

Action Description Position in Flow
Upsert Inserts or updates SQL Server data. By default, if a record already exists in SQL Server, an update is performed on the existing data in SQL Server using the values provided from the input. End
Lookup Retrieves a value from SQL Server and inserts that value into an already-existing Arc message in the flow.
The Lookup Query determines what value the connector will retrieve from SQL Server. It should be formatted as a SQL query against the SQL Server tables.		 Middle
Select Retrieves data from SQL Server and brings it into Arc.
You can use the Filter panel to add filters to the Select. These filters function similarly to WHERE clauses in SQL.		 Beginning
Execute Stored Procedures Treats data coming into the connector as input for a stored procedure, and then passes the result down the flow.
You can click the Show Sample Data button to provide sample inputs to the selected Stored Procedure and preview the results.		 Middle