User Guide > Map Connectors > Source and Target Map Connectors > SQL Server 7 Mass Insert (OLEDB)
Was this helpful?
SQL Server 7 Mass Insert (OLEDB)
The SQL Server 7 Mass Insert (OLEDB) connector provides a rapid way of inserting records into a SQL Server database. It bypasses the transactional layer of the SQL Server database and adds information directly to the storage tables. SQL Server 7 Mass Insert is a useful option if you are dealing with large tables and performance is of paramount importance.
Tip...  To connect to a data table in a SQL Server database, the SQL Server engine must be running in the background.
Connector-Specific Notes
• SQL Server 7 Mass Insert 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 multithreaded 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.
• The integration platform does not support milliseconds in DateValMask conversions. Although a mask is provided for them, the integration platform returns zeros in those places.
Connector Parts
Connector parts are the fields you configure to connect with a data source or target.
The settings that are available depend on the connector you select.
For a list of all parts for source connectors, see Specifying Connector, Parts, and Properties.
For a list of all parts for target connectors, see Specifying Connector, Parts, and Properties.
Property Options
You can set the following source (S) and target (T) properties.
Property
S/T
Description
CommitFrequency
T
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
T
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
T
Type of encoding used with your source and target files. Default is OEM.
IdentityInsert
T
By default, identity fields are not inserted into the database. To enable insertion of identity fields, change to true.
SystemTables
T
If set to true, 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.
Note:  This property is applicable only if the user is logged onto the database as the database administrator. Only the DBA has access to system tables.
Tablock
T
Either true (default) or false.
Views
T
If set to True, this property allows you to see the view names in the table list along with the table names. Default is True.
Note:  This property is available on all SQL connectors, but Mass Inserts can only write to tables, not views. Therefore, setting this property to True has no effect in this instance.
Note:  This property supports only Append and DeleteAndAppend output modes and does not support the Replace output mode.
Encoding Notes
Shift-JIS encoding is meaningful only in Japanese operating systems.
UCS-2 is no longer considered a valid encoding name, but you may use UCS2. In the XML file, change UCS-2 to UCS2.
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.
Length
These are field lengths in your data. If you need to change field lengths, reset them in the schema.
Caution!  A field length shorter than the default may truncate data.
Supported Data Types
The only data type available for SQL Server 7 Mass Insert is Text.
Last modified date: 02/01/2024