Home› User Guide› Working with AnalyticsCreator› Connectors› Configure a Microsoft SQL Server (MSSQL) Connector

Configure a Microsoft SQL Server (MSSQL) Connector

Introduction

A Connector stores the settings used to connect to a source system. Use this how-to to create a Microsoft SQL Server (MSSQL) Connector, enter its connection settings, test access to the intended database, and save the definition in your Data Warehouse (DWH) project.

The intended outcome is a saved Connector with the correct name, type, and database connection, verified by a successful connection test. Reading source metadata and loading data are subsequent tasks.

Applicability

This workflow applies when you are creating a new Connector of type MSSQL for an existing Microsoft SQL Server (MSSQL) database. It uses an existing provider and authentication setup; installing a provider or configuring the source database is outside this task.

The connection-string example uses integrated authentication. For another authentication method, you need a connection string confirmed for your provider and source. The screenshots illustrate the desktop interface; no product version is identified.

Prerequisites

3.1. An open Data Warehouse (DWH) project that you can edit, and a name that identifies the new Connector's source system.

3.2. The source server or instance name, database name, and intended authentication method, with an account that can connect to that database.

3.3. A provider installed in the connecting environment and confirmed as compatible with your AnalyticsCreator version and source. Have the corresponding connection-string syntax available. The pictured SQLNCLI11 value is an example, not an installation requirement.

3.4. Your project's decision on storing this connection string in CFG.SSIS_Configurations. For an Azure source, also have its service type and required Connector configuration available.

3.5. If your connection string uses a protected password value, an existing entry in Encrypted strings and its configured alias.

Steps

Open the Connector editor

In the navigation tree of the intended Data Warehouse (DWH) project, right-click Connectors and select Add connector. The new Connector editor opens.

Select Add connector from the Connectors context menu. The background diagram contains existing example objects.
Connectors context menu with Add connector highlighted.

Name the Connector and select MSSQL

In Connector name, enter a name that identifies your source. This is the Connector's name in the project; the database name is entered separately in the connection string. In Connector type, select MSSQL, the type for a Microsoft SQL Server (MSSQL) connection.

Confirm the name and type before entering the remaining settings. Connector 1 in the screenshot is an illustrative name, not a required value.

Enter your Connector name and select MSSQL. Connector 1 is the pictured example name.
Connector editor with the Connector type list open and MSSQL selected.

Enter the source connection string

To start from the Connector's template, click Template beside Connection string, then edit the text in that field. If you already have a connection string confirmed for your provider, database, and authentication method, enter it directly.

A connection string is text containing the provider, server, database, and authentication settings. For the illustrated template, review these settings:

  1. Data Source — Replace [SERVER] with the server or instance that hosts your source database.
  2. Initial Catalog — Replace [DATABASE] with the name of that source database.
  3. PROVIDER — Identifies the software provider used to connect. Enter the provider identifier from your confirmed connection configuration. Retain SQLNCLI11 only if it is the installed provider required by that configuration.
  4. Integrated Security — The illustrated value SSPI specifies Windows integrated authentication. Use it when the connection is intended to authenticate with a Windows account. For a different authentication method, use the confirmed connection string for that method; replacing only the server and database does not change the authentication method.

Template example: the following text still requires your server and database values.

PROVIDER=SQLNCLI11;Data Source=[SERVER];Initial Catalog=[DATABASE];Integrated Security=SSPI;

Connection example: replacing [SERVER] with REPORTING-SERVER and [DATABASE] with SalesSource directs the connection to the SalesSource database on REPORTING-SERVER. These are illustrative names; enter values that exist in your environment. Keep the syntax required by your provider.

If your configuration uses a password alias, enter the configured alias in place of the plain-text password. The editor banner shows the form #ENCRYPTEDSTRING# and the menu path Options → Encrypted strings. The Encrypted strings list maintains protected values used by connection settings; use the alias for the existing entry intended for this connection. The integrated-authentication example above has no password value to replace.

Before continuing, check that the string contains your actual server and database, the intended authentication settings, and no unreplaced [SERVER] or [DATABASE] placeholders.

