How to load data from Salesforce Pardot to MS SQL Server

Access your data on Salesforce Pardot

The first step in loading your Pardot data to any data warehouse solution is to access your data and start extracting it.

Salesforce was one of the pioneers in the SaaS and API economy, and as would someone expect from them, Pardot can be accessed through a web REST API.

Accessing data from Pardot through the API is a straightforward process. You perform GET requests to the relative API endpoints, and API will respond with a result to the query that has been made.

The API is built around 22 different resources representing anything that someone can do with the platform’s marketing automation capabilities. You will find endpoints to access your Lists or your Visitors.

The things that you have to keep in mind when dealing with an API like the one Pardot has, are:

1. Rate limits: Every API has some rate limits that you have to respect. Especially when you are dealing with APIs that are coming from SalesForce, where the API calls are shared among the integrations and the regular product users.
2. Authentication: You authenticate on Pardot using OAuth, which will add some overhead to developing an application that will try to pull data out.
3. Paging and dealing with a big amount of data: Platforms like Pardot tend to generate a lot of data, as they track people’s interactions with your brand. Pulling big volumes out of an API might be difficult, especially when considering and respecting any rate limits that the API has.

Transform and prepare your Pardot data for MS SQL Server Replication

After you have accessed data on Pardot, you will have to transform it based on two main factors,

1. The limitations of the database that is going to be used
2. The type of analysis that you plan to perform

Each system has specific limitations on the data types and data structures that it supports. If you want to push data into Google BigQuery, you can send nested data like JSON directly. But when dealing with tabular data stores, like Microsoft SQL Server, this is not an option. Instead, you will have to flatten out every data before loading it into the database.

Also, you have to choose the right data types. Again, depending on the system you will send the data to and the data types that the API exposes to you, you will have to make the right choices. These choices are important because they can limit the expressivity of your queries and limit your analysts on what they can do directly out of the database.

Export data from Pardot to Microsoft SQL Server

So, after you have managed to access data on Pardot and you have also figured out the structure that data will have on your database, you need to load data inside the database, in our case into a Microsoft SQL Server.

As a feature-rich and mature product, MS SQL Server offers a large and diverse set of methods for loading data to a database. One way of importing data into your database is by using the SQL Server Import and Export Wizard. With it and through a visual interface, you will be able to bulk load data from a number of different data sources that are supported.

Another way for importing bulk data into an SQL Server, both on Azure and on-premises, is by using the BCP utility. This is a command-line tool built specifically for bulk loading and unloading data from an MS SQL database.

Finally, you can BULK INSERT SQL statements for compatibility reasons, especially if you are managing databases from different vendors.

Similarly, and as it happens with the rest of the databases, you can also use the standard INSERT statements to add data row-by-row directly to a table. It is the most basic and straightforward way of adding data in a table, but it doesn’t scale very well with larger datasets.

Updating your Pardot data on MS SQL Server

As you will be generating more data on Pardot, you will need to update your older data on an MS SQL Server database. This includes new records, together with updates to older records that for any reason have been updated on Pardot.

You will need to periodically check Pardot for new data and repeat the previously described process while updating your currently available data if needed. Updating an already existing row on a SQL Server table is achieved by creating UPDATE statements.

Another issue that you need to take care of is identifying and removing any duplicate records on your database. Either because Pardot does not have a mechanism to identify new and updated records or because of errors on your data pipelines, duplicate records might be introduced to your database.

In general, ensuring the quality of data inserted in your database is a big and difficult issue, and MS SQL Server features like TRANSACTIONS can help tremendously. However, they do not solve the problem in the general case.

The best way to load data from Salesforce Pardot to MS SQL Server

So far, we just scraped the surface of what you can do with MS SQL Server and how to load data into it. Things can get even more complicated if you want to integrate data coming from different sources.

Are you striving to achieve results right now?

Instead of writing, hosting, and maintaining a flexible data infrastructure, Rudderstack can handle everything automatically for you.

Easily use the Salesforce Pardot connector from Rudderstack, along with multiple sources or services like databases, CRM, email campaigns, analytics, and more. Quickly and safely ingest Pardot data into MS SQL Server and start generating insights from your data. Don't want to go through the pain of direct integration? RudderStack’s Salesforce Pardot to MS SQL Server integration makes it easy to send data from Salesforce Pardot to MS SQL Server.