Write data in Cosmos DB using Cosmos DB Connector in SSIS

In this section we will learn how to configure and use Cosmos DB Connector in the API Destination to write data in the Cosmos DB.

Video Tutorial - Write or lookup data to Cosmos DB using SSIS

This video covers following and more so watch carefully. After watching this video follow the steps described in this article.

How to download SSIS PowerPack for Cosmos DB integration in SSIS
How to configure connection for Cosmos DB
How to Write or lookup data to Cosmos DB
Features about SSIS API Destination
Using Cosmos DB Connector in SSIS

Watch this video on YouTube

How to write or lookup data to Cosmos DB in SSIS (Import data)

In upper section we learned how to read data, now in this section we will learn how to configure Cosmos DB in the API Source to Post data to the Cosmos DB.

Read the data from the source, being any desired source component. In example we will use ZappySys Dummy Data Source component.
From the SSIS Toolbox drag and drop API Destination (Predefined Templates) on the Data Flow Designer surface and connect source component with it, and double click to edit it.
Select New Connection to create a new connection:

API Destination - Cosmos DB

Connect to your Azure Cosmos DB databases to read, query, create, update, and delete documents and more!
Use a preinstalled Cosmos DB Connector from Popular Connector List or press Search Online radio button to download Cosmos DB Connector. Once downloaded simply use it in the configuration:

Cosmos DB

Proceed with selecting the desired Authentication Type. Then select API Base URL (in most cases default one is the right one). Finally, fill in all the required parameters and set optional parameters if needed. You may press a link Steps to Configure which will help set certain parameters. More info is available in Authentication section.

ApiKey [Http]

Steps to get Cosmos DB Credentials : ApiKey [Http]
Connecting to your Azure Cosmos DB data requires you to authenticate your REST API access. Follow the instructions below:

Go to your Azure portal homepage: https://portal.azure.com/
In the search bar at the top of the homepage, enter Azure Cosmos DB. In the dropdown that appears, select Azure Cosmos DB.
Click on the name of the database account you want to connect to. (Also copy and paste the name of the database account for later use.)
On the next page where you can see all of the database account information, look along the left side and select Keys.
On the Keys page, you will have two tabs: Read-write Keys and Read-only Keys. If you are going to write data to your database, you need to remain on the Read-write Keys tab. If you are only going to read data from your database, you should select the Read-only Keys tab.
On the Keys page, copy the PRIMARY KEY value and paste it somewhere for later use. (The SECONDARY KEY value may also be copied and pasted instead of PRIMARY KEY.)
Back at the connector screen, enter the primary or secondary key you recorded in step 6 into the Primary or Secondary Key field.
Enter the database account you recorded in step 3 into the Database Account field.
Enter or select the default database you want to connect to using the Defualt Database field.
Enter or select the default table (i.e. container/collection) you want to connect to using the Default Table (Container/Collection) field.
Select the Test Connection button at the bottom of the window to verify proper connectivity with your Azure Devops account.
If the connection test succeeds, select OK.

Cosmos DB

ApiKey [Http]

https://[$Account$].documents.azure.com

Required Parameters
Primary or Secondary Key	Fill in the parameter...
Account Name (Case-Sensitive)	Fill in the parameter...
Database Name (keep blank to use default) Case-Sensitive	Fill in the parameter...
API Version	Fill in the parameter...
Optional Parameters
Default Table (needed to invoke #DirectSQL)	Fill in the parameter...

Select the desired endpoint, change/pass the properties values, and go to the Mappings tab to map the columns.

API Destination - Cosmos DB

Connect to your Azure Cosmos DB databases to read, query, create, update, and delete documents and more!
Finally, map the desired columns:

API Destination - Cosmos DB

Connect to your Azure Cosmos DB databases to read, query, create, update, and delete documents and more!
That's it; we successfully configured the POST API Call. In a few clicks we configured the Cosmos DB API call using ZappySys Cosmos DB Connector