Home > Articles > SQL Server Articles > SSIS: Execute SQL task component

SSIS: Execute SQL task component

by Rahul Pyarelal   on Feb 04, 2017   Category: SQL Server  | Level: Beginner  |  Views: 980    |  Points: 100   
Like this article? Bookmark and Share:
In this article we will know Execute SQL task component in SSIS.

SQL Execute task provide helping hand to do execute SQL Command. SSIS execute SQL task is also used to retrieve information from a database. The Execute SQL Task is also found in the legacy DTS product, but the SSIS version provides a better configuration editor and methods to map stored procedure parameters to read back result and output values.

The task can execute a SQL command basically in two ways: by executing inline SQL statements or by executing stored procedures. The resulting action can also result in the need to perform one of two options: accepting return values in parameters or a result set. You can get an idea of how the task can be configured to do these combinations in the “General” tab of the Execute SQL Task Editor.

Execute SQL Task in SSIS

We will insert recordin“EmployeeTarget” table using “Execute SQL Task”.

For this Demo we have a simple Database created called as “Pivot & UnPivot transaction Demo” and with the table call “EmployeeTarget”. This table contain three column that is EmpID, EmpName, EmpAdd. EmpID is the integer data type, EmpName and EmpAdd is the varchar data type as see the below screen short…


Step 1

First go to the “connection manager” and Create the connection manager. In order to do that, right click on the Connection manager and select the “New ADO.NET Connection manager” as see the below screen short…


When you select the “New ADO.NET connection” it opens the new window and click “New” then configure the “Connection manager” as see the below screen short…


After configuring the connection manager click ok.

Step 2

First go to the SSIS Toolbox and drag the “Execute SQL Task” in the Control Flow as see the below screen short…


When you drag the “Execute SQL Task” in the Control Flow than configure the Task. In order to that, right click on the “Execute SQL Task” and say Edit. Then go to the “connection type” and select the “ADO.NET”. Go to the Connection and Select the connection which will created in the “Step 1” as see the following screen short…


In this demo we will use “Direct input”.

Direct input --> Do you wants to write the Query in the “SQL Statement” then select “Direct input”.

File connection--> it want to tack query from text file than select the “file Connection”. Than create a File Connection manager which will connect to that text file.

Variable-->it you want to put some query in the “Variable” then selects Variable.

When select the “Direct input” than put the SQL query under the “SQL Statement” as see the following screen short…


Package exacting completes than run the application as see the following screen short…


Dynamitic value

In order to do Parameters query (Dynamitic value) go to the“Execute SQL Task Editor” and change the SQL Server Statement that is “insert into EmployeeTarget (EmpName, EmpAdd) values (@EmoName,@EmpAdd)”as see the following screen short…


Now create two parameter and parameter name is “P_EmpName and P_EmpAdd” and Data type is “String”. Value of parameter is DefName and DefAdd as see the following screen short…


This parameter map with the SQL Server parameter.

Then go to the “Execute SQL Task Editor” and click on the “Parameter mapping”. Create the two Parameter and Select the “Variable Name”, “Data type” and “Parameter Name” as see the following screen short…


Package exacting completes than run the application as see the following screen short…


If you want to change the value of the EmpName and EmpAdd in the SQL Server. In order to that go to the “Package1.dtsx[Design]” and right click say open containing Folder. Go to the binà Development --> Double click the ExecuteQuery. Its open the “Integration Services Deployment Wizard” and put the File path than click on the “next” as see the following screen short…


Now Browse the SQL Server name and project path name than click to “Next” as see the following screen short…


Than Deploy the Service and see the Result in the following screen short…


Now go to the “Integration Services Catalogs”in the SQL Server and right click on the “Package1.dtsx”say “Execute” as see the following screen short…


Now here change the Parameter value under the “Execute package –

Package1.dtsx(SSISProjectName)” as see the following screen short…


You can see the changes in the SQL Server output as see the following screen short…


Also do not forget to view below video on SSIS project which will demonstrate to create SSIS right from start: -



Like this article? Bookmark and Share:

Most viewed Articles

User Comments


No response found, be the first to review this article.

Submit feedback about this article

Please sign in to post feedback

Latest Posts

1 ???????? ????????? New! Forum