Flat File Destination

Question

Hi,

I need to export data from a table to a flat file.

the file should start with first two blank rows (enter, not space) and then the data - from the third row.

is it possible?

 

thanks in advance.

Answer 1

Once you have your data flow desinged, and your flat file format is fixed, you could add a data flow task AHEAD of the first one and simply pad in two rows, populating one of the fied with an empty string. You could do this by having an Object variable (table) with two rows. Use it as the Source, add a Derived Column with the expression set to "", and send THAT field into the Flat File Destination. I know, not terribly elegant, but it could work.

---

Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Answer 2

thank you.

I tried it before. but when you map the "empty" rows, you must set a minimum of width to a column, and then I have like one space (at least) which is not good for me.

I just need to skip 2 rows. definitely empty rows, even from "space".

Answer 3

OK, next tactic:

Load the flat file WITHOUT any padded rows at the beginning, then use a Script Task to edit the file, adding 2 {CR}{LF} right after if finds the first one which would be at the end of the Header row. (BTW, I'm not a VB.Net expert, I don't actually DO this kind of stuff, I only SUGGEST it. But I know it can be done.)

---

Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Answer 4

Hello NetGirl,

Here are the steps you can follow.

Query you need to write will be:

Select Null as Col1,Null as col2,Null as Col3

Union All

Select Null as Col1,Null as col2,Null as Col3

Union all

Select Col1,Col2,Col3 from MyTable

Use this query in your OLEDB Source , then bring the Flat File Destination.

Connect your OLEDB Source to Destination, General Table will be same, Select the Columns Table on the left and in COLUMN DELIMITER Select TAB{t}

Test your DFT, IT will write two blank rows first and then you will have your data.

Thanks

Answer 5

thank you guys.

I wrote:

Select Null as Col0
UNION ALL
select Null as Col0

I maped the rows to the a flat file destination, using a different connection manager which is pointing to the same file, but has only one column.

then I used another DFT to export the data from the table. I maped the data to the file,  and I unchecked the "overwrite data in the file" in the ff destination.

Answer 6

Hello NetGril,

Please do these steps.

Write your query as i described,

Select Null as Col1,Null as col2,Null as Col3

Union All

Select Null as Col1,Null as col2,Null as Col3

Union all

Select Col1,Col2,Col3 from MyTable

--See here i have used the column name from your SQL table. You have to change this query with your requirements, If you have more columns comming from MyTable then above queries will change and add more Null as Col4 etc.

Delete your old Flat File Destination component as well your connection , and then drag flat file destination and create new connection.

You should have all the columns coming from your oledb source.. Map them and then in COLUMN DELIMITER use TAB{t}.

Thanks

Answer 7

Hi Aamir you are right i didnt refersh my page , sorry , please ignore my last post, i missed something.

---

Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).