Overview
There are occasions where it may be desirable to import data from an external data source on a single use basis and use this data in the Jet Data Manager.
An example of this would be a regional sales template that exists in Excel that has a mapping of all salespeople to particular regions. Since this will rarely be updated it may not be necessary to set it up as a proper data source in the Jet Data Manager which would enable it to automatically refresh on a regular basis. Instead, the static data will be loaded into a SQL table that is known to the Jet Data Manager and the data can then be used throughout the project.
The general steps that will be followed in the article are:
- Create a base table structure in the Jet Data Manager with no data
- Guard this table so that it is never truncated during the normal deploy and execution processes
- Populate the data from the external data source into this table using the SQL Import Wizard
It is important to note that this process is only recommended for data that does not need to be updated regularly. This process will not be included in the regular execution package and should be viewed as a one-time import into the SQL table. If the data in the data source needs to be updated frequently then a proper data source should be configured in the Jet Data Manager
Create the Base Table
The first step is to create an empty base table in the Jet Data Manager with the correct fields that data will be imported for. The example covered in this article will illustrate the creation of a Custom Budget table used to hold budget information. The fields that will be created in this table are:
| Field Name | Field Type |
|---|---|
| Budget Date | DateTime |
| Budget Description | Text(50) |
| GL Account | Text(20) |
| Amount | Numeric |
A new custom table can be created by right clicking the Tables node under the data warehouse or staging database and selecting Add Table or Add Custom Table, respectively. Fields can be added to this table by then right-clicking on the table and selecting Add Field.
Guard the Base Table
All tables used in the project undergo standard data cleansing procedures. Part of this process normally involves the truncation and repopulation of the tables used in the data warehouse and staging database. The point of the process covered in this article, however, is to populate the data into the table once and then keep it there in a more permanent manner. The Base Table should be guarded on both deployment and execution. Guarding the table can be performed by right-clicking the Base Table and navigating to Advanced -> Guard.
Both check boxes on the Guard screen should then be checked:
Guarding the table will prevent the Jet Data Manager from trying to rebuild or repopulate the table during regular updated. It may be ideal to implement the Guard on the table last one the user knows that the data has successfully been imported. This will prevent the using from having to go through multiple iterations of guarding and unguarding a table to get the data loaded properly. If the table is not guarded, however, the data that is imported into the table will be removed during the next scheduled execution of the project.
More information regarding the process of Guarding a table can be found in the KB article here: Guarding a Table in the Jet Data Manager
Importing Data Using the SQL Import Wizard
The SQL Import Wizard is a tool that assists with the importing of data to a SQL table.
-
The first screen of the SQL Import Wizard will prompt the user for information regarding the data source that data should be imported from. This should be specified based on the user's preference.
-
The next screen will prompt the user for information regarding the destination for the data to be transferred to. The user should specify SQL as the destination and should point to the server and database that contain the Base Table that was built in the previous step. It is important to always select the _V table for the destination. Based on the current example, the table name would be "Custom Budget_V". This represents the Valid table in the staging database or data warehouse.
-
The next screen will prompt the user for the method in which to create the transfer.
The most desirable method is usually Copy data from one or more tables or views. Advanced users may prefer to Write a query to specify the data to transfer.
-
The next screen will be used to define the source and destination locations as well as edit the mappings between the source and destination. The destination should be the Valid table create in the previous step (in this example "Custom Budget_V").
-
Mapping should also be checked to make sure that the fields in the source and destination tables are mapped properly. This can be access from the "Edit Mappings" button.
-
The final screen will be used to run the transfer immediately. If the procedure was set up properly it will inform the user that all data was successfully transferred.
Further information on using the SQL Import/Export Wizard can be found in the Microsoft article below:
Comments