October 22, 2012 at 8:07 am
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
October 22, 2012 at 8:08 am
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
October 22, 2012 at 8:10 am
Where does the target file name come from?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 22, 2012 at 9:21 am
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
October 22, 2012 at 9:32 am
asita (10/22/2012)
hi philthanks 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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 22, 2012 at 9:53 am
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
October 22, 2012 at 10:48 am
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 22, 2012 at 1:01 pm
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
October 22, 2012 at 1:17 pm
That's fine, glad to help. Good look with the automation.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply