ssis Read from a CSV file Read from a CSV file and insert data into a table

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Extensions
> Step 2: And Like the video. BONUS: You can also share it!

Example

First, you need to prepare the environment by creating the SQL Server table and the CSV file.

Run the script below in SQL Server to create the SQL table either on a new database or an existing one. For this example, I used my ‘TrainingDB’ database.

/* Creates table for Students.csv */
CREATE TABLE StudentDetails
(
    Surname varchar(50),
    Firstname varchar(50),
    DateofBirth datetime,
    PostCode varchar(50),
    PhoneNumber varchar(50),
    EmailAddress varchar(50)
)

Now create a CSV file with the data below.

SurnameFirstnameDOBPostcodePhoneNoEmailAddress
BongaFred24-02-1990SA1 5XR08100900647[email protected]
SmithGill08-05-1992RMT 12TY08200900793[email protected]
TaylorJane01-12-1979PM2E 3NG09600900061[email protected]
BrownJohn06-10-1986CQ7 1JK08200900063[email protected]
CoxSam18-03-1982STR3 9KL08100900349[email protected]
LewisMark30-09-1975DN28 2UR08000900200[email protected]
KaurAhmed26-07-1984NI12 8EJ09500900090[email protected]

You can copy this into Excel and save as a CSV file.

After launching Microsoft Visual Studio, navigate to File - New - Project, as shown below. enter image description here

Under the Business Intelligence group, select Integration Services and Integration Services Project. Enter a name for project and a name for the solution, for example “Load CSV”. You can check the “Create a directory for solution” box if you want to create a solution. enter image description here Click OK

On the right side of the displayed screen, in the “Solution Explorer” window, change the name of the default package to “Load CSV File into Table” enter image description here

On the left side of the screen, in the SSIS Toolbar, drag the “Data Flow” to the “Control Flow” window and rename the task to “Load CSV File”

Next, you need to setup the connection managers for both the CSV file and the SQL Server table, which are also known as source and destination respectively. At the bottom of the screen, under Connection Managers, do a right click and select “New Flat File Connection” and configure the Flat file connection manager as shown below. enter image description here

Enter a suitable Connection manager name and specify the filepath for the Students.csv file. Click OK.

For the table’s connection manager, do a right click again in the Connection Managers window and click on “ New OLE DB Connection”. Click on New and specify the Server name and database name that contains the StudentsDetail table.

enter image description here

You can test the connection by clicking “Test Connection” then click OK and OK again. You should now have the 2 Connection Managers at the bottom of the screen.

Drag the “Flat File Source” from the SSIS Toolbox into the “Data Flow” window and rename it as “CSV File”. enter image description here

Double click on this source and select the “Student CSV File” connection manager. Click on Columns on the left side of the screen to review the columns in the file. Click OK.

Then drag the “OLE DB Destination” from the SSIS Toolbox to the “Data Flow” window and rename it as “SQL Table”. Drag the blue arrow from the source to the destination.

Double click on the destination and configure as shown below.

enter image description here

Click on Mappings on the left side of the screen and ensure all fields are mapped correctly from source to destination.

enter image description here

Click OK. Your screen should look like the image below. enter image description here

Run the package by clicking on Start. When the package finishes executing, you can check the table to view the data from the CSV file.



Got any ssis Question?