Hadi Fadlallah
This image shows the how to assign an expression to SqlStatementSource property of the Execute SQL Task in SSIS

Execute SQL Task in SSIS: SqlStatementSource Expressions vs Variable Source Types

September 9, 2019 by

In this article, I will give an overview of Execute SQL Task in SSIS and I will try to illustrate some of the differences between writing an expression to evaluate SqlStatementSource property or writing this expression within a variable and change the Execute SQL Task Source Type to a variable.

This article is the fourth article in the SSIS feature face to face series which aims to remove confusion and to illustrate some of the differences between similar features provided by SQL Server Integration Services.

Execute SQL Task

From the SSIS toolbox item description, Execute SQL Task in SSIS is used to execute SQL statements or stored procedures in a relational database.

This image shows the Execute SQL Task in SSIS descripton from the toolbox

Figure 1 – Execute SQL Task description

The SQL statement can be a:

  • Single SQL Command
  • Multiple SQL Commands
  • Stored procedure

This shows the Execute SQL Task in SSIS icon when added to the package control flow.

Figure 2 – Execute SQL Task in SSIS

This Task need a connection manager to establish a connection with a data source and it supports several data sources other than SQL Server since it can use the following connection managers:

  • Excel: An Excel connection manager enables a package to connect to a Microsoft Excel workbook file
  • OLE DB: An OLE DB connection manager enables a package to connect to a data source by using an OLE DB provider
  • ODBC: An ODBC connection manager enables a package to connect to a variety of database management systems using the Open Database Connectivity specification (ODBC)
  • ADO: An ADO connection manager enables a package to connect to ActiveX Data Objects (ADO) objects, such as a recordset
  • ADO.NET: An ADO.NET connection manager enables a package to access data sources by using a .NET provider
  • SQL Compact (SQLMobile): A SQL Server Compact connection manager enables a package to connect to a SQL Server Compact database

Note that the SQL Statements syntax differs based on the data source provider, as example you have to write T-SQL when data source is SQL Server. But when it is Excel or Access, SQL commands have to be supported by Microsoft.Jet.OLEDB and Microsoft.ACE.OLEDB providers.

Is Excel a relational database?

As mentioned above, Execute SQL Task in SSIS is used to execute SQL statements on a relational database, while Microsoft Excel is not a database engine. Because Excel is widely used to store data, and many users want to retrieve data using SQL Statements, Microsoft has given the ability to query tabular data stored in Excel and text file using Jet and ACE OLE DB providers, which pretend that Excel is a relational database and tries to force some data integrity rules while reading, i.e. forcing a single data type for each column by only reading a dominant data type and converting all other values to NULL.

Execute SQL Task configuration

As shown in the image below, the Execute SQL Task in SSIS has many options that can be configured. This article will only focus on the SQL Statement configuration. If you need more details on other options, you can refer to the official documentation.

This image shows the Execute SQL Task in SSIS editor form

Figure 3 – Execute SQL Task editor

There are different methods to define the SQL Statement that we need to execute. You can select the method in the SQLSourceType property:

  • Direct Input: You can write the SQL Statement manually in SQLStatement property; it can be a simple query or you can just enter a stored procedure name and change the IsQueryStoredProcedure property to True
  • File Connection: You can select a text file or .sql file that contains an SQL Statement using a File Connection manager
  • Variable: You can select a variable that contains an SQL Statement; when changing the SQLSourceType property to Variable, a new property appears in the editor which is SourceVariable

Another method to set the SQL Statement is by using expressions, you can go to the Expression Tab, and select the SQLStatementSource property to write an expression. To learn more about writing expressions in SSIS, you can refer to the official documentation: Integration Services (SSIS) Expressions.

SqlStatementSource expression vs Variable Source Type

Many times I was asked on the difference between writing the SQL Statement within a variable and use it as a Source or using expressions to define the SQL Statement. And if one approach is more recommended.

In this section, I will first mention each method description from the official documentation of Execute SQL Task in SSIS, then I will try to clarify more based on my experience.

This image shows the how to assign an expression to SqlStatementSource property of the Execute SQL Task in SSIS

Figure 4 – SQLStatementSource expression

Official Documentation

Referring to the official documentation of Execute SQL Task in SSIS:

  • Variable Source: Set the source to a variable that defines the Transact-SQL statement. Selecting this value displays the dynamic option, SourceVariable
  • SqlStatementSource expression: When you use an OLE DB connection manager, you cannot use parameterized subqueries because the Execute SQL Task in SSIS cannot derive parameter information through the OLE DB provider. However, you can use an expression to concatenate the parameter values into the query string and to set the SqlStatementSource property of the task

Discussion

After mentioning each approach description, and based on my experience, I can say that there is no better approach, each one has its own use case.

As mentioned above using expression is needed if the SQL Command is built based on other variables, such as passing the table name dynamically, or passing parameter because Execute SQL Task in SSIS doesn’t support dynamic queries such as:

The first approach can be used if the developer decided to store the whole SQL Command inside a variable even if the variable is evaluated as an expression.

In case that variable is evaluated as an expression, there is no difference between both approaches from the SQL command perspective, but it may differ in case that the statement is used multiple times within the package. As an example of the query mentioned above, if the table name is variable and this query needs to be executed at different steps within the package, evaluating the variable as an expression is more efficient than writing the expression multiple times. Also, using a variable may facilitate the debugging process since it can be easily monitored.

In addition, as I remember, in the old SSIS versions, the variable source option was not available and it was added later (I think in 2012) version to facilitate the developer work in case the whole SQL command is stored inside a variable rather than adding one variable expression in SQLStatementSource, as an example if the query is built within a Script Task and stored in a variable.

In the end, it is up to you to decide which approach you feel more comfortable with based on what we have mentioned.

Table of contents

SSIS OLE DB Source: SQL Command vs Table or View
SSIS Expression Tasks vs Evaluating variables as expressions
SSIS OLE DB Destination vs SQL Server Destination
Execute SQL Task in SSIS: SqlStatementSource Expressions vs Variable Source Types
Execute SQL Task in SSIS: Output Parameters vs Result Sets
SSIS Derived Columns with Multiple Expressions vs Multiple Transformations
SSIS Data types: Change from the Advanced Editor vs Data Conversion Transformations
SSIS Connection Managers: OLE DB vs ODBC vs ADO.NET
SSIS Flat Files vs Raw Files
SSIS Foreach Loop vs For Loop Container
SSIS: Execute T-SQL Statement Task vs Execute SQL Task
SSIS Lookup transformation vs. Fuzzy Lookup transformation
SSIS Pivot transformations vs. Unpivot transformations
SSIS Merge Join vs. Merge Transformation
Data Access Modes in SSIS OLE DB Destination: SQL Command vs. Table or View
SSIS XML Source vs XML task
SSIS Script task vs. Script Component
SSIS term extraction vs. term lookup
Hadi Fadlallah
ETL, Integration Services (SSIS), SSIS monitoring

About Hadi Fadlallah

Hadi is an SQL Server professional with more than 10 years of experience. His main expertise is in data integration. He's one of the top ETL and SQL Server Integration Services contributors at Stackoverflow.com . Also, he published several article series about Biml, SSIS features, Search engines, Hadoop, and many other technologies. Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a MongoDB, Neo4j, and ArangoDB certified professional. On the academic level, Hadi holds two master's degrees in computer science and business computing. Currently, he is a Ph.D. candidate in data science focusing on Big Data quality assessment techniques. Hadi really enjoys learning new things everyday and sharing his knowledge. You can reach him on his personal website. View all posts by Hadi Fadlallah

168 Views