Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Go Programs
    • Python Programs
    • Java Programs

Bulk Insert Task in SSIS

by suresh

The Bulk Insert task in SSIS can transfer data only from a text file into a SQL Server table or view, which is similar to Bulk Insert in SQL Server. If the destination table or view already contains data, the new data is appended to the existing data when the SSIS 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.

For instance, We are working with stock market data, and every day we are getting billions of data in .csv format (Comma Separated Values). Our task is to copy data inside this .csv file to SQL database table every day. We usually have two approaches to do the same in SSIS

  • Drag and drop the data flow task and inside the data flow drag and drop flat file source and OLE DB destinations and copy the data. This approach is useful if we want to perform any SSIS transformations.
  • Use the SSIS Bulk Insert Task. This approach is more powerful compared to the previous one because internally, Bulk Insert Task uses Bulk Copy (BCP) operation (Which is very fast in SQL Server).

Available Options inside the Bulk Insert task in SSIS

Click on the Options tab inside the Bulk Insert Task will give the following options

  • CodePage: Specify the code page of the data in the data file. Generally used for other languages.
  • DataFileType: Specify the data-type value to use in the load operation.
  • BatchSize: Specify the number of rows in a batch. The default is the entire data file. If you set BatchSize to zero, the data loaded in a single batch. For instance, If we set the batch size as 100, then each batch acts as one transaction, and if the task fails after some time, then successfully loaded batches will not be rollback.
  • LastRow: Specify the last row to copy.
  • FirstRow: Specify the first row from which copying starts.
  • SortedData: Specify the ORDER BY clause in the bulk insert statement. The default is false.
  • MaxErrors: Specify the maximum number of errors that can occur before the Bulk insert operation canceled. A value of 0 indicates that an infinite number of errors are allowed.
Bulk Insert Task in SSIS 0

Options

TermDefinition

Options

TermDefinition
Check constraints Checks the column data.
Keep nulls Select to retain null values during the bulk insert operation, instead of inserting any default values for empty columns.
Enable identity insert Select to insert existing values into an identity column.
Table lock Select to lock the table during the bulk insert.
Fire triggers Select to fire any insert, update, or delete triggers on the table.

Bulk Insert Task in SSIS Example

In this SSIS bulk insert task example, we are going to copy the text file present in the local drive to the SQL Server destination.

We have a flat-file called GEOGRAPHY.txt contains 1000000 Rows. Our task is to insert all the rows using the Bulk Insert Task in SSIS.

Bulk Insert Task in SSIS 1

Drag and Drop the SSIS Bulk Insert Task from the toolbox to Control flow region

Bulk Insert Task in SSIS 2

Double click on Bulk Insert Task to configure the source and destination connection strings. In the General tab, we can change the Name and description

Bulk Insert Task in SSIS 3

Now click on the connection tab to set the source and destination connections. Firstly, we have to configure the source connection so select the file option

NOTE: We must provide the credentials to access the text file present in the local or remote.

Bulk Insert Task in SSIS 4

By clicking on New Connection, we can select the source text file we want to use. Click on the Browse button to select the text file. Here we are choosing the GEOGRAPHY text file of size 92, 562 KB.

Bulk Insert Task in SSIS 5

Click ok to finish SSIS Bulk Insert Task source configurations

The Row Delimiter property option will be {CR}{LF} (a carriage return). And the Column Delimiter property will set to Comma{‘} as in our flat file, columns separated by a comma.

Bulk Insert Task in SSIS 6

Now let us configure the destination connection by click on <New Connection>. Connection means selecting the SQL server database name. Here we are choosing SSIS Tutorials Database as the target database.

Now, configure the SSIS Bulk Insert Task destination table by selecting it from the list. Here We are selecting GEOGRAPHY table as the destination table

TIP: The destination table should be pre-designed in the SQL server.

Bulk Insert Task in SSIS 7

NOTE: The Bulk Insert Task in SSIS does not log error-causing rows. If you want bad records to write to an error file or table, it’s better to use the Data Flow Task.

Click on the “Options”

Bulk Insert Task in SSIS 8

We already explained the properties of each option in the starting only. For the time being, We are leaving default settings, but for the First Row, we changed the value to 2. Because you can observe the data that, We have column names in the first row.

Click ok to finish configuring Bulk Insert Task in SSIS and run the package

Bulk Insert Task in SSIS 9

Let’s open the SQL Server Management Studio and write the following T-SQL command and check the result

SELECT [CountryRegionCode] AS [Country Code]
      ,[EnglishCountryRegionName] AS [Country]
      ,[StateProvinceCode] AS [State Code]
      ,[StateProvinceName] AS [State]
      ,[City]
      ,[PostalCode]
      ,[UnitPrice]
      ,[ExtendedAmount] AS [ExtAmount]
      ,[ProductStandardCost] AS [StandardCost]
      ,[TotalProductCost] AS [ProductCost]
      ,[SalesAmount] AS [Sales]
      ,[TaxAmt]
  FROM [SSIS Tutorials].[dbo].[DimGeography]

OUTPUT

Bulk Insert Task in SSIS 10

Points to Remember in SSIS bulk insert task

  • The Bulk Insert task in SSIS can transfer data only from a text file into a SQL Server Table or SQL Server View.
  • Bulk Insert Task supports the Flat file Connection manager to select the text file.
  • The Bulk Insert Task in SSIS only supports OLE DB Connection Manager in SSIS for the destination database.
  • Destination table must exist before it is using in the Bulk Insert Task
  • Don’t forget to change the First Row option to 2, if you have your column names in the first row of a text file.
  • It is always good practice to set the batch size to insert a large amount of data.

