Transferring data with Data Transfer packages

Created by: Gunnar Steinn Magnússon

In this article, you are going to transfer data from an external web service to your local exMon database. Specifically, you will read about:

• Create the Data Transfer
• Setting up the Source
• Configuring the Destination
• Column Mapping
• Next Steps

Data Transfer Packages can be used to transfer data from any Data Provider to an SQL Server destination. Common usage scenarios are:

• Staging data from a web service to be able to limit requests and join between multiple endpoints
• Create a historical table for later use

The exMon Data Transfer Packages support every Data Provider as the source and can be quickly deployed.

Create the Data Transfer

Create a new Data Transfer Package by right-clicking Packages and selecting New Data Transfer

 

 

Setting up the Source

All Data Providers in exMon can be used as a source in Data Transfer.

1. Start by selecting the appropriate source Data Provider. In this case, we are talking to a web service and select PowerShell as the Data Provider.
   When selecting SQL Server or Oracle as Data Provider, you are able to select a preexisting table or a view from the Table dropdown.
2. Custom can be used to write specific queries against the data provider.
3. After selecting a table or writing a custom query, click Preview to see the data.

 

In some cases, you might want to run a pre-or post-execution script on the source data provider. To do this, click Pre/postexecution to enter those scripts.

 

Configuring the destination

All SQL Server Data Providers are supported as destinations for Data Providers.

1. Select a Data Provider and a destination table.
2. If you have not already created the destination table, you can use Create table to create the table.
3. If you are using the exMon database as a destination, it's most common to use the client or staging schema.
4. Here, you can also set a Pre / post-execution script to be executed before or after the transfer. This can be useful for merging to final tables. Finally, click Create

 

Column Mapping

The final step is to set a mapping between the source and destination data. exMon will try to autocomplete this mapping if the names are the same.

 

Next steps

The data transfer is now ready. The next steps could be:

• Deploy to production and add to a scheduled group
• Create a Query that validates the data

Gunnar is the author of this solution article.