Scroll to top
  • Contact Us - (480) 750-9756

SSIS – How to deal with commas in data when exporting to a CSV file

admin - August 13, 2019 - 0 comments

At work, I often find myself exporting data in .csv format for our clients. I am talking about thousands of data, to the point that I never really noticed this issue.

I am talking about data that contain special characters like a comma for example.

So once in a while, I’d encounter names with commas in them. Names with suffixes like “Smith, Jr”, “Donald, Jr”, or “Elizabeth, III” and so on.

When exporting to a comma separated value .csv file using SQL Server Integration Services (SSIS), the flat file destination connection manager assumes that this comma is the end of the data and therefore “spills” the rest of the data into the next column. The problem with this is that it disrupts the arrangement of the output data thereby causing inconsistent and even incorrect output.

In the following example, I will be showing how to correct this issue using a text qualifier:

Before I continue, I will create my sample data below:


CREATE TABLE dbo.Employee
  EmpID  INT,
  FirstName VARCHAR(20),
  LastName VARCHAR(20),
  MiddleName VARCHAR(20),
  BirthDate DATE,
  HireDate DATE,
  Salary  MONEY,
  EndDate  DATE
INSERT INTO dbo.Employee (EmpID, FirstName, LastName, MiddleName, BirthDate, 
      HireDate, Salary, EndDate)
(1, 'John', 'Brown, Jr.','K', '01-01-1960', '10-01-1985', 85000.00, GETDATE()),
(2, 'Pete', 'Dawn, III', NULL,'12-03-1965', '12-01-2000', 60000.00, NULL),
(3, 'Amanda', 'Lopez', NULL,'06-01-1980', '12-01-2005', 45000.00, NULL),
(4, 'Adam', 'Smith, Sr.', NULL, '02-26-1971', '04-23-2001', 70000.00, GETDATE()),
(5, 'Taylor', 'Ramsey, Jr', NULL, '03-01-1984', '11-11-2004', 60000.00, NULL)

SELECT * FROM dbo.Employee
My result is shown below:

Post a Comment

Your email address will not be published. Required fields are marked *