Enabling SSIS in Visual Studio 2022

If you're using Visual Studio 2022, you will need to install the SQL Server Integration Services Projects extension to use SSIS.

1. Navigate to Extensions > Manage Extensions.
2. In the Manage Extensions window's search box, search for "SQL Server Integration Services Projects 2022" and select the extension in the list.
3. Click Download.
4. Close Visual Studio and run the downloaded Microsoft.DataTools.IntegrationServices.exe installer. Proceed through the installer with default settings.
5. Open Visual Studio. There should now be an "Integration Services Project" project template available.

Adding the MongoDB Connection Manager

Create a new connection manager as follows:

1. Create a Visual Studio project with the "Integration Services Project" template.
2. In the project, right-click within the Connection Managers window and select New Connection from the menu.
3. In the Description column, select CData MongoDB Connection Manager and click Add...
4. Configure the component as described in the next section.

Alternatively, if you have an existing project and CData MongoDB Source or CData MongoDB Destination:

1. Right-click your CData MongoDB source or destination component in your data flow
2. Select Edit... to open an editor window.
3. Click the New... button next to the Connection manager: dropdown selector to create a connection manager.
4. Configure the component as described in the next section.

Connecting to MongoDB

Set the following connection properties to connect to a single MongoDB instance:

• Server: Set this to the name or address of the server your MongoDB instance is running on. You can specify the port here or in Port.
• Database: Set this to the database you want to read from and write to.

Connecting to MongoDB Using DNS Seed Lists

To connect using DNS seed lists

• Server: Set this to "mongodb+srv://"" + the name of the server your MongoDB instance is running on. You can specify the port here or in Port.
• Database: Set this to the database you want to read from and write to.
• DNSServer: Set this to the hostname of a DNSServer that can resolve the necessary DNS entries.

Using DNS seed list connections allows for auto-detection of cluster topologies and more flexibility in deployment. See https://docs.mongodb.com/manual/reference/connection-string/#dns-seed-list-connection-format for more information.

Connecting to Replica Sets

To connect to a replica set, set the following in addition to the preceding connection properties:

• ReplicaSet: Set this to a comma-separated list of secondary servers in the replica set, specified by address and port.
• SlaveOK: Set this to true if you want to read from secondary (slave) servers.
• ReadPreference: Set this to fine-tune how the component reads from secondary servers.

Securing MongoDB Connections

You can set UseSSL to negotiate SSL/TLS encryption when you connect.

Authenticating MongoDB Connections

Supported AuthScheme types (MONGODB-CR,SCRAM-SHA-1,SCRAM-SHA-256,PLAIN,GSSAPI) are challenge-response authentication and LDAP.

Challenge-Response

In challenge-response authentication, the User and Password properties correspond to a username and password stored in a MongoDB database. If you want to connect to data from one database and authenticate to another database, set both Database and AuthDatabase.

LDAP

To use LDAP authentication, set AuthDatabase to "$external" and set AuthScheme to PLAIN. This value specifies the SASL PLAIN mechanism; note that this mechanism transmits credentials over plaintext, so it is not suitable for use without TLS/SSL on untrusted networks.

X.509 Certificates

Set AuthScheme to X509 to use X.509 certificate authentication.

Connecting to an Amazon DocumentDB Cluster

Before you can connect to Amazon DocumentDB, you will first need to, ensure your Amazon DocumentDB cluster and the EC2 instance containing the mongo shell are currently running.

Next, configure an SSH tunnel to the EC2 instance as follows.

1. From the AWS management console, select Services -> Database -> Amazon DocumentDB. From the DocumentDB management page, select Clusters, then click your cluster.
2. Under the Connect section, note the --host value and its port found in the sample connection string.
3. Navigate to Services -> Compute -> EC2. Select Running instances.
4. Select your instance, then click the Connect button.
5. Under the Example section, note the value identifying the instance and user, shown in the form <ami_username>@<Public DNS>
6. In your preferred SSH client, establish a connection to your EC2 instance using the Host Name from the EC2 instance's Connect page (username@publicDNS) and Port 22.
7. Provide your EC2 instance's private key file (in Putty, you will need to convert the keys from .pem to .ppk) for authentication.
8. Configure an SSH tunnel using the port and host name from the DocumentDB cluster page.
9. Establish the connection to the EC2 virtual machine.

Specify the following to connect to the DocumentDB cluster.

• Server: Set this to the machine name which is hosting the SSH tunnel.
• Port: Set this to the port the SSH tunnel is hosted on.
• User: Set this to the master username used to provision the DocumentDB cluster.
• Password: Set this to the master password set when provisioning the DocumentDB cluster.
• UseSSL: Set this to true.
• UseFindAPI Set this to true.

Connecting to CosmosDB with the MongoDB API

To obtain the connection string needed to connect to a Cosmos DB account using the MongoDB API, log in to the Azure Portal, select Azure Cosmos DB, and select your account. In the Settings section, click Connection String and set the following values.

• Server: Set this to the Host value, the FQDN of the server provisioned for your account. You can also specify the port here or in Port.
• Port: Set this to the port.
• Database: Set this to the database you want to read from and write to.
• User: Set this to the database user.
• Password: Set this to the user's password.