Data Transfer Packges 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
exMon Data Transfer Packages support every Data Provider as source and can be quickly deployed.
In this document we are going to transfer data from an external web service to our local exMon database.
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. Start by selecting the appropriate source Data Provider. In our 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 drop down.
Custom can be used to write specific queries against the data provider.
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. Click Pre / post execution to enter those scripts.
Configuring the destination
All SQL Server Data Providers are supported as destinations for Data Providers.
Select a Data Provider and a desitnation table.
If you have not already created the destination table, you can use Create table to create the table. If you are using the exMon database as a destination, it's most common to use the client or staging schema.
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.
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.
The data transfer is now ready. Next steps could be:
- Deploy to production and add to a schedule group
- Create a Query that validates the data