|
MS
SQL Server Bulk Insert
The MS SQL Server Bulk Insert object is an output only data object that employs the MS SQL Server BULK INSERT SQL statement, which allows you to load a lot of data into SQL Server very quickly.
BULK INSERT is a specific operation (new in 7.0+) in SQL Server that is able to copy data from a data file directly into a table at very high speeds by moving the entire recordset into the table in one step, or in several steps, if you are using a predetermined batch size.
BlueSky Integration Studio automates this process for you by creating the data file that SQL Server will use during its BULK INSERT operation. It will also automatically create the BULK INSERT statement for you based on the parameters you specify in the objects properties.
Properties
Action |
Describes the action to take for output streams. Ignored for input streams. Valid options are:
| ||||||
Catalog |
Database catalog name. For instance: Northwind. | ||||||
ColumnLayout |
The column layout (or file structure) of the table. Click on the Ellipse (...) button to fill in the column layout, or select an existing column layout from the database or repository. Please note: for the Bulk Loader to work correctly, you must include every field from the database table you are importing into. | ||||||
CommandTimeout |
Specify the maximum number of seconds to wait for any command that is sent to the database. Once this time is exceeded you will receive a timeout error. | ||||||
ConnectString |
(read only) This property displays the ConnectString that will be used to connect to the data source. It is built automatically by deriving the information from the other properties. | ||||||
ConnectStringOtherParams |
Any custom parameters to be included in the ConnectString. (example: see OleDb) | ||||||
FTPWizard |
The MS SQL Bulk Insert object builds a data file that is suitable for use in the BULK INSERT operation through SQL Server. The MS SQL Server BULK INSERT operation requires that the data file be on the same machine where SQL Server is located so that it can reference the data file locally. If BlueSky Integration Studio is not on the same machine as SQL Server it becomes necessary then to FTP the data file that the MS SQL Bulk Insert object creates (LocalDataFile) to the SQL Server machine (BulkInsertDataFile) location prior to running the BULK INSERT operation. The FTP Wizard will allow you to set the properties necessary so that BlueSky Integration Studio knows how to FTP the LocalDataFile to the MS SQL Server machine (BulkInsertDataFile).
| ||||||
Server |
The server name where your database resides. For MS SQL Server you would typically enter the DNS name or TCP/IP address of the server where SQL Server resides. | ||||||
Signon |
The user name and password to use to connect to the database. The password is hidden in this property. Click the Ellipse (...) button on the property where you can enter both the user name and hidden password. | ||||||
Tablename |
Database table name to BULK INSERT into. | ||||||
Comments |
Comment area to give descriptive text about the data object and its purpose. | ||||||
Alias |
Alias identifier. Aliases are used on all design objects to simplify coding and to ensure each object has a unique identifier. | ||||||
Name |
Descriptive name for this object. You can enter any text to describe the object | ||||||
|
| ||||||
BULK INSERT PROPERTIES |
| ||||||
|
| ||||||
BATCH_SIZE |
Specifies the number of rows in a batch. Each batch is copied to the server as one transaction. SQL Server commits or rolls back, in the case of failure, the transaction for every batch. By default, all data in the specified data file is one batch. | ||||||
BulkInsertDataFile |
The path and file location that the BULK INSERT Statement will reference when the BULK INSERT statement is sent to SQL Server. This can be different that the LocalDataFile property when using the FTPWizard option. For instance; if BlueSky Integration Studio is on a different server than SQL Server, then it becomes necessary to FTP the data file to the SQL Server machine prior to running the BULK INSERT statement. SQL Server can only reference data files used during BULK INSERT on the server in which it is running. If BlueSky Integration Studio and SQL Server is running on the same machine, then BulkInsertDataFile and LocalDataFile will be the same.
| ||||||
CHECK_CONSTRAINTS |
Specifies that table constraints are checked during the bulk insert operation. | ||||||
CODEPAGE |
[ = ACP | OEM | RAW | 'code_page number' ]. Specifies the code page of the data in the data file. CODEPAGE is relevant only if the data contains char, varchar, or text columns with character values greater than 127 or less than 32. (example code page number: 1252) | ||||||
FIELDTERMINATOR |
Specifies the field terminator to be used. The default is \t (tab character). You may also type in a character such as comma ( , ) | ||||||
KEEPIDENTITY |
Specifies that IDENTITY values being bulk inserted will keep their value rather than having a new IDENTITY value assigned to them as part of the insert process. | ||||||
KEEPNULLS |
Specifies that empty columns should retain a null value during the bulk copy operation, rather than have any default values for the columns inserted. | ||||||
LocalDataFile |
This is the local data file that BlueSky Integration Studio will build during the ETL process. Once the ETL process is complete, it will then check to see if the user specified an FTP location (when you need to move the data file to the SQL Server; if SQL Server is on a different machine). Then the BULK INSERT statement is submitted to SQL Server. | ||||||
MAX_ERRORS |
Specifies the maximum number of errors that can occur before the bulk copy operation is canceled. Each row that cannot be imported by the bulk copy operation is ignored and counted as one error. If max_errors is not specified, the default is 10. | ||||||
ROWS_PER_BATCH |
Specifies how many rows to move in each batch. You can use this option when BATCHSIZE is not specified. | ||||||
ROWTERMINATOR |
The End-of-Row terminator used in the output data file.
| ||||||
TABLOCK |
Specifies that a table-level lock is acquired for the duration of the bulk copy operation. A table can be loaded concurrently by multiple clients if the table has no indexes and TABLOCK is specified. By default, locking behavior is determined by the table option table lock on bulk load. Holding a lock only for the duration of the bulk copy operation reduces lock contention on the table, significantly improving performance. |
© 2003 - 2007 Relational Solutions, Inc. - All rights reserved