BulkInsertTask.KeepNulls Property

Gets or sets a Boolean that indicates whether empty columns should retain a nulla null reference (Nothing in Visual Basic) value during the bulk load operation, or insert default values into the columns.

Namespace:  Microsoft.SqlServer.Dts.Tasks.BulkInsertTask
Assembly:  Microsoft.SqlServer.BulkInsertTask (in Microsoft.SqlServer.BulkInsertTask.dll)

Syntax

'Declaration
Public Property KeepNulls As Boolean
    Get
    Set
'Usage
Dim instance As BulkInsertTask
Dim value As Boolean

value = instance.KeepNulls

instance.KeepNulls = value

public bool KeepNulls { get; set; }

public:
virtual property bool KeepNulls {
    bool get () sealed;
    void set (bool value) sealed;
}

abstract KeepNulls : bool with get, set
override KeepNulls : bool with get, set

final function get KeepNulls () : boolean
final function set KeepNulls (value : boolean)

Property Value

Type: System.Boolean
true if empty columns should retain a nulla null reference (Nothing in Visual Basic) value during the bulk load operation; false if the specified default values should be inserted instead.

Implements

IDTSBulkInsertTask.KeepNulls

Remarks

For more information, see Keeping Nulls or Using Default Values During Bulk Import.

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

See Also

Reference

BulkInsertTask Class

Microsoft.SqlServer.Dts.Tasks.BulkInsertTask Namespace