how to produce two text files one with path and second is actual content from the sql server table

Question

how to produce two text files one with path and second is actual content from the sql server table

asita

SSCarpal Tunnel

Points: 4144
More actions
October 22, 2012 at 8:07 am

#283876

Good Evening,
please help me with your valuable ideas,
i have a table with 9873 rows in it, 10 columns in a table, all columns were having datatype as nvarchar(255) except a column called EmailBody which is nvarchar(max), it need not be what it is now, it can be changeable (if any issues) if it require in some ssis transformations.
the source could be a sql server table, or i can create a textfile from the table for source feed to ssis package
1)each EmailBody column value will be saved into a text file and save to disk
2) a text file with all rows in the source/table with pointer (path) to step 1 created file
so to be clear, if i have 5 rows , i will end up with 5 email body text files (column always have values will be never empty) and then another text file (masterfile) with all rows info and each row will have pointer to above created 5 files.
for example
expected outcome in master file will be as below (instead email body column it wlill have email body extracted and saved to txt path) as in first step
123,"king@gbh.com","virat@live.com","Ctire@lie.co","jan-12-2012","jbh office","king","yes","5","C:\ssis\emailbody\1726366.txt"
123,"king@gbh.com","virat@live.com","Ctire@lie.co","feb-23-2012","jbh office","king","yes","4","C:\ssis\emailbody\1726367.txt"
124,"bong@ltd.com","singa@sim.com","NULL","mar-1-2012","bbk client","ginj","no","3","C:\ssis\emailbody\1726368.txt"
134,"jack@king.com","jaybol@jay.com","NULL","apr-12-2012","horiz","king no","4","C:\ssis\emailbody\1726369.txt"
127,"pck@jij.com","jinh@kin.co","bing@jik.com","jun-8-2012","ventura","linh","yes","2","C:\ssis\emailbody\1726370.txt"
please share your valuable ideas with me,
please again, just did derived column and with export column, i am getting outcome of text file per row, but dont know how to start for master file
or let me know is there any better ways that what i am doing now
Thanks in advance
asitti

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

asita SSCarpal Tunnel Points: 4144 More actions · Answer 1

another way i thought

1) all source is in text file, so loop through each row and extract emailbody column then create text file, then have the location where it saved in a variable or so, then update this to master file as last column.

could any body tell me can i do this, is it possible with script task

thanks

best regards

asitti

Phil Parkin SSC Guru Points: 246985 More actions · Answer 2

Where does the target file name come from?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

asita SSCarpal Tunnel Points: 4144 More actions · Answer 3

hi phil

thanks for your response,

just assume

it is a variable or just static d:\ssis\master.txt is the file to update path to each row

and then D:\ssis\individuals is for all body generated files to store

Phil Parkin SSC Guru Points: 246985 More actions · Answer 4

asita (10/22/2012)
hi phil
thanks for your response,
just assume
it is a variable or just static d:\ssis\master.txt is the file to update path to each row
and then D:\ssis\individuals is for all body generated files to store

Thank you. What I was thinking is that you could perhaps use the primary key of the source data as the file name (easy if it's an Int, for example). If you do this, you do not need to worry about creating 'pointers' - they're already there in the data and the two files can be produced independently.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

asita SSCarpal Tunnel Points: 4144 More actions · Answer 5

Thanks phil,

i am also on the same way, i added pk (identity column name rowID1) and i can generate another column with "D:\ssis\" + rowID1 + ".txt"

then here my recent problem is,

i want to generate each emailbody + subject both columns data to a separate text file (this will generate above place with rowid1 as the name

so to shorten my question how can i combine 2 columns and create a text file for each row

Thanks

asittti

Phil Parkin SSC Guru Points: 246985 More actions · Answer 6

asita (10/22/2012)
Thanks phil,
i am also on the same way, i added pk (identity column name rowID1) and i can generate another column with "D:\ssis\" + rowID1 + ".txt"
then here my recent problem is,
i want to generate each emailbody + subject both columns data to a separate text file (this will generate above place with rowid1 as the name
so to shorten my question how can i combine 2 columns and create a text file for each row
Thanks
asittti

Use a Foreach loop to create a text file per row. See here[/url] for a worked example.

I do not suggest having a column containing the d:\ssis\... path. Rather, create a variable in SSIS called OutputFilePath (or whatever) to hold the folder in which the files are going to be created (eg, d:\ssis\). Within the package dataflow, a derived column can easily combine this variable with the PK of the row currently being processed.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

asita SSCarpal Tunnel Points: 4144 More actions · Answer 7

Thanks phill

right now it is working based on what you said,

but let me come back, this is kindof manual process for now, it will be changed to full automatic

back to you phil

i greatful to you

god bless you alll

Phil Parkin SSC Guru Points: 246985 More actions · Answer 8

That's fine, glad to help. Good look with the automation.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel