Fill SQL database from a CSV File

Question

I need to create a database using a CSV file with SSIS. The CSV file includes four columns:

I need to use the information of that table to populate the three tables I created in SQL below.

I have realized that what I need is to use one column of the Employee Table, EmployeeNumber, and Group Table, GroupID, to populate the EmployeeGroup table. For that, I thought that a Join Merge table is what I needed, but I created the Data Flow Task in SSIS, and the results are the same, no data displayed.

The middle table is the one used to relate the other tables.

I created the package in SSIS and the Employee and Group Tables are populated, but the EmployeeGroup table is not. EmployeeGroup will only show the EmployeeNumber and Group ID columns with no data.

I am new using SSIS, and I really do not know what else to do. I will really appreciate your help.

Fitzsimmons

Did you try to do this all with one data flow? You might need several. – Canker

It sounds to me like you need to first populate your Employee and Group tables, and then - in another data flow, to run after the first - populate the EmployeeGroup table from a join between your initial data source and your Group table. – Canker

I would recommend loading your file to a staging table first and then querying the staging table to insert into your other tables. – Ludwog

Answer 1

Overview

1. Solutions using SSIS
   • Using 3 Data Flow Tasks
   • Using 2 Data Flow Tasks
2. Solutions Using T-SQL
   • Using Microsoft.Ace.OLEDB
   • Using Microsoft Text Driver
3. Solutions Using PowerShell

1st Solution - SSIS

Using 3 Data Flow Tasks

This can be done using only 2 Data Flow Task, but according to what the OP mentioned in the question I am new using SSIS, and I really do not know what else to do, i will provide easiest solution which is 3 DataFlow Task to avoid using more components like MultiCast.

Solution Overview

Because you want to build a relational database and extract relations from the csv, you have to read the csv 3 times -consider it as 3 seperated files -.

First you have to import Employees and Groups Data, Then you have to import the relation table between them.

Each Import step can be done in a seperate Data Flow Task

Detailed Solution

1. Add a Flat File connection Manager (Csv File)
2. Add An OLEDB connection Manager (SQL Destination)
3. Add 3 DataFlow Task like the image below

First Data Flow Task

1. Add a Flat File Source , a Script Component , OLEDB destination like shown in the image below

2. In the Script Component choose Group Name column as Input

3. Select the Output Buffer and change SynchronousInputID Property to None And add an output column OutGroupname with type DT_STR

4. In the Script section write the following Code:

    Imports System.Collections.Generic
   
    Private m_List As New List(Of String)
    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
   
   If Not Row.GroupName_IsNull AndAlso
           Not String.IsNullOrEmpty(Row.GroupName.Trim) Then
   
       If Not m_List.Contains(Row.GroupName.Trim) Then
   
           m_List.Add(Row.GroupName.Trim)
   
           CreateOutputRows(Row.GroupName.Trim)
   
       End If
   
   
   End If
   End Sub
   
   Public Sub CreateOutputRows(ByVal strValue As String)
   
   
   Output0Buffer.AddRow()
   Output0Buffer.OutGroupName = strValue
   End Sub
   

5. On the OLEDB Destination map OutGroupName to GroupName Column

Second Data Flow Task : Import Employees Data

• Repeat the same steps done with Groupname Column : with a single difference that is you have to choose the EmployeeID, Employee Name, LoginName columns as Input in the Script Component and Use the ID Column instead of Groupname column in the comparaison

Third Data Flow Task : Import Employees_Group Data

1. You have to add a Flat File Source , Look Up transformation , OLEDB Destination

2. In The LookUp Transformation Component select Groups Table as a Lookup table

3. Map GroupName Columns and Get Group ID as output

4. Choose Ignore Failure in the Error Output Configuration

5. In Oledb Destination map columns as following

Note: GroupID must be an Identity (set it in sql server)

Using 2 Data Flow Tasks

You have to do the same steps as the 3 Data Flow Tasks solution, but instead of adding 2 Data Flow Tasks to Group and Employee, just add one Data Flow Task, and after the Flat File Source add a MultiCast component to duplicate the Flow. Then for the first flow use the same Script Component and OLEDB Destination used in the Employee Data Flow Task, and for the second flow use the Script Component and OLEDB Destination related to Group.

---

2nd Solution - Using TSQL

There are many method to import Flat file to SQL via T-SQL commands

OPENROWSET with Microsoft ACE OLEDB provider

Assuming that the installed version of Microsoft ACE OLEDB is Microsoft.ACE.OLEDB.12.0 and that the csv file location is C:\abc.csv

1. First Import data into Employee and Group Table

   INSERT INTO [GROUP]
       ([Group Name])
   SELECT 
       [Group Name] 
   FROM 
       OPENROWSET
           (
               'Microsoft.ACE.OLEDB.12.0','Text;Database=C:\;IMEX=1;','SELECT * FROM abc.csv'
           ) t
   
   
   INSERT INTO [Employee]
       ([Employee Number],[Employee Name],[LoginName])
   SELECT 
       [Employee Number],[Employee Name],[LoginName] 
   FROM 
       OPENROWSET
           (
               'Microsoft.ACE.OLEDB.12.0','Text;Database=C:\;IMEX=1;','SELECT * FROM abc.csv'
           ) t
   

