Biml - Bulk Insert Task

The Bulk Insert Task is the SSIS equivalent of the bcp utility or the BULK INSERT SQL Command. It's a way to import flat file data very efficiently with no real Transformation in your ETL. Using Biml to create an Bulk Insert Task requires only a few lines of code but you'll observe I build out a full fledged demo.

Setup

I am a fan of working in tempdb so create the following table there, or any other scratch database you have handy. I'm going to create a table called BulkInsert and load a simple file into it.

Table declaration

CREATE TABLE dbo.BulkInsert
(
    EventNumber int NOT NULL
,   EventLocation varchar(50) NOT NULL
);

File declaration

I use a base or root folder for all of my SSIS file work. I call it "SSISData" as I'm original like that, and then create subject area subfolders and within each subfolder, I have 3 folders: Archive, Input, and Output. In this case however, I will have a file sitting in the root of the folder named "C:\SSISData\NoHeaderRow.tsv" (tsv means tab separated values) so if the following comes across as spaces, replace them with the tab character.

53	Kansas City

Biml

The following code uses two connection managers. CM_File is going to refer to the flat file created above. CM_OLE is looking for a named instance of Dev2012 on the local machine. Change these values as you see fit.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <FileConnection Name="CM_File" FilePath="C:\ssisdata\NoHeaderRow.tsv" FileUsageType="ExistingFile" HasMultipleFiles="false" />
        <OleDbConnection Name="CM_OLE" ConnectionString="Data Source=localhost\dev2012;Initial Catalog=tempdb;Provider=SQLNCLI11.0;Integrated Security=SSPI;"/>
    </Connections>
    
    <Packages>
        <Package Name="Task_BulkInsert" ConstraintMode="Linear">
            <Tasks>
                <BulkInsert Name="BLK Load Data" ConnectionName="CM_OLE" DataFileType="Char" TableLock="true">
                    <SourceFile ConnectionName="CM_File"></SourceFile>
                    <ExternalDestinationTable Table="[dbo].[BulkInsert]" />
                </BulkInsert>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Results

Right click on that Biml script and you'll have a package named "Task_BulkInsert" that looks something like

If you double click on the Task, you will see options we set for DataFileType and TableLock match what is implemented. Powerful stuff.