Purpose:
This post covers the process of developing an SSIS package that reads a CSV file and loads it into a SQL Server table.
Development Environment:
Visual Studio 2022 Community Edition.
SSIS (SQL Server Integration Services) 2022.
SQL Server 2022 Developer Edition.
1. csv file
sample_sales_data.csv
2. Create SQL Server Loading Table
-- Select database
USE SSIS;
go
-- create loading table
CREATE TABLE SalesData (
OrderID INT PRIMARY KEY,
CustomerName NVARCHAR(100),
Product NVARCHAR(100),
Quantity INT,
Price DECIMAL(10,2),
OrderDate DATE
);
3. Development SSIS Package
- insert data flow task
- double click data flow task
- insert flat file source
- flat file source > edit > flat file source editor
- new > flat file connection manager edit > browse > sample_sales_data.csv
- insert ole db destination > connect arrow (from flat file source to ole db destination)
- ole db destination > edit > ole db destination editor
- new > configure ole db connection manager
provider > Native OLE DB\Microsoft OLE DB Provider for SQL Server
server name > local pc name
authentication > Windows Authentication
select database name > SSIS
- ole db destination editor
data access mode > Table or view - fast load
Name of the table or the view > SalesData
- click start > package execute
- check loading data