2. Import the Employee_Group Data

   INSERT INTO [EmployeeGroup]
       ([Employee Number],[GroupID])
   SELECT 
       t1.[Employee Number],t2.[GroupID]
   FROM 
       OPENROWSET
           (
               'Microsoft.ACE.OLEDB.12.0','Text;Database=C:\;IMEX=1;','SELECT * FROM abc.csv'
           ) t1 INNER JOIN GROUP t2 ON t1.[Group Name] = T2.[Group Name]
   

OPENROWSET with Microsoft Text Driver

1. First Import data into Employee and Group Table

   INSERT INTO [GROUP]
       ([Group Name])
   SELECT 
       [Group Name] 
   FROM 
       OPENROWSET
           (
               'MSDASQL',
               'Driver={Microsoft Text Driver (*.txt; *.csv)};
               DefaultDir=C:\;',
               'SELECT * FROM abc.csv'
           ) t
   
   
   INSERT INTO [Employee]
       ([Employee Number],[Employee Name],[LoginName])
   SELECT 
       [Employee Number],[Employee Name],[LoginName] 
   FROM 
       OPENROWSET
           (
               'MSDASQL',
               'Driver={Microsoft Text Driver (*.txt; *.csv)};
               DefaultDir=C:\;',
               'SELECT * FROM abc.csv'
           ) t
   

2. Import the Employee_Group Data

   INSERT INTO [EmployeeGroup]
       ([Employee Number],[GroupID])
   SELECT 
       t1.[Employee Number],t2.[GroupID]
   FROM 
       OPENROWSET
           (
               'MSDASQL',
               'Driver={Microsoft Text Driver (*.txt; *.csv)};
               DefaultDir=C:\;',
               'SELECT * FROM abc.csv'
           ) t1 INNER JOIN GROUP t2 ON t1.[Group Name] = T2.[Group Name]
   

Note: You can Import Data to a staging table, then query this table, to avoid connecting many times to the csv File

---

Solutions Using PowerShell

There are many method to import csv files to SQL server, you can check the following links for additional informations.

• Four Easy Ways to Import CSV Files to SQL Server with PowerShell
• How to import data from .csv in SQL Server using PowerShell?

---

References

• OPENROWSET (Transact-SQL)
• T-SQL – Read CSV files using OpenRowSet
• Import error using Openrowset

Matti

Why not add a third option Powershell? – Impermanent

@MarkKram i don't have a good experience in Powershell, but i am interesting to see how it can be done. If you know it plz write an answer. and i will upvote it surely – Matti

@MarkKram i added some helpful links about importing csv to sql-server using powershell to my answer. – Matti

With Powershell (PoSh) you can leverage the .Net framework (think CLR) within a scripting environment. You can also run the PoSh script natively in an SQL Agent job. I use it all the time to import CSV, Excel, and Access data. It's very powerful and simply to use. – Impermanent

@MarkKram why not posting a powershell detailed answer? – Matti

Well mainly is that I don't have access to a computer right now and it is too much typing on a cell phone. There are quite a few examples on this site. – Impermanent

@MarkKram That's ok. I list it in my answer. If the OP want to use it. He can access to the links i added. Thx for the remark – Matti

What an effort you put into your answer +1 – Machuca

@plaidDK thanx, i really appreciate that – Matti

Answer 2

I think the easiest solution would be to import the csv to a flat staging table and then use some insert into...select statements to populate the target tables. Assuming you know how to import to a flat table, the rest is quite simple:

INSERT INTO Employee (EmployeeNumber, EmployeeName, LoginName)
SELECT DISTINCT EmployeeNumber, EmployeeName, LoginName
FROM Stage

INSERT INTO [Group] (GroupName)
SELECT DISTINCT GroupName 
FROM Stage

INSERT INTO EmployeeGroup(EmployeeNumber, GroupId)
SELECT DISTINCT EmployeeNumber, GroupId
FROM Stage s
INNER JOIN [Group] g ON s.GroupName = g.GroupName

You can see a live demo on rextester.

Lubalubba

Answer 3

Since you already know how to import the csv and extract two tables (Employee and Group), I suggest you just populate EmployeeGroup in the same way. And stop using a group_id. If you do that, you'll get sql statements like:

select [Employee Number], [Employee Name], LoginName from Employee
select [Group Name] from Employee
select distinct [Employee Number], [Group Name] from Employee

Most likely, you'll have similar statements already working for Employee and Group. In this option you can make it work in the same way, without using a Join Merge. It's a usefull option, but clearly somewhere in that component something goes wrong.

Supersedure