Information Security Short Takes: SQL Server Bulk Import

A lot of people using the free MS SQL Server 2005 Express hit a brick wall when they try to import data into the created database. Here is a tutorial, with video demo included on how to use the command-line BCP tool to import data into MS SQL Server 2005 Express.

During an analysis i conducted in the past days, I also found out the hard way that MS SQL Server 2005 Express does not have a GUI based Data Transformation Services. The only thing it does have is a BCP command-line tool.

So, here is a step-by-step tutorial how to use the BCP tool and not give up on an otherwise good (and free) product:

The data - I am importing data collected by tcpdump. I stored the data into a CSV file (data.csv), a text file with a comma delimiter.
Here is a sample row 16,10.176.1.105,NULL,10.176.1.254,NULL,NULL,64,17.12.2007,19:20:52,520,PING Req,NULL
Creating the database - Log-in with the command-line sql tool (sqlcmd) and use the following set of commands to create the database and table for storing of imported data:

sqlcmd -S ATLAS\SQLEXPRESS
create database data_analysis
go
use data_analysis
go
create table data_import (
[No_packet] [int] NULL ,
[Src_Logical] [varchar] (255) ,
[Src_Port] [varchar] (255) ,
[Dest_Logical] [varchar] (255) ,
[Dest_Port] [varchar] (255) ,
[Flags] [varchar] (255) ,
[Packet_Size] [int] NULL ,
[Packet_Date] [varchar] (255) ,
[Absolute_Time] [varchar] (255),
[Additional] [varchar] (255) ,
[Protocol] [varchar] (255) ,
[newdata] [varchar] (255)
)
go

Content verification - To verify the contents of the created table, use the following set of commands

use data_analysis
select count(*) from data_import
go
quit

Data import - To import the data, use the following command

bcp data_analysis.dbo.data_import in data.csv -T -C1250 -c -t, -S ATLAS\SQLEXPRESS

Detailed explanation

bcp - the executable file name
data_analysis.dbo.data_import - name of database, owner and name of table to receive the data
in - the same command is used for export and import. in means importing, out means exporting
data.csv - file name that contains data to be imported, or to receive exported data when using the out direction
-T - swich indicating trusted connection. When using this switch, the bcp command uses the kerberos ticket of the logged-on Windows user to authenticate. If you don't use -T, you'll have to use -U and -P (user name/password)
-C1250 - collation. I found out that BCP does not work well with Unicode files, so i am forcing the 1250 collation (central European) - works with most characters
-c - treat everything as characters. This way it will be very easy to import any information.
-t, - delimiter. Default delimiter for BCP is tab, so i need to inform it of my delimiter character (comma)
-S ATLAS\SQLEXPRESS - server. This switch is followed by the hostname\instance name (for MS SQL Server Express its SQLEXPRESS)