未加星标

#0389 SQL Server SSIS OLE DB Destination Table Fast Load Keep NUL ...

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二05 | 时间 2016 | 作者 红领巾 ] 0人收藏点击收藏

SQL Server IntegrationServices (SSIS) are typicallycalled upon when integrating systems exchange data from one source toa given destination.The reason I use the term “source” and “destination” instead of a “database”becauseeither of the two can be something other than a database (a flat file, some web-service, a script task, etc).This is possible becauseSSIS is more like any other .net framework based programming language (C# or VB.net).

OLE DBdestination

Because one would commonly have either a Microsoft Access or a Microsoft SQL Server on at least one side of the integration, the most common source & destinations used in a SSIS-based data solutionare the OLE DB Source and the OLE DB Destination.The OLE DB destination allows you to load data to a table, a view or even a SQL command (e.g. the results of a statement execution).

In order to load data as quickly into thedestination as possible, the OLE DB destination allows us to use a“Fast Load” mode. The“Fast Load” option allows the datateam to configure various options that affect thespeed of the data load:

Keep Identity Keep NULLs Table Lock Check Constraints Rows per Batch MaximumInsert Commit Size

We will look at each option in detail over the next couple of weeks.

Keep NULLs option

The Keep NULLsoption is normally something that mostaccidental SSIS developers donot pay much attention to. It comes unchecked by default and it left unchecked. However, the state of this checkbox can have a significant impact on the completeness and quality of data being inserted into thedestination database.

Toclarify, allow me to explainthefunctionality of this checkbox:

Checked If a column in the source data has NULL values, keep them as-is Unchecked If a column in the source data has NULL values, try to replace them with the default values as definedby the destination DB

The stateof this checkbox typically does notmake much of a difference because in most cases,the domain and business rules in both the systems involved would be similar. Thus, the if a column in one system allows a NULL value, other systems in the same domain would also allow a NULL (e.g. in most enrollment forms, the last name would generally be mandatory but the first name is not). However,legacy systems (whichhave been around since decades) would have accumulated a lot data that does notconform to newer domain practices, causing issues during migration. This is when the “Keep Nulls” checkbox comes into action.

In the case I am going to present today, I have a set of Product Names and their corresponding Manufacturers. In a few of these cases, Idon’t know the manufacturer and have therefore kept it blank.

USE tempdb; GO --Test Data SELECT [ProductList].[ProductName], [ProductList].[ManufacturerName] FROM (VALUES ('windows' , 'Microsoft'), ('SQL Server' , NULL ), ('VisualStudio','Microsoft'), ('mysql' , 'Oracle' ), ('PeopleSoft' , 'Oracle' ) ) AS [ProductList] ([ProductName], [ManufacturerName]); GO
#0389   SQL Server   SSIS   OLE DB Destination   Table Fast Load   Keep NUL ...

Sample data with some NULL values

For the sake of this demo, I have used this query as my source in the test SSIS package. Below is a screenshot of my data flow task.


#0389   SQL Server   SSIS   OLE DB Destination   Table Fast Load   Keep NUL ...

Using a test data query in the OLE DB source command

I directlytake this dataset as input to the OLE DB destination.The OLE DBdestinationis configured to a test table ( [dbo].[KeepNullsInOLEDB] ) with the followingtable definition. USE [tempdb]; GO --Safety Check IF OBJECT_ID('dbo.KeepNullsInOLEDB','U') IS NOT NULL BEGIN DROP TABLE dbo.KeepNullsInOLEDB; END GO --Create table CREATE TABLE dbo.KeepNullsInOLEDB ([ProductName] VARCHAR(255) NULL, [ManufacturerName] VARCHAR(255) NULL CONSTRAINT df_KeepNullsInOLEDB_ManufacturerName DEFAULT ('Microsoft') ); GO
#0389   SQL Server   SSIS   OLE DB Destination   Table Fast Load   Keep NUL ...

OLE DB Destination Configuration. Notice the “Keep nulls” switch is unchecked.

Afterexecuting the package, I query the [dbo].[KeepNullsInOLEDB] table in the destination database, andcompare with the source data.
#0389   SQL Server   SSIS   OLE DB Destination   Table Fast Load   Keep NUL ...

Values inserted into the destination table. Notice the default value from table definition is used.

As can be seen from the screenshot, the [ ManufacturerName ] for “SQL Server” is not NULL. It is instead set to “Microsoft” which is the defaultvalue as set in the default constraint on the destination table.

The data inserted in the destination table changes if the switch is kept checked in the OLE DB destination.


#0389   SQL Server   SSIS   OLE DB Destination   Table Fast Load   Keep NUL ...

Notice how the value from the default constraint is not used when “Keep Nulls” is checked.

If the “Keepnulls”checkbox is checked, thedefault constraint on the target table is notused thereby maintainingthe same data as the source.

Summary

Depending upon the business requirements, it may be critical to migrate data from a source to a destination “as-is”, without the application ofdefault constraints. In such situations, the “Keep nulls” switch on the OLE DB destination (“Fast Load” mode) needs to be checked.

If the “Keep nulls” switch is unchecked, the default constraints from the target table definition come into effect.

In my future posts,I will take a look at the other switches on the OLE DB Fast Load mode.

Until we meet next time,

Be courteous. Drive responsibly.

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

主题: SQLSQL ServerMySQLWindowsC#AU
分页:12
转载请注明
本文标题:#0389 SQL Server SSIS OLE DB Destination Table Fast Load Keep NUL ...
本站链接:http://www.codesec.net/view/481211.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(31)