SSIS OLE DB Source Editor Data Access Mode: "SQL command" vs "Table or view"

Question

In BIDS, I'm running a Data Flow Task & I'm wondering what the difference is behind the scenes between using "Table or view" vs "SQL Command" for the Data Access Mode for an OLE DB Source. For example, suppose I have a table called mytable with 5 columns (column1, column2, column3, column4, column5) but I'm only interested in column1. I see these 2 options:

1. I can either use "Table or view" and select "mytable" and then only check "column 1" from the "Columns" pane.
2. Or I can use "SQL Command" and use this query "select column1 from mytable".

When I ran a test, the second option seemed to run much faster but I'd like confirmation of what's going on behind-the-scenes when I use "Table or view" and then only select certain columns. I'd appreciate any insight anyone can provide.

(In my particular use case, mytable is an offsite Azure Database table and column2 has large amounts of xml within it. My guess is that all of the columns were coming over the wire when I used "Table or view")

Answer 1

I will make a small experiment. i will use SQL profiler to see what is going in the background while using an OLEDB Source in the two cases:

I have a Table named dbo.Table_1 that contains 3 columns (ID,name,department)

I used SQL profiler to Tune the database containing this Table and i used the 2 access mode, below the results:

Table or View - selecting only ID column

The profiler shows that the following command is executed

SELECT * FROM [dbo].[Table_1]

Even if you only select one column, the OLEDB Source reads all data then filters columns after reading them all.

enter image description here

SQL COMMAND

The profiler shows that the following command is executed

SELECT [ID] FROM [dbo].[Table_1]

enter image description here

---

Recently I published an article that contains more details, you can check it on the following link:

• SQLShack - SSIS OLE DB Source: SQL Command vs Table or View