Hadi Fadlallah
SSIS OLE DB Destination SQL command Query builder

Data Access Modes in SSIS OLE DB Destination: SQL Command vs. Table or View

October 5, 2021 by

This article compares the SSIS OLE DB Destination SQL command with the “Table or View” data access mode. In a previously published article, SSIS OLE DB Destination vs SQL Server Destination, in this series, we explained the OLE DB Destination component in SSIS and illustrated how it differs from the SQL Server Destination component. So, in this article, we will focus on the OLE DB Destination data access modes, not the component itself.

Introduction

As mentioned in the OLE DB Destination official documentation, The OLE DB destination provides five different data access modes for loading data:

  1. Table or view: select an existing table or view
  2. Table or View – fast load: select an existing table or view – using fast load options
  3. Table name or view name variable: select a variable that contains a table or view name
  4. Table name or view name variable – fast load: select a variable that contains a table or view name – using fast load options
  5. SQL Command: The results of an SQL statement

We can note that the first four options are used to select an existing table or view from a database. The difference is whether the name is stored in a variable or passed manually and whether the data insertion is done using the fast load option. Besides, the SSIS OLE DB Destination SQL Command data access mode is different from the other options.

This article will explain the use cases where users should use the SSIS OLE DB Destination SQL Command data access mode, how it differs from the other modes, and its limitations.

This article is the fifteenth of the SSIS features face-to-face series that aims to compare similar SSIS components in order to remove any confusion for the data integration developers that use this technology.

  • Note: All examples in this article are made using the Stack overflow 2013 database, SQL Server 2019, Visual Studio 2019, SQL Server Integration Services projects extension version 3.4.

SSIS OLE DB Destination Table or View Data Access Modes

First, let us start by illustrating the four “Table and View” data access modes briefly.

Fast load

In brief, fast load mode means using BULK INSERT operation when inserting data into the OLE DB Destination. From a performance perspective, the fast load is highly recommended since data is inserted in batches rather than row-by-row insertion. But in case you need to perform a row-by-row insertion to improve error handling and logging, you don’t have to use this option.

- OLE DB Destination fast load modes

Figure 1 – OLE DB Destination fast load modes

For additional information, you can refer to the SSIS OLE DB Destination Vs. SQL Server Destination article we previously published in this series, where we explained the “Fast Load” mode and all related configurations.

SSIS OLE DB Destination fast load options

Figure 2 – SSIS OLE DB Destination fast load options

Selecting table manually vs. using variables

There are two ways to pass the destination table name to the SSIS OLE DB Destination:

  1. Selecting the name manually from a drop-down list
  2. Selecting the name from a variable

Selecting the table name from a drop-down list is the mode used once we are importing data to a fixed table name, which is mostly done in the periodic data warehousing process since dimension and fact tables name are well known and don’t change frequently.

When using this option, the user can create a new table in the Integration Services design studio (Visual Studio) by clicking on the “New” button that appears in the OLE DB Destination editor.

Creating a new destination table button

Figure 3 – Creating a new destination table button

Once the user clicks on the “New” button, a small SQL Command window appears containing the CREATE TABLE command generated based on the OLE DB Destination input data flow metadata. The user can edit this command.

CREATE TABLE SQL Command

Figure 4 – CREATE TABLE SQL Command

After clicking on the “OK” button, the created table name appears in the table name drop-down list.

View as a destination?

In SQL, a View is considered as a virtual table. It is a result set of a fixed query stored in the SQL database engine. The main idea is that users can query this result set as a single object. Based on that, how can data be inserted into a virtual table? Inserting data into a view will modify the data stored in the underlying tables of a view.

Based on the SQL Server View official documentation, not all views are updatable. There are many conditions to allow developers to modify the data stored in the underlying tables of a view.

  • Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table
  • The columns being modified in the view must directly reference the underlying data in the table columns. The columns cannot be derived in any other way, such as aggregation (AVG, COUNT, SUM…) and computation (expressions, UNION, CROSS JOIN…)
  • The columns being modified are not affected by GROUP BY, HAVING, or DISTINCT clauses
  • TOP is not used anywhere in the select_statement of the view together with the WITH CHECK OPTION clause

SSIS OLE DB Destination SQL Command Data Access Mode

SSIS OLE DB Destination SQL Command is very similar to using views as a destination. They both require the same conditions we already listed before. An SQL Command must be mainly used when the destination table contains too many columns, and you need to select only some specific one.

As shown in the image below, when using the SSIS OLE DB Destination SQL Command data access mode, the destination editor has five properties/features:

SSIS OLE DB Destination SQL Command configuration

Figure 5 – SSIS OLE DB Destination SQL Command configuration

  1. SQL Command: The text box where we should write the SQL query we want to use as a destination
  2. Build Query button: Opens a query builder that facilitates building SQL queries

    SSIS OLE DB Destination SQL command Query builder

    Figure 6 – Query builder

  3. Browse: This button allows to read a SQL query stored in a separate file
  4. Parse Query: After writing a SQL query, we can use this option to validate the SQL query by checking for any syntax error
  5. View Existing: This button previews the SQL query result (existing data)

    Previewing the existing data of a SSIS OLE DB Destination SQL Command

    Figure 7 – Existing data

As mentioned before, SSIS OLE DB Destination SQL Commands do not allow joins or any complex computation nor aggregation. To prove, we ran the following example:

We created a simple package with one data flow task. In this data flow task, we added an OLE DB Source and an OLE DB Destination. We used the following query as an SSIS OLE DB Destination SQL Command:

After executing the package, it throws the following exception:

[OLE DB Destination [2]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: 0x80004005 Description: “Cannot insert or update columns from multiple tables”.

Exception when using joins in the SSIS OLE DB Destination SQL command

Figure 8 – Exception when using joins in the SSIS OLE DB Destination SQL command

The main limitation of using an SSIS OLE DB Destination SQL command is that it uses a row-by-row insertion which has slow performance. Besides, its main advantage is decreasing the number of columns mapping needed while configuring the destination component.

Summary

This article explained the different data access modes available in the OLE DB Destination component in SSIS. We previously illustrated the fast load options and the conditions needed to use a view as a destination. Finally, we illustrated the SSIS OLE DB Destination SQL command data access mode, how it differs from using tables or views, and its limitations.

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 transformation vs. Unpivot transformation
SSIS Merge Join vs. Merge Transformation
Data Access Modes in SSIS OLE DB Destination: SQL Command vs. Table or View

Hadi Fadlallah
ETL, Integration Services (SSIS)

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 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

188 Views