Hadi Fadlallah
This image shows how to add an OLE DB Connection manager from Visual studio

SSIS OLE DB Source: SQL Command vs Table or View

August 22, 2019 by

Introduction

SQL Server Integration Services provides a wide variety of features that helps developers to build a robust Extract, Transform, and Load process. After many years contributing to SSIS-related tags on Stackoverflow.com, I can say that many developers have some misunderstandings about SSIS features (SSIS OLE DB Source, SSIS Expressions, SQL Server destination …) especially those which are very similar and have some common usability.

This article is a part of a series called SSIS features face-to-face, which aim to remove any confusion and to illustrate some of the differences between similar features provided by SQL Server Integration Services.

When using a SSIS OLE DB Source, many Access modes can be used to read from the source:

  • Table or View: Select a Table or View from a drop-down list
  • Table name or View name variable: Choose a SSIS variable that contains the table or view name
  • SQL Command: Write your own SQL query (single table query or complex query)
  • SQL Command from variable: Select a SSIS variable that contains a SQL command

This image shows the data access mode dop down list in the SSIS OLE DB Source editor

Figure 1 – OLE DB Source Data Access mode

Many times I was asked on the difference between (1) using Table or View access mode and selecting specific Input Columns from the SSIS OLE DB Source and (2) using a SQL Command to select some columns from one table.

This image shows how to select specific column in SSIS OLE DB Source when using Table or View data access mode

Figure 2 – Selecting column in Table or View mode

This image shows how to use an SQL Command as source in SSIS OLE DB Source

Figure 3 – OLE DB Source SQL Command

In order to learn more about SSIS OLE DB Source, you can refer to the following official documentation provided by Microsoft:

The main reason to run this experiment was a question posted on Stack Exchange Database Administrators website. Due to its importance, I decided to write a related article with more details in order to give more explanation.

Experiments

In order to check the difference between these two options, I will run a small experiment using SQL Server profiler to check what is the command sent to a database in each method. The experiments will be conducted on the AdventureWorks database. In this section, I will describe each step in these experiments and illustrate the results.

Creating Test Packages

First of all, I will create two packages; each one contains a Data Flow Task where I will set up a SSIS OLE DB Source and a Row Count component.

Before adding these components, we have to create a new OLE DB connection manager in order to set up a connection with the database. Just right-click on the Connection Managers tab and click on New OLE DB connection manager:

This image shows how to add an OLE DB Connection manager from Visual studio

Figure 4 – Adding an OLE DB connection manager

Then you have to set up your connection by choosing the server name, authentication parameters and the database name (AdventureWorks) as shown in the image below:

This image shows the OLE DB Connection manager form

Figure 5 – OLE DB connection manager configuration

After configuring the OLE DB Connection manager, we must add a Data Flow Task. And within the Data Flow Task, we have to add a SSIS OLE DB Source and a Row Count Transformation (dummy task) as we mentioned above.

In the first package, we will configure the SSIS OLE DB Source to read from [Sales].[Customer] Table using Table or View Data access mode, and I will select CustomerID and AccountNumber columns as Input.

This image shows how to select a Sales.Customer as source in a SSIS OLE DB Source when using Table or View data access mode

Figure 6 – Selecting Customer Table as Source

This image shows how we selected CustomerID and AccountNumber columns from the SSIS OLE DB source editor

Figure 7 – Selecting Input columns

In the other package, I will configure the SSIS OLE DB Source to read from the following SQL Command:

This image shows how we configured the Second OLE DB Source to read from the following commnad: SELECT CustomerID, AccountNumber From Sales.Customer

Figure 8 – Configuring second SSIS OLE DB Source

Create and Start a Profiler Trace

Before executing the created packages, we have to create and configure a Trace using SQL profiler in order to monitor all commands executed on the AdventureWorks database.

If you are interested to learn more about SQL profiler, you can refer to the following Microsoft articles:

From the SQL Server Management Studio, Go to Tools menu strip and click on SQL Server Profiler:

This image shows how we open SQL Server profiler from SQL Server Management Studio

Figure 9 – Opening SQL Profiler

Then a connection dialog appears where you have to set up a connection with the same Instance you already connected in SQL Server Management Studio.

After connecting to the Server, we have to configure the Trace as the following:

First, we have to select the Tuning Template and to set the trace destination to a new Table called OLEDBSourceTest as mentioned in the images below:

This image shows how we configred the TSQL Server profiler Trace

Figure 10 – Configuring Trace

This image shows how to create a destination table to store the Trace log within SQL Server

Figure 11 – Saving Trace to a Table

After that we must go to the Event Selection Tab and click on Column filters button, then we have to filter on AdventureWorks2017 database as shown in the images below:

This image shows how the SQL Profiler Event Selection Tab

Figure 12 – Event Selection Tab

This image shows how to filter events based on database name

Figure 13 – Setting up the filter

Now, we have to run the Trace.

Note – it is more preferable to create the trace destination table in a different database to prevent showing unwanted queries in the trace log.

Results

After executing the first package (Table or View approach), the following command is logged by the SQL profiler:

This image shows how the SQL Profiler logged the first SSIS OLE DB Source activity

Figure 14 – First command retrieved from the profiler Trace

Moreover, after executing the second package the following command is logged by the SQL profiler:

This image shows how the SQL Profiler logged the second  SSIS OLE DB Source activity

Figure 15 – Second command retrieved from the profiler Trace

Discussion and Conclusion

After running this experiment, we can figure that even if we select some specific columns while using Table or View data access mode in a SSIS OLE DB Source, a SELECT * command is executed on the database engine and columns are filtered within the SSIS package which is different from using a SQL command to select specific columns since the second choice will only read these columns from the database engine and not filters are applied in SSIS.

Based on that, we can conclude that using the SQL command is more efficient from the performance perspective since it will decrease the memory usage and the amount of data retrieved. But make sure that the SQL command has its needed indexes created. Also, it will be more efficient to have the primary key columns included even if they are not needed.

Also, these results can be applied to Lookups Transformations since they also use the same Data access methods, and be aware that in lookups selecting needed columns is more critical since it affects performance more, especially when handling a huge volume of data.

Helpful Links

You can refer to the following links to learn more about monitoring the SQL Server Database Engine and some SSIS best practices:

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