Biml - Bulk Insert TaskThe 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.
SetupI 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.
CREATE TABLE dbo.BulkInsert ( EventNumber int NOT NULL , EventLocation varchar(50) NOT NULL );
File declarationI 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
BimlThe 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>
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.