generate a text file from SQL Server

Hi

I need to generate a text file from SQL Server. The task automatically runs daily.

The format of the text file use "/" to separate the field and the row. At the end of the file, it adds number of char and uses "\". The number of char includes the data parts and excludes a few word of "the number of char".

For example,
The underline fonts mean the first row record. The Bold fonts mean the second row record. The Italic fonts mean the third row record. The red fonts mean the number of char and the end of file.

1/060222/008888/234/1/7441/2/BB/10000//////290025/////1/060222/008888/234/1/7441/3/XX/100-//////290025/////1/060222/008881/234/1/7442/2/BB/10000//////290025/////161\

I am no idea about this problem. Please give me some suggestions. Thanks a lot

regards
Alex

For your information

I use SQL Server 2000 in Windows 2000 Server environments

Hi Alex,

You need to generate a Text File from SQL Server with field delimeter as "/" and the line delimeter as "/////"?

Also please let us know that whether you want to generate a Text file with static filename or it may vary?

Anyway I will also try to generate the file based on my understandings from your post.

Regards,

Prakash Srinivasan

|||

Hi Prakash Srinivasan

The field delimeter is "/" and there is no line delimeter.

The task generates many files and place at different places where are the fixed folders. The filenames are static and need to override the old one.

Thank you for your help.

Alex

|||

Hi Alex,

If you don't have any line delimeter (Row delimeter), then how would you differentiate the records. So there should be a row delimeter in the file to separate the records.

I assume there is and proceed further.

Regards,

Prakash Srinivasan

|||

Hi Alex,

First create a Package and put a Data Flow. In the Data Flow, put one OleDb Source and Flat File Destination. Also create Connection for both OleDb Source and Flat File Destination.

And in the Flat File Connection Manager, specify the filename in which you want to dump the data, and leave the rest of the setting in General tab as it is.

In Advanced tab, create columns based on your requirement (Suppose if you need only 3 columns, create 3 columns and set the datatype based on the datatype specified in your SQL Server database).

In Columns tab, give the Row Delimeter as "////" and Column delimeter as "/".

Now connect your OleDb Source to the OleDb Connection manager and mention the tables which you want to access.

Now map the columns appropriately in the Flat File Destination and execute the package.

The results are coming exactly in my machine. And for writing the number of characters at the end of the file, put one Script Task and write a code to open the text file and read till the end of the file and write at the end of the file and add "\".

I hope there is a way to get this done in SSIS. If I get that, will let you know.

Regards,

Prakash Srinivasan.

|||

One record followed by one record. The number of field are fixed in each row, so there are not the row delimeter.

for example,
record1field1/record1field2/record2field1/record2field2/55\

--Alex

|||Being new to this, how would you do it with a dynamically generated name? I need to generate a text file with the date as the filename and a key field from from a table daily. Thanks in advance!|||

Miles Calunod wrote:

Being new to this, how would you do it with a dynamically generated name? I need to generate a text file with the date as the filename and a key field from from a table daily. Thanks in advance!

Miles,

Put an expression on the connection string property of the connection manager that you are using to point at the destination file. This expression will get evaluated at runtime and therefore can set the connection string (i.e. the file location) to be whatever you like.

-Jamie