Placed Under: SSIS

  • What is SSIS
  • Install SQL Server Data Tools
  • Learn SSIS in 28 Days
  • Create a SSIS Project
  • Create SSIS Package
  • SSIS Connection Manager
  • OLE DB Connection Manager
  • SSIS ADO Connection Manager
  • ADO.NET Connection Manager
  • SSIS Cache Connection Manager
  • SSIS Excel Connection Manager
  • SSIS File Connection Manager
  • SSIS FTP Connection Manager
  • SSIS SMO Connection Manager
  • Source Assistance in SSIS
  • Flat File Source in SSIS
  • OLE DB Source in SSIS
  • Excel Source in SSIS
  • ADO.NET Source in SSIS
  • SSIS FLAT FILE Destination
  • SSIS OLE DB Destination
  • SSIS ADO.NET Destination
  • SSIS Transformations
  • SSIS Audit Transformation
  • SSIS Aggregate Transformation
  • SSIS Aggregate Advanced Mode
  • SSIS Cache Transformation
  • SSIS Character Map
  • SSIS Conditional Split
  • SSIS Copy Column
  • SSIS Data Conversion
  • SSIS Derived Column
  • SSIS Export Column
  • SSIS Fuzzy Grouping
  • SSIS Fuzzy Lookup
  • SSIS Import Column
  • SSIS Lookup Introduction
  • Lookup – OLE DB Connection
  • SSIS Lookup in Full Cache Mode
  • SSIS Lookup – Case Sensitivity
  • SSIS Merge Transformation
  • SSIS Merge Join Transformation
  • SSIS Merge Join – Left Outer Join
  • SSIS Merge – Right Outer Join
  • SSIS Merge Join – Full Outer Join
  • SSIS Multicast Transformation
  • SSIS OLEDB Command
  • OLEDB Command – Delete Data
  • OLEDB Command-Update Data
  • SSIS Percentage Sampling
  • SSIS Pivot Transformation 2008
  • SSIS Pivot Transformation
  • SSIS Row Count Transformation
  • SSIS Row Sampling
  • Script Component as Source
  • Script Component as Destination
  • SSIS Script as Transformation
  • SSIS Sort Transformation
  • SSIS SCD Type 0
  • SSIS SCD Type 1
  • SSIS SCD Type 2
  • SSIS Term Lookup
  • SSIS Term Extraction Intro
  • Term Extraction – Extract Nouns
  • SSIS Extract Noun Phrases
  • Extract Nouns & Noun Phrases
  • Term Extraction – Exclusion Tab
  • SSIS Unpivot Transformation
  • SSIS Union All Transformation
  • SSIS For Loop Container
  • SSIS ForEach File Enumerator
  • SSIS ForEach SMO Enumerator
  • SSIS ForEach Variable
  • SSIS Foreach NodeList
  • Foreach ADO.NET Schema Rowset Enumerator
  • SSIS Bulk Insert Task
  • SSIS Data Profiling Task
  • Execute T-SQL Statement Task
  • SSIS Execute SQL Task Intro
  • SSIS Execute SQL Task Example
  • Execute SQL Task- Single Rowset
  • Execute SQL Task – Full Row Set
  • SSIS Execute Package Task
  • Execute Packages in SQL Server
  • Execute Packages in File System
  • SSIS Execute Package Project Reference
  • SSIS File System Task
  • SSIS File System Task- Copy Files
  • File System Task-Copy Directory
  • File System Task – Delete Files
  • File System Task – Delete Folder
  • File System Task -Move Directory
  • SSIS File System Task -Move File
  • File System – Move Multiple files
  • File System Task – Rename File
  • File System Task – Set Attributes
  • SSIS FTP TASK
  • SSIS Create Local Directory
  • SSIS Create Remote Directory
  • SSIS FTP Task Send Files
  • SSIS FTP – Send Multiple Files
  • SSIS FTP Task Delete Local Files
  • FTP TASK Delete Local Directory
  • FTP Task Delete Remote files
  • SSIS Delete Remote Directory
  • SSIS FTP Task Receive Files
  • SSIS FTP Receive Multiple Files
  • SSIS Script Task
  • Transfer SQL Server Objects Task
  • Transfer SQL Table Structures
  • Transfer SQL Tables with Data
  • Transfer SQL Stored Procedures
  • Transfer User Defined Functions
  • Transfer SQL Views in SSIS
  • SSIS Web Service Task
  • SSIS XML Task-Validate XML File
  • Transform XML File using XSLT
  • XML Task-XML files Differences
  • Create SSIS Catalog
  • Package Deployment using BIDS
  • Deploy Package Using SQL
  • Deploy using SQL Server Wizard
  • SSIS Breakpoints
  • SSIS Checkpoints
  • SSIS Error Handling
  • SSIS Event Handlers
  • SSIS Transactions
  • SSIS Logging
  • SSIS Parameters
  • SSIS Package Configuration
  • Configure using SQL Server
  • Config using Registry Entry
  • Conf with Environment Variable
  • SSIS XML Configuration File
  • XML Configuration File Part 2
  • SSIS Package Protection Level
  • SSIS Incremental Load
  • Incremental Load Example 2
  • SSIS Remove Double Quotes

Copyright © 2021 · All Rights Reserved by Suresh

About Us | Contact Us | Privacy Policy