Export Data from SQL Server to BigCommerce through SSIS

SQL Server databases are commonly used to store enterprise records. It is often necessary to move this data to other locations. The CData SSIS Task for BigCommerce allows you to easily transfer BigCommerce data. In this article you will export data from SQL Server to BigCommerce.

Add Source and Destination Components

To get started, add a new ADO.NET Source control and a new BigCommerce Destination control to the data flow task.

Configure the ADO.NET Source

Follow the steps below to specify properties required to connect to the SQL Server instance.

1. Open the ADO.NET Source and add a new connection. Enter your server and database information here.
2. In the Data access mode menu, select "Table or view" and select the table or view to export into BigCommerce.
3. Close the ADO NET Source wizard and connect it to the destination component.

Create a New Connection Manager for BigCommerce

Follow the steps below to set required connection properties in the Connection Manager.

1. Create a new connection manager: In the Connection Manager window, right-click and then click New Connection. The Add SSIS Connection Manager dialog is displayed.
2. Select CData BigCommerce Connection Manager in the menu.
3. Configure the connection properties.

   BigCommerce authentication is based on the standard OAuth flow. To authenticate, you must initially create an app via the Big Commerce developer platform where you can obtain an OAuthClientId, OAuthClientSecret, and CallbackURL. These three parameters will be set as connection properties to your driver.

   Additionally, in order to connect to your BigCommerce Store, you will need your StoreId. To find your Store Id please follow these steps:

   1. Log in to your BigCommerce account.
   2. From the Home Page, select Advanced Settings > API Accounts.
   3. Click Create API Account.
   4. A text box named API Path will appear on your screen.
   5. Inside you can see a URL of the following structure: https://api.bigcommerce.com/stores/{Store Id}/v3.
   6. As demonstrated above, your Store Id will be between the 'stores/' and '/v3' path paramters.
   7. Once you have retrieved your Store Id you can either click Cancel or proceed in creating an API Account in case you do not have one already.

Configure the BigCommerce Destination

In the destination component Connection Manager, define mappings from the SQL Server source table into the BigCommerce destination table and the action you want to perform on the BigCommerce data. In this article, you will insert Customers entities to BigCommerce.

1. Double-click the BigCommerce destination to open the destination component editor.
2. In the Connection Managers tab, select the connection manager previously created.
3. In the Use a Table, menu, select Customers. In the Action menu, select Insert.
4. On the Column Mappings tab, configure the mappings from the input columns to the destination columns.

Run the Project

You can now run the project. After the SSIS Task has finished executing, data from your SQL table will be exported to the chosen table.