Back to Index

 

  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:

Option

Description

ClearThenInsert

Clears the table via TRUNCATE TABLE, then inserts records

InsertOnly

Inserts records into table only.

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.  

Option

Description

DOS

Carriage return / Line feed

UNIX

Line Feed

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