Register an ODBC Extractor
This topic describes the steps to register and configure an ODBC extractor in order for data to egress to FactoryTalk DataMosaix.
Prerequisites
- A project is created using FactoryTalk DataMosaix.
Register an ODBC Extractor
- On the Management Console, select theExtractor Infotab.The Extractor list displays.
- Click the [Add] icon.TheExtractor Info Detailscreen displays.
- Provide a unique and meaningful name for your Extractor. The Source Name works as the identifier for your extractor. You can search for an extractor with the Source Name. By default, DataMosaix populates a source name.Source Name example: EXT_1406230728_createproject_v1.Here, EXT stands for extractor, 140623 is the date in ddmmyy format, 0728 is the timestamp in hhmm format, and createproject_v1 is the project in which you are currently working. You can use the default source name or create your own.
- Provide the location details for which you are creating the extractor. This is additional optional information that can help you identify an extractor when you have many extractors for different plants/sites/locations.
- From the [Associated Roles] drop-down list, select at least one role. You can refer to various roles and capabilities by clicking [Role Reference].You can select multiple roles from the Associated Roles drop-down list. In the search bar, you can search for any specific role from the list. If you want to apply all the roles to the application, select the [Select All] check box.NOTE:The Associated Roles drop-down is visible and available only for Project Admin users or users within the Organization with a Project Admin Role assigned.Next, you can select a pipeline or create a new pipeline. When you create a new pipeline, you have the option of selecting from an existing dataset or creating a new dataset.
- From the pipeline drop-down, select a pipeline. On selecting a pipeline, the Pipeline External ID and Dataset Name fields are populated.To create a new pipeline, select theCreate Pipeline +option.The Create Pipeline dialog box displays.
- Provide the following Pipeline information to create a new pipeline.
- Pipeline Name
- Pipeline External ID
- Pipeline Description
- When you create a pipeline, you can either select an existing dataset or create a new dataset. Select a Dataset from the drop-down if you want to use an existing Dataset.If you choose to create a new Dataset, the Dataset related fields are displayed. Provide the following dataset information.
- Dataset Name
- Dataset Description
- ClickAdd. Based on the options you choose (select or create), the Pipeline and Dataset information is either populated or created and displayed on the Extractor Info Detail page.
- Select the source type of extractor as ODBC.The fields relevant to the ODBC extractor appear on the screen for you to fill in details.
- In the Table field, provide a table name. If this table does not exist, it will be created inside the database that is created in step 7.
- In the Raw database field, provide a database name. If this database does not exist, the database is created in the cdf project.
- In the Connection String field, enter the ODBC connection string to establish the connection.
- In the Primary Key field, provide a primary key for the table. For example, ID
- In the Query field, enter a query to fetch records.NOTE:TheSave and Applybutton enables after you provide all the required details.
- Click Save and Apply.The Generated info Saved pop-up window displays that contains the configuration information. You can view the configuration file details by clickingView generated configuration file. Alternatively, you can also copy and save the information for future use by clicking Copy All. More importantly, you can download the .yml file that includes all the configuration information that you can use to run the extractor.
- ClickDownload.The .yml file is downloaded in the Downloads folder of your local machine.
- ClickCloseto close the pop-up window.The new extractor is displayed under Extractor Listing.
- Navigate to your project in CDF by clicking [Continue to Main Site] and selectIntegrate > Prepare data for transformation.
- Click [Create Database].
- Click [Create table]. Provide a unique name for the table. The table name cannot be changed later.
- Select [Create empty table] and click [Create].
- Download the extractor to your local machine.
- Copy the downloaded .yml file to the folder where the extractor is installed.
- Run the extractor on the command line.
- Once the extractor has been configured and executed successfully, navigate to your project in CDF and open the pipeline you created earlier.
- Click the pipeline link to verify the pipeline run details.
- SelectIntegrate > Prepare data for transformationto verify if the data is inserted into the table.
Troubleshooting ODBC Extractor
- Use a yml Lint site to validate your yml file.
- View the log file in the logging folder (as configured in the configuration file) for any error messages.
- Double-check your connection string.
- Double-check that all references to dataset and pipeline IDs are correct.
- Double-check that the client ID and secret in the on-prem configuration file is correct.
- If you start the extractor but data fetched successfully but error while pushing data into CDF check once the scopes are properly assigned in auth0.
- If extractor ran successfully and extraction is successful but no data pushing into cdf or only few rows are getting pushed (not whole data), then check primary key which you are defining in config.yml file is present in source table or not.
- If extractor ran successfully but no data extracted then try to run the query defined in queries section of config.yml file into the sql server.
Provide Feedback