Edit Connection string for your source. This capture still contains [SERVER] and [DATABASE] placeholders and is not a completed configuration.
Connector editor showing the MSSQL template, Template button, Azure source type, and connection-string storage checkbox.

Review Azure classification and connection-string storage

Review the remaining settings in the same Connector editor:

  1. Azure source type — Identifies the Azure service used as the source. If your source is hosted in Azure, select the service type specified by your project's Connector configuration. If your source requires no Azure classification, leave this setting unchanged. The blank field pictured in Enter the source connection string does not establish a choice for an Azure connection.
  2. Do not store connection string in CFG.SSIS_Configurations — Select this checkbox when your project's configuration requires excluding the string from CFG.SSIS_Configurations. Clear it when the string must not be excluded by this setting. This setting concerns the named configuration table; it is separate from saving the Connector definition. The cleared checkbox in the screenshot is not a universal recommendation.

Confirm these selections against the configuration established in prerequisite 3.4 before testing.

Test the connection

Click Test connection at the bottom left of the Connector editor and read the reported result. Continue only when the test succeeds for the intended source database.

If the test fails, use the reported error to check the server or instance, database, provider, and authentication settings. Correct the identified problem and click Test connection again. See Troubleshooting for the supported checks.

Click Test connection after completing the string. This capture identifies the button; it still shows placeholders and contains no successful test result.
Test connection button at the bottom left of the Connector editor.

Save the Connector

Review Connector name, Connector type, and the completed connection string. If you change connection settings after a successful test, repeat Test the connection before saving.

Click Save at the bottom right of the editor. This saves the Connector definition, which is available under Connectors in the navigation tree.

Click Save after a successful test. This capture identifies the button; it does not show a saved Connector or a successful test result.
Save button at the bottom right of the Connector editor, beside Cancel.

Reopen and check the saved Connector

Expand Connectors and locate the name you saved. To find it in a list, right-click Connectors, select List connectors, and locate the matching entry.

In the navigation tree, right-click the saved Connector and select Edit connector. Check that Connector name, Connector type, and Connection string retain the intended values. Check the server and database within the string, then click Test connection again to verify the saved configuration.

Expected Results

5.1. The Connector can be found under Connectors in the intended Data Warehouse (DWH) project using the name you entered.

5.2. Reopening it shows MSSQL as the Connector type and the connection settings for your intended source database.

5.3. Testing the saved configuration reports a successful connection. Clicking the test button without checking its result does not establish success.

These checks verify the saved definition and connection at the time of testing. They do not demonstrate that source metadata has been imported, data has been loaded, or a package has been deployed or executed.

Decisions and variations

Authentication and provider: use the illustrated template only when it matches your installed provider and integrated-authentication configuration. Use a confirmed connection string for other configurations, as explained in Enter the source connection string.

Azure and storage settings: use the source service and project storage requirements to make the selections in Review Azure classification and connection-string storage; do not reproduce the screenshot's selections by default.

Metadata work after saving: use Read metadata from a Connector with DWH Wizard to retrieve metadata from the source system. A Source is an input object supplied by a Connector; metadata describes its structure, such as columns and data types. To reuse Source definitions already stored in the Data Warehouse (DWH) project, use Use existing Source metadata with DWH Wizard. Working from existing metadata without a live connection is a separate task from creating the tested connection described here.

Troubleshooting

  1. Provider or driver error — Compare the PROVIDER value with the provider installed in the connecting environment. If they do not match, use the identifier and connection-string syntax confirmed for that environment. Resolve an unavailable or incompatible provider before repeating Test connection.
  2. Connection or authentication failure — Read the reported error. Recheck Data Source and Initial Catalog for the intended server or instance and database, and replace any remaining template placeholders. Verify the intended account's access and the authentication method with the source administrator. Correct the identified settings and test again.
  3. Saved item not found — Confirm that you clicked Save in the intended Data Warehouse (DWH) project. Right-click Connectors and select List connectors to look for the actual name you entered. Locate the existing entry before creating another Connector.