Loading dynamic files from a table that has header and detail records

all,

i have table, say, footest has 2 columns, 1 column (say, message) has header , detail records stored , 2nd column in "status". how table structure looks like:

message                                 status

1|hdr|abc|121|qwasdf             0

2|dtl|abc|121|qwfggjg            0

3|dtl|abc|121|cvmcbnv           0

4|hdr|def|134|dghmcbq          0

5|dtl|def|134|dghmcbq           0

6|hdr|abc|173|mnxccvc           0

7|dtl|abc|173|ndsjkdnx          0

i want dynamically generate files table. so, example, when first loop runs, file named, "abc_12142014_6:10:23 (filename_date_time)" generated , status column should updated "1" , file have 3 records i.e.:

1|hdr|abc|121|qwasdf

2|dtl|abc|121|qwfggjg

3|dtl|abc|121|cvmcbnv

similarly, once, "def" file has been loaded, status column should updated 1 , file name should have date , time (including milliseconds).

also, though filename same last 2 records (i.e. abc), want 1 different file first one. filename should "abc_12142014_6:10:45" (milliseconds or minute change), but, still different file 2 records, i.e.:

6|hdr|abc|173|mnxccvc

7|dtl|abc|173|ndsjkdnx

basically each "hdr" record there should new file header , detail records specific header (doesn't matter filename (abc) same).

is there way achieve in ssis?

i have been able create package dynamically create files based on distinct file name. so, in scenario, package created 2 files (abc , edf). however, since want 2 files 'abc'', that's trying see if accomplished using sql task (may be, using charindex)? won't mind trying script task @ all, think might solution, but, trying see if sql functions can used in way achieve this.

its possible using sql

see illustration below

declare @t table ( message varchar(1000), status int ) insert @t values('1|hdr|abc|121|qwasdf',0), ('2|dtl|abc|121|qwfggjg',0), ('3|dtl|abc|121|cvmcbnv',0), ('4|hdr|def|134|dghmcbq',0), ('5|dtl|def|134|dghmcbq',0), ('6|hdr|abc|173|mnxccvc',0), ('7|dtl|abc|173|ndsjkdnx',0) select * @t t outer apply(select max(left(message,charindex('|',message+'|')-1)) x             @t 			where left(message,charindex('|',message+'|')-1) <= left(t.message,charindex('|',t.message+'|')-1) 			and substring(message,charindex('|',message+'|')+1,charindex('|',message+'|',charindex('|',message+'|'))+1) ='hdr' 			)t1            

you can query above , in ssis add loop based on field x value. inside loop can add data flow task oledbsource have sql statement filter values table based on iterated  value of x (ie in example above 1,4,6 etc ) , link flat file destination expression set filename add timestamp , abc,def etc info filename in way want

---

please mark answer if solved issue
please vote helpful if helps solve issue
visakh
----------------------------
my msdn page
my personal blog
my facebook page

SQL Server  >  SQL Server Integration Services