Tuesday, January 19, 2016

How to use bulk insert task in SSIS (SQL Server 2012)

Bulk insert task is used to copy large amount of data into SQL Server table or view. Assume your company maintaining the information in mainframe systems and the information having millions of rows and but the company’s online shopping website using SQL server database for populating the data into front end application. You can’t copy huge data into sql server into tables from mainframe system and the list products saved in comma-delimited format for this bulk insert task is very use full to move the data very fast. Main purpose is high speed data copying, where as general transformations can’t do this operation very fast.
  • Bulk insert task copy’s data from only text files into sql table or view.
  • Destination must be sql server table or view, if the table already had data then the new data will  be appended to the existing data
  • If you don’t want old data use execute sql task and delete or truncate the data
  • Bulk insert task supports xml and non xml file formats
  • Only sysadmin role members can run the bulk insert task packages
Configuration of Bulk insert task:-
Go to SSIS Toolbox and select bulk insert task and drag into control flow.

Go to connection
Connection:-Select the destination database connection or create new connection to the database
Destination table: - Select the table where you want to insert the data.
Note: - Flat table structure must be present in the destination database.
Format: - Use file: - you can use format file if you have the file which is created by BCP utility tool
: -Specify: - Row Delimiter: - How the text file row data is separated.
Column Delimiter: - How the text file column names are separated.
Based on the test file delimiters select the column delimiters, row delimiters, below is comma separated delimiters

Source connection: - File -> Create new connection (or) use existing connection
Go to options and select batch size as 0 or number rows per batch, if you mention any number
For ex: - 50 per batch if it is 0 means insert all data in one batch
Go to options

Check constraints which are required. Configuration part is done. Now execute the task