SQL

The SQL component enables users to write the SQL queries. Using this component, users can execute queries to perform multiple operations like Create table, Insert, Delete etc.

Screenshot

Supported Functions

Double Click on the SQL component title bar to launch the SQL Editor Window.

Screenshot

  1. Parameters section allows users to create input variables (by clicking on the Screenshot icon) and use then in the SQL Query. Once a parameter is created, it can be renamed, and its data type changed. For each parameter which is created, SQL component will expose a Data In port.

  2. Script (SQL) section is where the SQL query is entered.

Ports

The SQL component exposes Control In, Control Out and Data Out ports by default.

Port Description
Control In Must be connected to the Control Out port of one or more components.
Control Out Must be connected to the Control In port of any other component.
Data In- Dynamic Must be connected to Data out ports from where input values are to be derived (Object).
Data In The SQL component exposes following Data In ports:
Connection Provider: Specifies Database types.
Connection String: Specifies the connection string related database type.
Data Out Must be connected to the Data In port of another component from where data is to be collected. SQL component can return any type of data, such as objects, stings or integers.

Properties

To edit the properties of the SQL component, in the properties window, change the required property. Users can edit the following properties:

Property Description
Search Search for the respective property.
Delay After Execution Specifies the wait time (in seconds) after the action is executed.
Delay Before Execution Specifies the wait time (in seconds) before the action is executed.
Name Specifies the name for the SQL component.Always set the name to match the application field the user is interacting with.

Example

Let us consider an example of create a table by using SQL component-

Screenshot

To use the SQL component:

  1. In the Toolbox, expand General category.

  2. Drag the SQL component and drop it on the Design Surface.

  3. Double click on Connection Provider textbox then Port Properties window will be display.

  4. In that Window Select the Connection Provider from Static Value Drop down box (MSSQL is used in this example) and click on ok.

  5. Double click on Connection String textbox and write the Connection String based on which Connection Provider selected.(Connection String for MSSQL: Data Source=” ”;Initial Catalog=" ";Integrated Security=False; User Id=" ";Password=" ";MultipleActiveResultSets=True).

  6. Double click on the title bar of the SQL component.The SQL Editor dialog box is displayed.

    Screenshot

  7. In the Script(SQL), the user has to write a query whihc is to be executed.In this example , lets write some queries to create a table, insert data into the table and select data from the table and Click on Ok.

    Screenshot

  8. Expand the Utilities and then expand DataWriter.

  9. Drag the WriteToExcel component and drop it on the Design surface.

  10. To specify the file location, double-click Filepath and enter the location of the file.

  11. Double click on SheetName textbox and enter the SheetName.

  12. Select Override and change the data source.

    Note

    To learn more about overriding the data source of the data port, refer the Override section.

  13. Connect all the control ports and data ports in the activity.

  14. In the toolbar, Click Run.

    • In this automation SQL Component will create a new table and insert the given data.

    • The automation will return that data to transfer to a specified destination.