SSIS Bulk Insert

Bulk Insert is mainly used for copy large of data into a SQL Server Table. Its high speed data copy and transformation cannot be performed while transferring the data from source to destination.

Attention 

• Destination must be a table or view.
• Batch size is not set (Zero) Bulk copy treated as one transaction.
• If the source is located in the same server and the copy operations occur even fast (no network traverse)
• If the destination table already contains data the new data is appended to the existing data when the Bulk Insert task runs.
• Use Execute SQL Task to delete or truncate the existing data then run the bulk insert task.

How to use Bulk Insert 

Step 1: Drag the Bulk Insert Task from SSIS Toolbox into Control flow Environment.

Step 2: Double click the Bulk Insert Task

Step 3: In bulk insert task contains four tabs which is General, Connection, Options and Expressions.

Step 4: Add the Name and Description in General Tab and Go to Connection Tab.

Step 5: Add the connection from Connections Managers

Step 6: Click the new connection in Connection column

Step 7: Choose the existing connection if available or create the new one.

Step 8: Choose the destination table

Step 9: Choose the Source File and click new connection in source column

Step 10: Click the new connection and choose the existing file

Step 11: Go to the options tab

Step 12: Choose the Options property

Step 13: click the Keep nulls, enable identity insert and table lock

Step 14: Run the package or Task and check the destination table.

I hope this post will help you and Please let me know in case of questions. Thanks