SSIS basics – import CSV to a SQL Server table

Posted: June 30, 2012 in MSSQL
Tags:

Task: to give a basic look at the import of a CSV file to a SQL Server table by means of SSDT by Microsoft Visual Studio



Sales per region,Africa,Asia,Europe
January,34,67,56
February,36,87,78
March,31,56,88
April,29,67,92
May,54,71,68
June,68,71,54
July,54,71,68
August,68,71,54
September,92,67,29
October,88,56,31
November,78,87,36
December,56,67,34

Source code 1: CSV data



USE [Test]
GO

CREATE TABLE [dbo].[SSIS_CSV_Import]
(
    [Month]      [varchar](9)    NOT NULL,
    [SaleAfrica] [numeric](2, 0) NOT NULL,
    [SaleAsia]   [numeric](2, 0) NOT NULL,
    [SaleEurope] [numeric](2, 0) NOT NULL
)
GO

Source code 2: Table structure


Flat File Connection Manager Editor – General

Figure 1: Flat File Connection Manager Editor – General


Flat File Connection Manager Editor – Columns

Figure 2: Flat File Connection Manager Editor – Columns


Flat File Connection Manager Editor – Advanced

Figure 3: Flat File Connection Manager Editor – Advanced


SQL Destination Editor – Connection Manager

Figure 4: SQL Destination Editor – Connection Manager


SQL Destination Editor – Mappings

Figure 5: SQL Destination Editor – Mappings


SSIS Package – Data Flow

Figure 6: SSIS Package - Data Flow



SELECT [Month], [SaleAfrica], [SaleAsia], [SaleEurope]
FROM   [dbo].[SSIS_CSV_Import]
GO
/* Result:
   Month       | Africa | Asia | Europe
   January       34       67	 56
   February      36       87     78
   March         31       56     88
   April         29       67     92
   May	         54       71     68
   June	         68       71     54
   July	         54       71     68
   August        68       71     54
   September     92       67     29
   October       88       56     31
   November      78       87     36
   December      56       67     34
*/

Source code 3: SQL query for result verification

Additional references:
Advertisements
Comments
  1. Swathi says:

    Good article for beginners. Thanks a lot!

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