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.
Double Click on the SQL component title bar to launch the SQL Editor Window.
Parameters section allows users to create input variables (by clicking on the 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.
Script (SQL) section is where the SQL query is entered.
The SQL component exposes Control In, Control Out and Data Out ports by default.
|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.|
To edit the properties of the SQL component, in the properties window, change the required property. Users can edit the following properties:
|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.|
Let us consider an example of create a table by using SQL component-
To use the SQL component:
In the Toolbox, expand
SQLcomponent and drop it on the
Double click on
Connection Providertextbox then
Port Propertieswindow will be display.
In that Window Select the Connection Provider from Static Value Drop down box (MSSQL is used in this example) and click on
Double click on
Connection Stringtextbox and write the Connection String based on which
Connection Providerselected.(Connection String for MSSQL: Data Source=” ”;Initial Catalog=" ";Integrated Security=False; User Id=" ";Password=" ";MultipleActiveResultSets=True).
Double click on the title bar of the SQL component.The SQL Editor dialog box is displayed.
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
Utilitiesand then expand
WriteToExcelcomponent and drop it on the Design surface.
To specify the file location, double-click
Filepathand enter the location of the file.
Double click on
SheetNametextbox and enter the SheetName.
Overrideand change the data source.
To learn more about overriding the data source of the data port, refer the Override section.
Connect all the control ports and data ports in the activity.
In the toolbar, Click
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.