Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Go Programs
    • Python Programs
    • Java Programs

Execute SQL Task in SSIS

by suresh

The Execute SQL Task in SSIS is used to run SQL queries (statements) or stored procedures from the SSIS package. You can use this SSIS Execute SQL task to write the single SQL statement or multiple SQL statements that can run sequentially.

We can use this SSIS Execute SQL Task for the following purposes:

  • Create, Alter, and drop the tables and views.
  • Before we start inserting the data into the table, we can use this task to truncate tables or views
  • Recreating (drop and create) the dimension and fact tables before loading the data into them.
  • Run stored procedures.
  • Save the result set in a variable so that we can use that data in other tasks.

Execute SQL Task in SSIS Properties

In this article, we will show you the steps involved in configuring the Execute SQL Task in SSIS with an example. First, Drag the Execute SQL Task in SSIS Toolbar and drop it into the Control Flow region

Execute SQL Task in SSIS 1

Double click on it will open the SSIS Execute SQL Task Editor to configure it.

SSIS Execute SQL Task General Tab

This tab is used to configure the SSIS Execute SQL Task properties.

  • Name: Please provide the Unique Name
  • Description: Briefly describe the SQL Task Functionality. It is always a good practice to provide a valid description.

In real-time, please change the Task name to an appropriate name, and write the description to explain the task functionality. The description helps other developers to understand the SSIS task better.

Execute SQL Task in SSIS 2

SSIS Execute SQL Task Options Tab

  • TimeOut (in seconds): Please specify the connection timeout in seconds. If the connection takes more than that time, then the connection will fail. By default, it is 0, and it means infinity seconds.
  • CodePage: Please specify the code page that the task wants to use while converting the Unicode values in variables. By default, it will use the local computer code page.
  • Type Conversion Mode: By default, it is Allowed, and it means the execute SQL task will attempt to convert the query results and output parameters to data types of the variable
Execute SQL Task in SSIS 3

Connection Type

Please select the Connection Manager you want to use for the data source. By clicking the down arrow, you can see the list of available connection types provided by the Execute SQL Task in SSIS. And they are OLE DB, EXCEL, ODBC, ADO, ADO.NET, and SQLMOBILE

Execute SQL Task in SSIS 4

Connection

Configure the Connection Manager settings for the SQL Statement. If you already created the Connection, then select from the list or else click <New connection…> to create a New Connection.

Execute SQL Task in SSIS 5

SQL Source Type

Please specify, How you want to provide your source data. This SSIS Execute SQL Task provides three options.

  • Direct Input: If you want to write an SQL Statement directly, then select this option.
  • File Connection: If your SQL Query is in the file system, then please select this option
  • Variable: If your query is in a variable, please choose this option.
Execute SQL Task in SSIS 6

The next property completely based on the SQL Source Type option. For example, if we select the File system as the source type, the following property will change to File Connection. And we can use this property to locate the select the file.

Execute SQL Task in SSIS 7

For example, if we select the Direct input as the source type, the next property will be changed to SQL Statement. And we can use this property to write the SQL statement. To demonstrate, we are writing the following query.

SELECT [Id]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
FROM [SQL Tutorial].[dbo].[Employee Table]
WHERE [Sales]  > ?
Execute SQL Task in SSIS 8

IsQueryStoredProcedure

If you selected the Connection type as ADO or ADO.NET, then only the property value is enabled. If your query is a stored procedure, change the property value to True.

Result Set

This SSIS Execute SQL Task property helps us to configure the output. It has four properties:

  • None: If you don’t want to return anything, select this option. For example, you are using the Execute SQL Task to truncate the table. Then there is no point in returning any value.
  • Single Row: If you want to return a single row as the result set, then you can use this option — for example, the total number of records in a table, or columns, etc.
  • Full Result Set: If you want to return more than a single row as the result set, then you can use this option.
  • XML: If you want to return the XML file as the result set, you can use this option.
Execute SQL Task in SSIS 9

Here is the final look of our configured SSIS execute SQL task

Execute SQL Task in SSIS 10

SSIS Execute SQL Task Parameter Mapping Tab

You have to use this SSIS Execute SQL Task Parameter mapping tab to map the parameters that we specified in our query. If you don’t have any parameters in your query, quit this section.

If you observe the above SQL statement, we specified the query as Sales > ?. It means we have to pass the value (or variable) to that question mark. So, please click on the Add button to add the result set name.

Execute SQL Task in SSIS 11

Once you click on the Add button, it will enable you to map the variable to the parameter.

Execute SQL Task in SSIS 12

Variable Name: This property displays the list of the available system variable, and user variables (if any), Please select the appropriate variable. If you haven’t created any variable before, please click on the <New Variable..> as shown below

Execute SQL Task in SSIS 13

Execute SQL Task Result Set Tab

You have to use this SSIS Execute SQL Task Result Set tab to map the result that we are getting from our query. Remember, if you specified the Result set property as None, then this tab is disabled.

Please click on the Add button to add the result set name.

Execute SQL Task in SSIS 14

Here,

  • Result Name: Please specify the valid name that you want to assign for the result set. I suggest you use the meaningful name because we will use this name in a subsequent task that is a connection to this execute SQL task.
  • Variable Name: This property displays the list of available user variables (if any). Please select the appropriate variable. If you haven’t created any variable before, please click on the <New Variable..>.
Execute SQL Task in SSIS 15

