Updated June 8, 2023
Definition of SSIS Execute SQL Task
The execution of SQL tasks in SSIS is extremely useful, but it can lead to chaos for budding developers as they have a chance to use it occasionally. The Execute SQL is one of the important functions which has handy components in the integration of SQL server as it allows the user to execute Transact-SQL queries inside the existing current flow. It is used to return the result set which can be occupied by other functions in the SSIS package. In this article, we can look into the function and implementation of Execute SQL in a real-time environment.
SSIS Execute SQL Task Overview
The Execute SQL is used to return the output result set which should be implemented with necessary parameters and variables to push the data in and out of the statement of T-SQL which is followed by other functions. The Execute SQL deals with the result set, variables, and parameters and there are multiple scenarios where Execute SQL is implemented like the initial task is fetched from the table from the database and it is returned by another task with a single row as result. The next Execute SQL will fetch the value and store it in the row in the other table.
In another case, it can be used to retrieve the result as a multi-row which is called a complete result set. The user can also include control flow in the Execute SQL which is used to store the result set to the given variable. Foreach loop container is used in the variables to add the SSIS components.
In simple, the Execute SQL task is used to execute SQL statements, queries, or stored algorithms in the SQL integration server from the available SSIS packages. The user can implement Execute SQL to compile a single statement of SQL and at times it is used to compile multiple SQL statements also to run the data sequentially. The functions and processes available in Execute SQL task are as follows.
• Drop, alter, and create the views and tables
• The user can implement Execute SQL tasks to truncate views or tables before feeding the data into the table.
• It is possible to recreate the dimension of the fact table before injecting the data into the concerned table or views.
• The Execute SQL task is used to run the saved procedure or algorithms
• Store the result set as a variable, so that the developer can use the result set for some other tasks.
Configure SSIS Execute SQL Task
In this section, the configuration of Execute SQL task is shown with the example.
First, Drag the Execute SQL Task in SSIS Toolbar and drop it into the Control Flow region. Then double click on the task editor of Execute SQL task to make preferred settings.
The general tab of Execute SQL task is used to configure a few properties like name and description.
The unique and standard name should be provided in the name column and a suitable description like brief functionality of SQL task should be mentioned in the description window. It is a best practice to give a valid and detailed note in the description box and the developer understood better about the SSIS task.
The options tab of Execute SQL has a timeout, type conversion, and CodePage options. The TimeOut should be measured in seconds and specify the timeout connection in seconds. If there is a more time-consuming connection, then this connection will fail by default. So it should be set to zero which means infinite seconds. The CodePage should be given when converting the values of Unicode to variables. By default, the task will use the code page of the local host machine. In the type conversion mode, it is enabled to Execute SQL tasks to convert the result query and output values to the data type of the variable.
Choose the connection manager if the user wants to check on the data source. In the drop-down, there are many connection types available like Excel, ADO. ODBC, OLE DB, SQLMOBILE connection, and Ado.net. In the settings of the connection manager, choose a new connection or click the name of the existing connection according to the requirement.
In the SQL source type tab, there are three options like variables, file connection, and direct input. If the user wants to execute the SQL statement direct, choose direct input from the option. If the file system holds the SQL query, then the file connection should be selected. If there is variable data in the query choose a variable option from the menu.
The properties of Execute SQL help the user to configure the output. The following properties are none, full result, single row, and XML. If the user doesn’t want to return any values or anything, choose the none option. The none is used in case if the user wants to truncate the table by Execute SQL task because he is not returning any value. The single row option is chosen, if the user wants to return only a single row as the output result set. For example, if the user wants only the total number of records or the name of the student row, this option can be selected. If the user wants to return multiple rows as a final result set, then select the full result set option. XML option is used when the user wants to return any XML file as the result set.
In the result set tab of the Execute SQL option, select the add button to give the name of the result set. The result and variable name should be given in this window to establish a suitable connection. Both the result and variable name should be unique, standard, and meaningful as the user use this name in the subsequent task connection.
Conclusion
Hence, these are a few important properties and functions of Execute SQL task to be noted. All these should be properly configured to give and execute a meaningful task.
Recommended Articles
This is a guide to SSIS Execute SQL Task. Here we discuss the definition, overviews, Configure SSIS Execute SQL Task examples. You may also have a look at the following articles to learn more –
