SSIS – Handling Comma’s in CSV Export

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:

My result is shown below:

Notice how the last name has some suffix in it.

Next I will show the typical approach to exporting data from an OLEDB source into a .csv file in a serious of steps

Step One: Create a data flow task

Step Two: Create your Source and destination tasks

Step Three: Connect the data source. In my case, I am connecting to the employee table I created earlier which contains the last names including commas.

Step Four: Configure the flat file connection manager.

Notice that the highlighter text qualifier section has a default value <none>

Step Five: Complete and run your package

Step Six: Review the generated .csv file

You will notice that everything looks fine until column D (MiddleName). The suffix has “spilled” over to that column and the middle name has over shifted over to the BirthDate.  You will also notice that a new column has been added (column I) which has no header and was not part of the original employee table.

This is a problem and it is quite easy to solve.

The key is to utilize the text qualifier option in the flat file connection manager as shown in a couple of steps below.

Step Seven: Repeat the same steps as above (Step One to Step Three).

Step Eight: This time around, when you want to configure the flat file connection manager, replace the <none> in the text qualifier option with a quote (“) as highlighted in the image below.

Step Nine: Complete and run your package

Step Ten: Review the generated .csv file

You will notice that the data is returned as it was in the source table. The last name is displayed correctly without and spilling over.

Although this is a simple process, it is often overlooked by many developers. This is sometimes due to the volume of data we work with and sometimes because we often do not encounter names with suffix or maybe because we just never noticed.

I will be very happy to hear your feedback on this. Let me know your thoughts by adding them in the comment section.

Thanks for reading and I hope it helps someone out there.

 

Posted in SQL Server, SQL Server Integration Services (SSIS) | Tagged , , , , , , | Leave a comment

OUTPUT Clause (Transact -SQL)

When you perform a DML operation using an INSERT, UPDATE, DELETE or MERGE statement, an OUTPUT clause can be used to return information regarding the rows affected. You can find this useful when you need to archive, audit or even troubleshoot the result of DML operations performed on a table.

There are two keywords that can be “activated” when you specify the OUTPUT clause.

1. Inserted:
The inserted keyword is “activated” when you use INSERT with OUTPUT.
For example:
For this example, I will be creating a temporary table called “employee” and I will be inserting some records into the table.
With the OUTPUT clause, we’ll be able to display the records that were inserted into the table.

The OUTPUT clause in this case basically worked as a SELECT statement, giving us information about the data that was inserted into the table.

2. Deleted:

When the OUTPUT clause is used in conjunction with a DELETE statement, the deleted keyword is enabled.

For example:

Based on the sample we created above, I am going to delete records belonging to employee ID 5.

You can see that the resultset only shows us the data that was deleted and nothing more.

3. Inserted and Deleted:

Both the inserted and deleted keywords are enabled when the OUTPUT clause is used alongside an UPDATE statement.
For example:
I am going to update the salary of Pete who has an Employee ID “2” from $60,000 to $65,000. Here we will see how the OUTPUT clause behaves with an UPDATE statement.


From the resultset above, you can see that I was able to utilize both the inserted keyword and the deleted keyword. This is because an UPDATE is simply SQL Server deleting an old record and inserting a new record in its place

CONCLUSION
There are so many uses to the OUTPUT clause such as for archiving, auditing and informational purposes. You can even input the result from the OUTPUT into a table or even use it in a stored procedure.

Posted in Uncategorized | Leave a comment

Testing Crayon Syntax Highlighter

Since I will be writing a lot of SQL queries on this blog, I decided to try out one of the syntax highlighters out there. The one I am using is called Crayon Syntax Highlighter and it seems to do the what I want it to do quite well. So let’s go!

Above is a sample query from the Microsoft Adventureworks database. I am simply performing a join between the employee table and the person table in order to get the first name, last name, birth date, hire date and job title of all the employees working in the company.

Posted in Uncategorized | Leave a comment

Hello world!

Yes, Hello world!

I am excited to finally start this blog this year, 2017. I got the SQLAmerica.net domain in 2015 and I have not been able to get something started. I got a lot of motivation from Kenneth Fisher (www.sqlstudies.com) who told me to just start.

Now we are here. I am hoping that by this time next year, I will look back at this first post and be amazed at how much progress has been made. I will be super excited to see this happen.

So, what is SQL America all about? Honestly, I don’t know. But one thing is sure, I am believing this will be an opportunity for me to share the little I know as I learn on a daily basis. My blog might not become something big or extraordinary but one thing is sure, I will be very happy if just one person is helped from reading this blog.

With all that said, I want to welcome you to 2017 and welcome to my world of SQL America.

Hello World!

Posted in General, Uncategorized | Tagged , , | Leave a comment