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

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.

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

• SSIS OLE DB Source
• Extract Data by Using the SSIS OLE DB Source

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.

• You can download the AdventureWorks database from the following page: AdventureWorks Installation and configuration.

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:

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:

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.

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

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:

• SQL Server Profiler
• Run SQL Server Profiler

From the SQL Server Management Studio, Go to Tools menu strip and click on SQL Server 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:

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:

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:

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

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:

• Is this the end of SQL Profiler?
• Integration Services Performance Best Practices – Data Flow Optimization
• Integration Services Performance Best Practices – Writing to the Destination

Table of contents

• Author
• Recent Posts

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

Latest posts by Hadi Fadlallah (see all)

• An overview of SQL Server monitoring tools - December 12, 2023
• Different methods for monitoring MongoDB databases - June 14, 2023
• Learn SQL: Insert multiple rows commands - March 6, 2023