Prerequisite

Installed connectors File Reader, Base64 to Text Converter, CSV Parser, Microsoft SQL database (see tutorial 1. Connector) and created DX Agent local (see tutorial 3. Agent)

Overview

In the following tutorial, we create a new integration task that loads the CSV file and imports the content into a Microsoft SQL database. Then we explain how to work with the contents of a Base64 file, its conversion to CSV format, data access and the difference between bulk and record-by-record data processing.

Part of the solution is using external DX Agent to get resources from your Windows machine. In our case, we set the reading CSV task step to use Agent on your machine to read CSV file.

1. Login

Log in to Xeelo DX and press the Tutorials tile in the Companies section. If you do not see the company Tutorials, it is necessary to create it (see tutorial 2. Hello World!)

2. Creation of integration task

Press Task tile and press Add. Integration task editor appears. It consists of the task name and control panel, the information panel below it and the integration steps. Change the name of the integration task and press the check mark

3. Creation of Read CSV file

1. Press the green + button to create a new Integration Task Step
2. The integration step editor appears
3. At the top, name it according to the task it will perform: Read CSV file
4. Underneath you can find Agent which is responsible for task execution. The CSV file fruits.csv (download link) supppose to be downloaded on you machine so change it to Agent local
5. Under the name, change the Data snapshot to Everytime, giving you the ability to browse the data at the output of the integration step.
6. In the Connector section, press the magnifying glass to find the File Reader and select the File Reader connector from the result. You may notice that the Output schema section is automatically populated with predefined values
7. In the Configuration section:
• Select Read read file mode from Working mode
• Fill in the Path: C: \ DX-Demo \ fruits.csv
• Press the green floppy disk button and save
8. Finally, press the green Save & Close button at the top right of the screen. After saving, the first integration step of the task is displayed. It can be re-edited using the Cog wheel button on the integration step

4. Creation of follow-up Convert Base64 to Text step

1. Press the green + button, located at the bottom of the Read CSV file integration step
2. Name it at the top: Convert Base64 to Text
3. Change Data snapshot to Everytime
4. In the Connector section, press the magnifying glass to find Base64 and select Base64 to Text Converter from the result. Again, you may notice that the Output schema section is automatically populated with predefined values.
5. In the Input schema section, it is necessary to pass data from the output of the Read CSV file step to the input of the Convert Base64 to Text step. This can be done in two ways:
• In the Input mapping section, find the Data parameter, press plus and select Data
• In the Input mapping section, move to the double arrow next to Read CSV file and press. The output from the previous step is automatically mapped to the input of the current step
6. In the Configuration section:
• Select Encoding: Unicode
• Press the green floppy disk button and save
7. Finally, press the green Save & Close button at the top right of the screen. After saving, the subsequent integration step of the task is displayed

5. Creation of follow-up Parse CSV from Text

1. Press the green + button located at the bottom of the Convert Base64 to Text integration step
2. Name it at the top: Parse CSV from Text
3. Under name, change Data snapshot to Everytime
4. In the Connector section, press the magnifying glass to search for CSV and select CSV Parser from the result
5. In the Input schema section, it is necessary to pass data from the output of the Convert Base64 to Text step to the input of the Parse CSV from Text step. Again, this can be done in two ways:
• In the Input mapping section, find the Text parameter, press plus and select Text
• In the Input mapping section, move to the double arrow next to Convert Base64 to Text and press. The output from the previous step is automatically mapped to the input of the current step
6. In the Output schema section, add the following:
• ID: Integer
• Fruit: String
• Price: Double
• Press the green floppy disk button, name the output scheme Fruits and save
7. In the Configuration section:
• Check CSV Header
• Press all the green diskette buttons in the section one at a time to save the configuration
8. Finally, press the green Save & Close button at the top right of the screen. After saving, the subsequent integration step of the task is displayed

6. Creation of follow-up step Insert data into SQL table - Row by row

1. Press the green + button, located at the bottom of the Parse CSV from Text integration step
2. Name it at the top: Insert data into SQL table - Row by row
3. Under the name, change:
• Input processing on Row by Row Sequentially
• Data snapshot to Everytime
4. In the Connector section, press the magnifying glass to find SQL and select the Microsoft SQL Database connector from the result
5. In the Input schema section:
• Press the blue table button on the right side of the Structure subsection
• Locate the already saved Fruits schema
• Press the Load schema button
6. In the Input schema section, it is also necessary to pass data from the output of the Parse CSV from Text step to the input of the Insert data into SQL table step. In the Input mapping section, move to the double arrow next to Parse CSV from Text and press. The output from the previous step is automatically mapped to the input of the current step
7. In the Output schema section, fill in the following data and save it under the name Inserted:
• Inserted: Integer
• Press the green floppy disk button and save
8. In the Configuration section:
• Fill in the connection string to the SQL database in the form below and save it under the name SQL connection:
  Server =; Database =; User ID =; Password =; Trusted_Connection = False; Encrypt = True; TrustServerCertificate = True; Connection Timeout = 600
• Add the script below to SQL Statement and save it under the name Get Fruits:
  SELECT [id], [fruit], [price] INTO #dx_demo_fruits FROM #InputData SELECT @@ROWCOUNT AS [Inserted]
9. Finally, press the green Save & Close button at the top right of the screen. After saving, the subsequent integration step of the task is displayed

7. Creation of parallel step Insert data into SQL table - Bulk

1. Press the green + button, located in the right part of the integration step Insert data into SQL table - Row by row
2. Name it at the top: Insert data into SQL table - Bulk
3. Under the name, change:
• Input processing on Bulk
• Data snapshot to Everytime
4. In the Connector section, press the magnifying glass to find SQL and select the Microsoft SQL Database connector from the result
5. In the Input schema section:
• Press the blue table button on the right side of the Structure subsection
• Locate the already saved Fruits schema
• Press the Load schema button
6. In the Input schema section, it is also necessary to pass data from the output of the Parse CSV from Text step to the input of the Insert data into SQL table step. In the Input mapping section, move to the double arrow next to Parse CSV from Text and press. The output from the previous step is automatically mapped to the input of the current step
7. In the Output schema section:
• Press the blue table button on the right side of the Structure subsection
• Locate the already saved Inserted schema
• Press the Load schema button
8. In the Configuration section:
• Press the blue table button to the right of the Connection String subsection
• Locate the already saved SQL connection
• Press the Load configuration button
• Press the blue table button to the right of the SQL Statement subsection
• Locate the already saved Get Fruits
• Press the Load configuration button
9. Finally, press the green Save & Close button at the top right of the screen. After saving, the subsequent integration step of the task is displayed

8. Execution of integration task:

1. Switch the Runnable / Not runnable checkbox at the top of the screen to the Runable - green backlight state. You may notice that at the same time, the History / Design switch changes to the left.
2. Refresh the Xeelo DX cache using the gray recycle button at the top right of the screen, next to the profile. In this way, Xeelo DX loads the integration task and its steps into the cache for faster integration
3. Press the green Start button next to the History button. A green message will appear in the lower right corner
4. On the left side of the screen, press the Reload task runs button
5. Press the orange Data snapshot button on the last green integration steps and compare the outputs