User Guide : Map Connectors : Source and Target Map Connectors : SQL Server 2016 Mass Insert
 
Share this page             
SQL Server 2016 Mass Insert
The SQL Server 2016 Mass Insert connector provides a rapid way of inserting records into a SQL Server 2016 database. It bypasses the transactional layer of the SQL Server database and adds information directly to the storage tables. This connector is a useful option if you are dealing with large tables and performance is of paramount importance.
Connector-Specific Notes
The SQL Server 2016 Mass Insert connector does not transform to updateable views, only to tables.
The Update Mode is not an available option for this connector, since it is a mass insert application.
Process Limitation: This connector cannot be used in a multi-threaded process. The mass insert feature causes problems when more than one thread runs parallel in a process. Use the single-thread process instead. Important: Your process can include multiple transformations running in parallel to different tables.
Property Options
The following properties are supported for SQL Server 2016 Mass Insert target connections:
Property
Use
CommitFrequency
Allows you to control how often data is committed to the database when the AutoCommit property is set to False.
The default value is zero that is, the data is committed at the end of the transformation, allowing rollback on error. This is the slowest setting. When performing large transformations, this is not practical as it may produce too many transaction log entries.
Specifying a nonzero value indicates that data is committed to the database after inserting or updating specified number of records.
ConstraintDDL
This is a pass-through mechanism that allows you to specify any additional data definition language (DDL) statements that need to be executed when a new table is created. DDL is used to define keys, indexes, constraints and integrity rules for SQL databases. There is no default.
Encoding
Type of encoding to use with source and target files. Default is OEM. For choices, see Source and Target Map Connectors.
Notes:
Shift-JIS encoding is meaningful only in Japanese operating systems.
The Encoding property is not the encoding of the database that you connect to, but rather the encoding in which the connector expects to receive SQL query statements to be sent to the database.
IdentityInsert
Determines if the integration platform automatically inserts Identity fields into your database. The default is false. If you want to allow Identity fields, you must change this property to true.
SystemTables
If set to true, this property allows you to see all tables created by the DBA in the database. The system table names appear in the table list. Default is false.
Data Types
SQL Server 2016 Mass Insert connector supports the following data types:
Exact numerics:
bigint
numeric
bit
smallint
decimal
smallmoney
int
tinyint
money
Note:  The bigint identity, decimal() identity, numeric() identity, smallint identity, and tinyint identity data types are Id numbers that are read-only and cannot be used as target.
Approximate numerics:
float
real
Date and time:
date
datetimeoffset
datetime2
smalldatetime
datetime
time
timestamp
Character strings:
char
varchar
text
Unicode character strings:
nchar
nvarchar
ntext
Binary strings:
binary
varbinary
image
Other data types:
uniqueidentifier
sql_variant
xml
sysname
Note:  timestamp, uniqueidentifier, and sysname are read-only and system generated. Hence, cannot be used as target.
Additional Information
Rowversion, timestamp, and sysname datatypes are automatically generated identifiers and hence cannot be used as target fields.
Note:  Timestamp data type is deprecated.
Id datatypes such as bigint identity, decimal identity, int identity, numeric identity, smallint identity, and tinyint identity are automatically generated numbers and cannot be used as target fields.