SSIS basics – import CSV to a SQL Server table

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

Figure 1: Flat File Connection Manager Editor – General

Figure 2: Flat File Connection Manager Editor – Columns

Figure 3: Flat File Connection Manager Editor – Advanced

Figure 4: SQL Destination Editor – Connection Manager

Figure 5: SQL Destination Editor – Mappings

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:

• Comma-separated values
  by Wikipedia
• SQL SERVER – Import CSV into Database – Transferring File Content into a Database Table using CSVexpress
  by Pinal Dave
• SQL SERVER – Importing CSV File Into Database – SQL in Sixty Seconds #018 – Video
  by Pinal Dave
• SQL SERVER – Powershell – Importing CSV File Into Database – Video
  by Laerte Junior
• SQL SERVER – Running SSIS Package in Scheduled Job
  by Pinal Dave