SSIS Flat file manager column delimiter Issue

SSIS and Import/Export
1

0

Hi,

I created SSIS package to export SQL results to csv format , in that I have sleeted column delimiter as "Comma" but the problem is since I have comma existing in one of sql results column its dividing as two fileds in output csv file... But that should be one filed in csv file.. How can I fix this ?

Can any one please help me on this?

Thank you..

2

You didn't say which version of SSIS/SQL Server you are using.

First, if you have the separator (in your case comma) as part of the data, you have to escape them. For example, like this:
a,"b,c",d
where I have escaped the second column with double-quotes. In addition to that, you have to tell SSIS that you are using double-quotes as escape character.

Even with all of that, some versions of SQL Server cannot properly process csv files. See some discussion here. I don't know if the issue has been fixed in a newer version of SQL Server.

What I normally do is to ask the data provider to send the data using some other delimiter such as pipe (|) or caret (^) that is guaranteed to be not part of your data. Which delimiter you should use depends on the nature of your data and the business segment you are in. In my case - financial data - usually pipe character works.

3

Hi James,

I am using 2008 R2 version of SQL server .

I have escaped the SQL column which has comma with double quotes.

How can I tell SSIS that I am using double-quotes as escape character ?

Thank you for helping me on this.

4

If you are using Import/Export wizard, see the screen-shot below.

If you already have an SSIS package, open it in BIDS, right-click on the source file connection manager, select properties. See screenshot below.

5

I did that part adding Text Qualifier but still SQl column deviding in to 2 fields in csv as shown below

One field is DAVE and other other filed is LINDA S " "

But it supposed be in one filed as DAVE,LINDA S

Can you please help me?

6

Looks like you have escape character also as part of the data. In that case, the normal way to escape the escape would be with another escape. However, you may be running into the inherent limitation in SSIS in SQL 2008 R2, as was discussed in the link that I had posted earlier - I just don't know.