Bulk Insert Task
It provides an efficient way to copy large amounts of data into a SQL Server table or view. The company stores its million-row product list on a mainframe system. the e-commerce system uses SQL Server to populate Web pages. The update the SQL Server product table with the master product list from the mainframe. To update the table product list in a table-delimited format. the Bulk Insert task to copy the data into the SQL Server table. high-speed data copying, transformations performed on the data. while it is moving from the source file to the table or view.
bulk insert task in SSIS
The Bulk Insert task, consider the following
- The Bulk Insert task can transfer data only from a text file into a SQL Server table or view. To use the Bulk Insert task to transfer data from other database management systems. the data from the source to a text file and then import the data from the text file into a SQL Server table or view.
- The destination of a table or view in a SQL Server database. If the destination table or view already contains data. the new data appended to the existing data Bulk Insert task runs. The want to replace the data, run an Execute SQL task. that runs DELETE or TRUNCATE statement run the Bulk Insert task.
- The format file in the Bulk Insert task object. If have a format file created by the BCP utility. it is the path in the Bulk Insert task. The Bulk Insert task supports both XML and nonXML format files.
- Only members of the system admin fixed server role run package contain a Bulk Insert task.
Bulk Insert Task with Transactions
If a batch size is not set, the complete bulk copy operation treated as one transaction. A batch size of 0 indicates that the data inserted in one batch. If a batch size is set, each batch represents a transaction. that committed when the batch finishes running. The behavior of the Bulk Insert task. it relates to transactions, depends task joins the package transaction. If the Bulk Insert task does not join the package transaction. Each error-free batch committed as a unit before the next batch tried. If the Bulk Insert task joins the package transaction. The error-free batches remain in the transaction at the conclusion of the task. These batches are subject to the commit or rollback operation of the package. A failure in the Bulk Insert task does not roll backloaded batches. if the task succeeds, batches are not committed. Commit and rollback operations occur only in response to package and workflow settings.Source and Destination
The location of the text source file following ways - The server must have permission to access both the file and the destination database.
- The server runs the Bulk Insert task. Thus, any format file that the task uses located on the server.
- The source file Bulk Insert task loads on the same server as the SQL Server database. which inserted, or on a remote server. If the file is on a remote server, specify the file name using the Universal Naming Convention name in the path.
Performance Optimization
To optimize performance, consider the following - If the text file located on the same computer as the SQL Server database. which data inserted, the copy operation occurs at an even faster rate. the data is not moved over the network.
- The Bulk Insert task does not log error-causing rows. the error outputs of data flow components to capture error-causing rows.