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
SQLAssist 