Was this helpful?
SQL Server 2022 Mass Insert
The SQL Server 2022 Mass Insert connector provides a rapid way of inserting records into a SQL Server 2022 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 have large tables for better performance. This connector implements SQL Server Native Client 11.0 ODBC driver bulk copy (BCP) API.
Prerequisites
Before using the connector, make sure you perform the following:
Install SQL Server Native Client 11.0 ODBC driver.
Obtain the SQL Server 2022 Mass Insert Server URL, Source Database, User ID, and Password.
Connector Parts
Connector parts are the fields you configure to connect with a data source or target. The fields that are available depend on the connector you select.
This is a target only connector. For a list of all parts for target connectors, see Specifying Target Connector, Parts, and Properties.
Connector Properties
You can specify the following target properties:
Property
Description
Encoding
Type of character encoding to use with source and target files. The default value is OEM.
Shift-JIS encoding is used only in Japanese operating systems.
UCS-2 is no longer considered a valid encoding name, but you may use UCS2. In the data file, change UCS-2 to UCS2.
Note:  This property is not encoding of the database that you connect to, but it is the encoding in which the connector expects to receive SQL query statements that must be sent to the database.
ConstraintDDL
Additional SQL data definition language statements that must be executed after the target table is created. This is similar to the support provided for SQL pass-through in the SQL import connectors. Each line must be a valid ODBC DDL statement.
For example, you can have the following statements:
CREATE UNIQUE INDEX index1 ON mytable (Field1 ASC)
CREATE INDEX index2 ON mytable (Field2, Field3)
These statements create two indices on the table mytable. The first statement does not allow duplicates and the index values are stored in ascending order. The second index is a compound index on fields Field2 and Field3.
The ConstraintDDL is run only if the output mode is Replace for the target. If there are any errors, they are written to the error and event log file. An error during transformation displays the Transformation Error dialog. You can ignore the DDL errors and continue the transformation.
ConstraintDDL also supports an escaping mechanism that allows you to specify DDL in the native SQL of the DBMS. Any statement preceded by an @ sign is sent directly to DBMS.
The following is a DDL statement for creating a primary key for the table mytable:
@CREATE INDEX pk_mytable ON mytable (Field1, Field2) WITH PRIMARY
Some ODBC drivers do not support the SQL extensions required to create a primary key with the ODBC variant of the SQL CREATE statement. In these cases, to create primary keys, use native SQL.
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.
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.
IdentifyInsert
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.
Supported Data Types
The following data types are supported:
Exact numerics:
bigint
numeric
bit
smallint
decimal
smallmoney
int
tinyint
money
Note:  Id datatypes (bigint identity, decimal identity, int identity, numeric identity, smallint identity, tinyint identity) can be used as target field only if identifyInsert property is set to True.
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
xml
sysname
Note:  timestamp, uniqueidentifier, and sysname are read-only and system generated. Hence, cannot be used as target.
Additional Information
Id datatypes (bigint identity, decimal identity, int identity, numeric identity, smallint identity, tinyint identity) can be used as target field only if identifyInsert property is set to True.
Process Limitation: The SQL Server 2022 Mass Insert connector cannot be used in a multi-threaded process. The mass insert feature causes problems when more than one thread runs in parallel with a process. Use the single-thread process instead.
Note:  Your process can include multiple transformations running in parallel to different tables.
Last modified date: 12/03/2024