SSIS Package 2012 Bulk Insert Task with Examples
Bulk Insert task in SSIS 2008 and SSIS 2012
- In this i will explain the importance of bulk insert task in ssis 2008 or ssis 2012 and i will also give some examples
- The Bulk Insert task can only transfer data from a text file into a SQL Server table.
- Bulk Insert task supports only Object Linking and Embedding, Database (OLE DB) connections for the destination database.
- While the Data Flow task can provide this capability, the Bulk Insert task is the most efficient method for this type of transfer, because it cannot perform transformations or error logging on the data while it is moving from the source file to the table.
- If the destination table or view already contains data, the new data is appended to the existing data when the Bulk Insert task runs.
- If you want to replace the data, run an Execute SQL task that runs a DELETE or TRUNCATE statement before you run the Bulk Insert task.
- The Bulk Insert task is particularly useful in situations where data needs to be retrieved from a system that cannot be connected to SSIS. Text files can act as a great intermediary data source and the Bulk Insert task will minimise the time required to import the data into a SQL Server table.
The Bulk Insert Task Editor contains
four property categories:
General. - This category includes a Name and Description property.
- This category includes a File property to define the source text file connection.
- The connection category also contains a Connection and Destination Table property to define the destination SQL Server database and table.
- The text file RowDelimiter and ColumnDelimiter property can be used to define the text file format or if the Format property is set to Use File, a FormatFile can be used to define custom mappings between the text file and SQL Server table.
- If you have a format file that was created by the bcp utility, you can specify its path in the Bulk Insert task.
- The Bulk Insert task supports both XML and non-XML format files.
- On the Options page, the Codepage and DataFileType property is used to define the type of text file.
- The BatchSize determine how many rows are inserted into the table as a batch.
- A setting of zero means all rows in the text file are inserted as one batch.
- If a batch size is set, each batch represents a transaction that is committed when the batch finishes running.
- The FirstRow and LastRow properties determine the starting and ending row of the data in the text file.
- You can also use the MaxErrors property to determine the number of errors that are allowed before the Bulk Insert task fails.
0 comments:
Post a Comment