- From the User Interface locate the query you would like to execute, right-click on the query and click 'run query'. This action will instruct ICA to execute the data source query immediately to bring the data from the source system in to the staging tabled defined for the query.
Figure 1 - This figure depicts how you would run a query through the ICA UI to bring data into the staging area
- After the query has executed, launch SSMS and locate the ImportRuleMappingID associated to the data source query we have just executed. The Import Rule Mapping ID will be needed in order to move the data from staging into the pre-processing area. We will be passing the import rule mapping ID as a parameter to the stored procedures to move the information from staging into ICA.
- SELECT * FROM IW_ImportRuleMapping
Figure 2 - The figure above is a sample query that will provide us the import rule mapping id for the organizations mapping. We will note that the ImportRuleMappingID will be used in the next step to begin the process of loading the data into pre-processing.
- In this step in the process we will need to obtain the pre-processing table information from the IW_PreProcessTable as the process ‘spIW_RunImportRuleMappingEntityCreation’ will require these parameters when the data is moved from the pre-processing table to the tables what will store the data in ICA. In this example we will lookup the pre-process table information for Organizations.
- Make a note of the PreProcessTableID for Organizations, in this case it is 10. This information will be passed to the parameter @i_nPreProcessTableID when we invoke the process ‘spIW_RunImportRuleMappingEntityCreation’.
- Make a note of the PreProcessTableName for Organizations, in this case it is Stg_PreProcess_Organizations. The actual value that will be passed to the parameter @i_sSourceTableName is a combination of the PreProcessTableName_ImportRuleMappingID. In this use case we will pass the value of Stg_PreProcess_Organizations_6. The number 6 is the value for the import rule mapping id in step 2 of this process.
Figure 3 - Sample output from the IW_PreProcessTable
- Now that you have the ImportRuleMappingID from step 2, we will begin the process of moving data from staging into the pre-processing area. The stored procedure ‘spIW_RunImportRuleMappingStaging’ will need to be executed in order to move the data from staging into pre-processing. A sample statement to move the data from staging to pre-processing is listed below.
- exec spIW_RunImportRuleMappingStaging @i_nImportRuleMappingID=<XX>
- exec spIW_RunImportRuleMappingStaging @i_nImportRuleMappingID=6
- Once the data has been moved over into the pre-processing area we will need to move the data from pre-processing into LDW. In order to move this data from staging to pre-processing you will need to execute the following stored procedure.
- exec spIW_RunImportRuleMappingEntityCreation @i_nImportRuleMappingID=<XX>,@i_nPreProcessTableID=<XX>, @i_sSourceTableName=<XX>
- The variable @i_nImportRuleMappingID is Identified in Step 2.
- The variable @i_nPreProcessTableID is equates to the PreProcessTableID returned in 3.A
- The variable @i_sSourceTableName is a combination of the PreProcessTableName and the ImportRuleMappingID it is returned in step 3.B.
- The actual statement to move the data is as follows:
- exec spIW_RunImportRuleMappingEntityCreation @i_nImportRuleMappingID=6, @i_nPreProcessTableID=10, @i_sSourceTableName=Stg_PreProcess_Organizations_6