Create and Edit flat file from Sqlserver (SP).

HI All,
I use sqlserver2000 server.
I have a requirement to create a flat file(.txt) and dump the data into the file with some formatting.
I tried to use DTS but,
1. it doesnt allow me to put one row information of table to multiple line in the flat file.
2. Dynamically we cannot create n number of files we need from DTS.

Now, i am trying to create a file from sql server Stored Procedure and write data into it.

Can any one help me..
How to create and write to file from sqlserver (sp) .

Regards

Abdul lateef.Originally posted by a_lateef

I tried to use DTS but,
1. it doesnt allow me to put one row information of table to multiple line in the flat file.
2. Dynamically we cannot create n number of files we need from DTS.


Maybe if you add a ActiveX script step in the DTS package. Hope someone else can explain further...|||Greetings!

I personally would just write a script (pick a language of choice) to pull the data from the server, format it and then write it to a textfile. Although there are ways to do it in SQL Server it is very cumbersome and I think you will find that just about any other language will offer a lot more flexibility in this case. If you use something like a Visual Basic Script file you could then just set it up as a job to run when it was supposed to.

Maybe someone else could offer something else on this, good luck!

HTH!|||I am unclear as to what you want in the text file - could you explain in detail (what are your requirements) - maybe with an example.

create a flat file on a remote server

Hi,

In the new flat file connection dialog box, can i create a flat file on a remote server? thanks.

Yes, if you can access that server through a network share. Try using the UNC path, e.g.

\\ServerName\ShareName\Folder\File.txt

\\DGPC\C$\Temp\File.txt

sql

Create a flat file for each record in a table

I'm rather new to ssis and I've been reading and testing but didn't find a solution for this problem.Supose I've got a table Customer with some fields. One of the fields is CustID.I want to create as many flat files as there are Customers in the table with flat file name set to the CustID.If you could point me in a good direction, It would be nice.Greetings from Belgium

Do you want any data in these files or just the file to be created? If you just want the file to be created you could aggregate the data and then use the FileExtractor to create the file (You would need to add 2 columns one for the file name and one for the file data (empty)). If you actually want the data to flow into these files then you would most likely want to use a script component.

HTH,

Matt

|||

It sounds like you want to run a exectue SQL task to get an ADO object which holds the records you want. Then for each of these you what to push thenm to a for loop and have a dataflow inside the for loop. In the Dataflow the source would be the ADO object and you can push it to the text file.

These pages should help a bit

http://blogs.conchango.com/jamiethomson/archive/2005/12/04/2458.aspx

http://sqljunkies.com/WebLog/knight_reign/archive/category/458.aspx

|||Thanks!

Counting rows from Flat File Source

Hello,

Is there a way (perhaps a property) to capture the number of rows selected from a Flat File Data Flow Source without having to develop a script to loop through the rows and count them?

Thanks a lot,

Grace

Yep. Use the ROWCOUNT component.

Row Count Transformation

(http://wiki.sqlis.com/default.aspx/SQLISWiki/Row%20Count%20Transformation.html)

-Jamie