Home > SSIS > Import variable record length CSV or any delimited file to a database using SSIS – SQL Server

Import variable record length CSV or any delimited file to a database using SSIS – SQL Server


Delimited text files are the method to store user data  in a file system in the past and even now. These files are easy to create and simple to transfer from one system to another system without trouble. In modern data technology we use various data base management system that keeps the data in tabular format and the latest Big data evolution keeps data in key and value pair or in No-SQL database. Now, we have got delimited files and database system to store. But, We also need to migrate data from the text file to database using data export utility. These utilities are very well designed to handle the delimited file with single data structure (ie fixed record length).

How do we import  variable record length delimited file to a SQL database?

In this demo, I will be using VB.net code to read variable length CSV file data and store it in SQL tables.

Scenario:

I have a CSV  file with ATM transactions of bank customers. This file contains two types of record with variable length. Customer information and ATM transactions are two types of record in this file. Now, I need to upload this file to tables tblCustomer and tblTransactions in SQL Server database.

We already know that we have to use script task to write .net code to read the file and split the line with delimited character and upload in to the tables respectively. I do construct the SQL insert statement in side the script task for data upload.

Sample CSV File

pic0Download CSV file (Bank_AX.TXT)

Table structure

tblCustomer

CREATE TABLE [dbo].[tblCustomer](
[Rec_ID] [varchar](50) NULL,
[AC_No] [varchar](50) NULL,
[Ac_Name] [varchar](50) NULL,
[TransactionDate] [varchar](50) NULL
) ON [PRIMARY]

tblTransaction

CREATE TABLE [dbo].[tblTransaction](
[Rec_ID] [varchar](50) NULL,
[Ac_No] [varchar](50) NULL,
[Transaction_Type] [varchar](50) NULL,
[Amount] [varchar](50) NULL,
[Transaction_Time] [varchar](50) NULL
) ON [PRIMARY]

Script

This image shows partial script. You can download the full code below this image.

pic1Full source code for reading variable length records is available for download here.

Output

Data has been uploaded to tblCustomer and tblTransaction tables after executing this script.

pic3

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: