A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Tuesday, January 28, 2014

Biml - Bulk Insert Task

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.


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


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">
        <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;"/>
        <Package Name="Task_BulkInsert" ConstraintMode="Linear">
                <BulkInsert Name="BLK Load Data" ConnectionName="CM_OLE" DataFileType="Char" TableLock="true">
                    <SourceFile ConnectionName="CM_File"></SourceFile>
                    <ExternalDestinationTable Table="[dbo].[BulkInsert]" />


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

Bulk Insert Task package

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

Bulk Insert Task Connections tab
Bulk Insert Task - Advanced tab

No comments: