Flat File Custom Formatting output issue.

Question

Desired Output: To Flat file from typical OLEDB Source SQL Server 2005 simple table.
"ABCDE  ""AA" 6144029 90241354 00112234"SQLSERVERDOTNET                      ""NAME                             "00000"*""     ""     ""00000000""000000"


Current Output from SSIS: 
ABCDE  ,AA,61.44029,90.241354,11.2234 ,SQLSERVERDOTNET                     ,NAME                            ,.00000000,*,     ,     ,,

Tried:
1) To avoide commas delimitation in current output, created 1 derived column, concatenating all the values, with the formatting and quotes, and trying to use "Fixed width" option in SSIS.

2) BCP command with format file, but format file, but that's too confusing to figure out! working on that route too though.

Any pointers and help will be appreciated

Answer 1

Your requirement is something like:
you have input columns comming from a table. (col1,col2,col3)
you want to put all these columns into a flat file using "" for each column.

Use a derived column with this expression
"\"" + col1 + "\"" + "\"" + col2 + "\"" + "\"" + (DT_WSTR,10)col3 + "\""

While creating the flat fule connection manager, use the default settings (i.e delimited format).
Then go to advanced tab and delete all the columns except the column created in derived-column component.

Correct me if I did not get your actual question

---

Nitesh Rai- Please mark the post as answered if it answers your question

Answer 2

Hello Nitesh,

Thansk for your prompt response.

Idea of deleting other columns and using comma seperated with 1 derived column, would result in comma not showing though selected, and that's a new idea I got from you.

However, I was wondering how I can use BCP and a format file to achieve the goal. I have the format file for bulk insert which I used to first load table from file. Now I need to export the same data in the same format as I had imported using Bulk BCP command or using Bulk Insert transformation. I was surprised SSIS doesn't have anything like Bulk export?!! So wanted clarity on those aspects. However, for now I'll proceed like you mentioned, but I'll keep this question unanswered so you or someone else can provide clarity which also I need,

Regards.