BulkInsertTask.KeepNulls Property
Definition
Important
Some information relates to prerelease product that may be substantially modified before it’s released. Microsoft makes no warranties, express or implied, with respect to the information provided here.
Gets or sets a Boolean that indicates whether empty columns should retain a null value during the bulk load operation, or insert default values into the columns.
public:
property bool KeepNulls { bool get(); void set(bool value); };public bool KeepNulls { get; set; }member this.KeepNulls : bool with get, setPublic Property KeepNulls As BooleanProperty Value
true if empty columns should retain a null value during the bulk load operation; false if the specified default values should be inserted instead.
Implements
Examples
The following example creates a BulkInsertTask, and then shows the default settings of the properties, including the KeepNulls, by using the TaskHost. The example then sets two fields to demonstrate how to set field values.
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Tasks.BulkInsertTask;
namespace Microsoft.SqlServer.SSIS.Samples
{
class Program
{
static void Main(string[] args)
{
Package pkg = new Package();
Executable exec1 = pkg.Executables.Add("STOCK:BulkInsertTask");
TaskHost th = exec1 as TaskHost;
// List the default values of the Bulk Insert task
// by using the Properties collection of the TaskHost.
Console.WriteLine("BatchSize {0}", th.Properties["BatchSize"].GetValue(th));
Console.WriteLine("CheckConstraints {0}", th.Properties["CheckConstraints"].GetValue(th));
Console.WriteLine("CodePage {0}", th.Properties["CodePage"].GetValue(th));
Console.WriteLine("DataFileType {0}", th.Properties["DataFileType"].GetValue(th));
Console.WriteLine("DebugMode {0}", th.Properties["DebugMode"].GetValue(th));
Console.WriteLine("DestinationConnection {0}", th.Properties["DestinationConnection"].GetValue(th));
Console.WriteLine("DestinationTableName {0}", th.Properties["DestinationTableName"].GetValue(th));
Console.WriteLine("FieldTerminator {0}", th.Properties["FieldTerminator"].GetValue(th));
Console.WriteLine("FireTriggers {0}", th.Properties["FireTriggers"].GetValue(th));
Console.WriteLine("FirstRow {0}", th.Properties["FirstRow"].GetValue(th));
Console.WriteLine("FormatFile {0}", th.Properties["FormatFile"].GetValue(th));
Console.WriteLine("KeepIdentity {0}", th.Properties["KeepIdentity"].GetValue(th));
Console.WriteLine("KeepNulls {0}", th.Properties["KeepNulls"].GetValue(th));
Console.WriteLine("LastRow {0}", th.Properties["LastRow"].GetValue(th));
Console.WriteLine("MaximumErrors {0}", th.Properties["MaximumErrors"].GetValue(th));
Console.WriteLine("RowTerminator {0}", th.Properties["RowTerminator"].GetValue(th));
Console.WriteLine("SortedData {0}", th.Properties["SortedData"].GetValue(th));
Console.WriteLine("SourceConnection {0}", th.Properties["SourceConnection"].GetValue(th));
Console.WriteLine("SqlStatement {0}", th.Properties["SqlStatement"].GetValue(th));
Console.WriteLine("SuspendRequired {0}", th.Properties["SuspendRequired"].GetValue(th));
Console.WriteLine("TableLock {0}", th.Properties["TableLock"].GetValue(th));
Console.WriteLine("UseFormatFile {0}", th.Properties["UseFormatFile"].GetValue(th));
Console.WriteLine("--------------------------");
// Show how to set a property by using the TaskHost Properties.
th.Properties["CheckConstraints"].SetValue(th, true);
th.Properties["DataFileType"].SetValue(th, DTSBulkInsert_DataFileType.DTSBulkInsert_DataFileType_Native);
Console.WriteLine("New value of CheckConstraints and DataFileType: {0}, {1}", th.Properties["CheckConstraints"].GetValue(th), th.Properties["DataFileType"].GetValue(th));
}
}
}
Imports System
Imports System.Collections.Generic
Imports System.Text
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Tasks.BulkInsertTask
Namespace Microsoft.SqlServer.SSIS.Samples
Class Program
Shared Sub Main(ByVal args() As String)
Dim pkg As Package = New Package()
Dim exec1 As Executable = pkg.Executables.Add("STOCK:BulkInsertTask")
Dim th As TaskHost = exec1 as TaskHost
' List the default values of the Bulk Insert task
' by using the Properties collection of the TaskHost.
Console.WriteLine("BatchSize {0}", th.Properties("BatchSize").GetValue(th))
Console.WriteLine("CheckConstraints {0}", th.Properties("CheckConstraints").GetValue(th))
Console.WriteLine("CodePage {0}", th.Properties("CodePage").GetValue(th))
Console.WriteLine("DataFileType {0}", th.Properties("DataFileType").GetValue(th))
Console.WriteLine("DebugMode {0}", th.Properties("DebugMode").GetValue(th))
Console.WriteLine("DestinationConnection {0}", th.Properties("DestinationConnection").GetValue(th))
Console.WriteLine("DestinationTableName {0}", th.Properties("DestinationTableName").GetValue(th))
Console.WriteLine("FieldTerminator {0}", th.Properties("FieldTerminator").GetValue(th))
Console.WriteLine("FireTriggers {0}", th.Properties("FireTriggers").GetValue(th))
Console.WriteLine("FirstRow {0}", th.Properties("FirstRow").GetValue(th))
Console.WriteLine("FormatFile {0}", th.Properties("FormatFile").GetValue(th))
Console.WriteLine("KeepIdentity {0}", th.Properties("KeepIdentity").GetValue(th))
Console.WriteLine("KeepNulls {0}", th.Properties("KeepNulls").GetValue(th))
Console.WriteLine("LastRow {0}", th.Properties("LastRow").GetValue(th))
Console.WriteLine("MaximumErrors {0}", th.Properties("MaximumErrors").GetValue(th))
Console.WriteLine("RowTerminator {0}", th.Properties("RowTerminator").GetValue(th))
Console.WriteLine("SortedData {0}", th.Properties("SortedData").GetValue(th))
Console.WriteLine("SourceConnection {0}", th.Properties("SourceConnection").GetValue(th))
Console.WriteLine("SqlStatement {0}", th.Properties("SqlStatement").GetValue(th))
Console.WriteLine("SuspendRequired {0}", th.Properties("SuspendRequired").GetValue(th))
Console.WriteLine("TableLock {0}", th.Properties("TableLock").GetValue(th))
Console.WriteLine("UseFormatFile {0}", th.Properties("UseFormatFile").GetValue(th))
Console.WriteLine("--------------------------")
' Show how to set a property by using the TaskHost Properties.
th.Properties("CheckConstraints").SetValue(th, True)
th.Properties("DataFileType").SetValue(th, DTSBulkInsert_DataFileType.DTSBulkInsert_DataFileType_Native)
Console.WriteLine("New value of CheckConstraints and DataFileType: {0}, {1}", th.Properties("CheckConstraints").GetValue(th), th.Properties("DataFileType").GetValue(th))
End Sub
End Class
End Namespace
Sample Output:
BatchSize 0
CheckConstraints False
CodePage RAW
DataFileType 0
DebugMode False
DestinationConnection
DestinationTableName
FieldTerminator Tab
FireTriggers False
FirstRow 1
FormatFile
KeepIdentity False
KeepNulls False
LastRow 0
MaximumErrors 0
RowTerminator {CR}{LF}
SortedData
SourceConnection
SqlStatement System.String[]
SuspendRequired False
TableLock False
UseFormatFile False
--------------------------
New value of CheckConstraints and DataFileType: True, 1
Remarks
For more information, see Keep Nulls or Use Default Values During Bulk Import (SQL Server).