Execute SQL Task Expressions Tab

It is used to write expressions.

Execute SQL Task in SSIS 16

Placed Under: SSIS

  • What is SSIS
  • Install SQL Server Data Tools
  • Learn SSIS in 28 Days
  • Create a SSIS Project
  • Create SSIS Package
  • SSIS Connection Manager
  • OLE DB Connection Manager
  • SSIS ADO Connection Manager
  • ADO.NET Connection Manager
  • SSIS Cache Connection Manager
  • SSIS Excel Connection Manager
  • SSIS File Connection Manager
  • SSIS FTP Connection Manager
  • SSIS SMO Connection Manager
  • Source Assistance in SSIS
  • Flat File Source in SSIS
  • OLE DB Source in SSIS
  • Excel Source in SSIS
  • ADO.NET Source in SSIS
  • SSIS FLAT FILE Destination
  • SSIS OLE DB Destination
  • SSIS ADO.NET Destination
  • SSIS Transformations
  • SSIS Audit Transformation
  • SSIS Aggregate Transformation
  • SSIS Aggregate Advanced Mode
  • SSIS Cache Transformation
  • SSIS Character Map
  • SSIS Conditional Split
  • SSIS Copy Column
  • SSIS Data Conversion
  • SSIS Derived Column
  • SSIS Export Column
  • SSIS Fuzzy Grouping
  • SSIS Fuzzy Lookup
  • SSIS Import Column
  • SSIS Lookup Introduction
  • Lookup – OLE DB Connection
  • SSIS Lookup in Full Cache Mode
  • SSIS Lookup – Case Sensitivity
  • SSIS Merge Transformation
  • SSIS Merge Join Transformation
  • SSIS Merge Join – Left Outer Join
  • SSIS Merge – Right Outer Join
  • SSIS Merge Join – Full Outer Join
  • SSIS Multicast Transformation
  • SSIS OLEDB Command
  • OLEDB Command – Delete Data
  • OLEDB Command-Update Data
  • SSIS Percentage Sampling
  • SSIS Pivot Transformation 2008
  • SSIS Pivot Transformation
  • SSIS Row Count Transformation
  • SSIS Row Sampling
  • Script Component as Source
  • Script Component as Destination
  • SSIS Script as Transformation
  • SSIS Sort Transformation
  • SSIS SCD Type 0
  • SSIS SCD Type 1
  • SSIS SCD Type 2
  • SSIS Term Lookup
  • SSIS Term Extraction Intro
  • Term Extraction – Extract Nouns
  • SSIS Extract Noun Phrases
  • Extract Nouns & Noun Phrases
  • Term Extraction – Exclusion Tab
  • SSIS Unpivot Transformation
  • SSIS Union All Transformation
  • SSIS For Loop Container
  • SSIS ForEach File Enumerator
  • SSIS ForEach SMO Enumerator
  • SSIS ForEach Variable
  • SSIS Foreach NodeList
  • Foreach ADO.NET Schema Rowset Enumerator
  • SSIS Bulk Insert Task
  • SSIS Data Profiling Task
  • Execute T-SQL Statement Task
  • SSIS Execute SQL Task Intro
  • SSIS Execute SQL Task Example
  • Execute SQL Task- Single Rowset
  • Execute SQL Task – Full Row Set
  • SSIS Execute Package Task
  • Execute Packages in SQL Server
  • Execute Packages in File System
  • SSIS Execute Package Project Reference
  • SSIS File System Task
  • SSIS File System Task- Copy Files
  • File System Task-Copy Directory
  • File System Task – Delete Files
  • File System Task – Delete Folder
  • File System Task -Move Directory
  • SSIS File System Task -Move File
  • File System – Move Multiple files
  • File System Task – Rename File
  • File System Task – Set Attributes
  • SSIS FTP TASK
  • SSIS Create Local Directory
  • SSIS Create Remote Directory
  • SSIS FTP Task Send Files
  • SSIS FTP – Send Multiple Files
  • SSIS FTP Task Delete Local Files
  • FTP TASK Delete Local Directory
  • FTP Task Delete Remote files
  • SSIS Delete Remote Directory
  • SSIS FTP Task Receive Files
  • SSIS FTP Receive Multiple Files
  • SSIS Script Task
  • Transfer SQL Server Objects Task
  • Transfer SQL Table Structures
  • Transfer SQL Tables with Data
  • Transfer SQL Stored Procedures
  • Transfer User Defined Functions
  • Transfer SQL Views in SSIS
  • SSIS Web Service Task
  • SSIS XML Task-Validate XML File
  • Transform XML File using XSLT
  • XML Task-XML files Differences
  • Create SSIS Catalog
  • Package Deployment using BIDS
  • Deploy Package Using SQL
  • Deploy using SQL Server Wizard
  • SSIS Breakpoints
  • SSIS Checkpoints
  • SSIS Error Handling
  • SSIS Event Handlers
  • SSIS Transactions
  • SSIS Logging
  • SSIS Parameters
  • SSIS Package Configuration
  • Configure using SQL Server
  • Config using Registry Entry
  • Conf with Environment Variable
  • SSIS XML Configuration File
  • XML Configuration File Part 2
  • SSIS Package Protection Level
  • SSIS Incremental Load
  • Incremental Load Example 2
  • SSIS Remove Double Quotes

Copyright © 2021 · All Rights Reserved by Suresh

About Us | Contact Us | Privacy Policy