question

CzarR-3851 avatar image
0 Votes"
CzarR-3851 asked ZoeHui-MSFT commented

SSIS flat file add double quotes

Hi ,I have a flat file that I am moving from one location to another. While moving from source location to destination I want to add double quotes to the header as well as rows(Enclose the column names and data values in double-quotes) while loading it to the destination. How do I do that?

Thanks in advance.

sql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CzarR-3851 avatar image
0 Votes"
CzarR-3851 answered ZoeHui-MSFT commented

Hello @Zoehui-MSFT , why can't we simply use just Flat file source directly connected to Flat file destination. And in the Flat file destination just change the Text qualifier to quotes.?? Why this lengthy route?

Also, the following settings for Flatfilesource and FlatFiledestination. Please suggest. Just trying to learn. Thanks in advance.
100569-capture.png



capture.png (41.6 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @CzarR-3851, in my opinion, the Flat file source format is to use to extract the data we need in the file instead of directly modifying the content the data in the file so if we need to use Derived Column Transformation to define the new columns.
flat-file-source

0 Votes 0 ·
ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered ZoeHui-MSFT edited

Hi @CzarR-3851,

You may follow the steps to meet your requirement.

1.In the flat file source connection manager, don't choose "Column names in the first data row"

99665-screenshot-2021-05-26-100542.jpg

2.Use Derived Column to add double quotes.

 " \" " + [Column 0] + " \" "

99619-screenshot-2021-05-26-100753.jpg

3.In the destination file manager, choose header rows to skip for 1.

99608-screenshot-2021-05-26-101242.jpg

The result is like below:

99654-screenshot-2021-05-26-101517.jpg

If I misunderstand your needs, please incorrect me.

Regards,

Zoe


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.