User Guide : Map Connectors : Source and Target Map Connectors : SQL Server 2000 Mass Insert
 
Share this page                  
SQL Server 2000 Mass Insert
The SQL Server 2000 Mass Insert connector provides a rapid way of inserting records into a SQL Server 2000 database. It bypasses the transactional layer of the SQL Server database and adds information directly to the storage tables. SQL Server 2000 Mass Insert is a useful option if you are dealing with large tables and performance is of paramount importance.
To connect to SQL Server 2000 with NT authentication, set your server to allow NT authentication as the default method. Then when following the procedure below, leave the User ID and Password options blank.
Note:  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 2000 Mass Insert does not transform to updatable 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.
Note:  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.
Property Options
You can set the following source (S) and target (T) properties.
Property
S/T
Description
CommitFrequency
T
Controls how often data is committed to the database. By default, its value is zero, which means that data is committed at the end of transformations. For large transformations, this is not practical as it may fill up the transaction logs. Setting the CommitFrequency to some nonzero value instructs the connector to do a database commit after the specified number of records have been inserted or updated in the table. This keeps the transaction log from getting too full but limits the restartability of the transformation.
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 to use with source and target files. Default is OEM. See Encoding Notes.
IdentityInsert
T
The integration platform does not automatically insert identity fields into the database. The default is false. To allow identity fields, you must change this property to true.
SystemTables
T
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.
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 (default), allows you to see views. The view names appear in the table list along with the tables. To disallow views, set to false.
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.
Data Types
ID field types are not supported.