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:
- Table or view: select an existing table or view
- Table or View – fast load: select an existing table or view – using fast load options
- Table name or view name variable: select a variable that contains a table or view name
- Table name or view name variable – fast load: select a variable that contains a table or view name – using fast load options
- 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.
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.
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:
- Selecting the name manually from a drop-down list
- 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.
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.
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:
Figure 5 – SSIS OLE DB Destination SQL Command configuration
- SQL Command: The text box where we should write the SQL query we want to use as a destination
Build Query button: Opens a query builder that facilitates building SQL queries
Figure 6 – Query builder
- Browse: This button allows to read a SQL query stored in a separate file
- Parse Query: After writing a SQL query, we can use this option to validate the SQL query by checking for any syntax error
View Existing: This button previews the SQL query result (existing data)
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:
|
1 2 |
SELECT Users.AboutMe, Users.Age, Comments.Score, Comments.CreationDate FROM Users INNER JOIN Comments ON Users.ID = Comments.UserID |
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”.
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
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
- 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