I am very new to SSIS and have following to do drop table #table-- Prepare test dataCREATE TABLE #table( [Admissions_key] bigint NOT NULL PRIMARY KEY,MRN nvarchar(10) NOT NULL,hosp_code nvarchar(10) NOT NULL,adm_datetime datetime NOT NULL,sep_datetime datetime NOT NULL,Sequence nvarchar(10) NOT NULL,GroupID nvarchar(10) NOT NULL,IsGroupCorrect nvarchar(10) NOT NULL,RowNum nvarchar(10) NOT NULL)SET DATEFORMAT DMYINSERT INTO #table(Admissions_key,MRN,hosp_code,adm_datetime,sep_datetime,Sequence,GroupID,IsGroupCorrect,RowNum)VALUES(7099250,'999301','3','14/08/2011 15:38','14/08/2011 23:00','Middle','7099250','E','1'),(7099251,'999301','3','14/08/2011 20:00','15/08/2011 08:00','First','7099251','C','1'),(7099252,'999301','3','15/08/2011 08:01','15/08/2011 21:00','Middle','7099251','C','2'),(7099253,'999301','3','15/08/2011 21:01','16/08/2011 02:00','Final','7099251','C','3'),(7099254,'999302','3','16/08/2011 11:28','16/08/2011 18:00','Final','7099254','E','1'),(7099255,'999302','3','16/08/2011 17:00','16/08/2011 19:00','First','7099255','C','1'),(7099256,'999302','3','16/08/2011 19:01','16/08/2011 23:00','Middle','7099255','C','2'),(7099257,'999302','3','16/08/2011 23:01','17/08/2011 01:00','Final','7099255','C','3'),(7099258,'999333','3','18/08/2011 15:38','19/08/2011 22:00','First','7099258','C','1'),(7099259,'999333','3','19/08/2011 22:01','19/08/2011 23:00','Middle','7099258','C','2'),(7099260,'999333','3','19/08/2011 23:01','20/08/2011 03:00','Final','7099258','C','3'),(7099261,'999333','3','19/08/2011 23:01','20/08/2011 03:00','Final','7099261','E','1'),(7099262,'999333','3','20/08/2011 01:00','20/08/2011 08:00','First','7099262','E','1'),(7099263,'999333','3','20/08/2011 01:00','20/08/2011 08:00','First','7099263','C','1'),(7099264,'999333','3','20/08/2011 08:01','21/08/2011 01:00','Middle','7099263','C','2'),(7099265,'999333','3','21/08/2011 01:01','21/08/2011 22:00','Final','7099263','C','3'),(7099266,'999333','3','22/08/2011 23:01','23/08/2011 02:00','Single','7099266','C','1'),(7099267,'999333','3','22/08/2011 23:01','23/08/2011 02:00','Single','7099267','E','1')Select * from #table
This is output of my source in OLEDB source in result of sql command. I want to select each group and create a new record in another table.I have following conditions to meet initially. 1. Records will only be created for those recrods if the group has 'C' in IsgroupCorrected. 2. Same records are already present in that table as well, so I need to mark those already presented records as 'Grouped' in one of the column. 3. when creating new record I need to put adm_date from first and sep date from final in case where there are first and final, other case is only single so will be copying same 4. I also need to update and calculate many other fields on the basis of this group in newly created record. Which approach I should follow, what would be control flow of my task ?Umar Memon
Create and updating a record based on groups
Posted - 2012-03-22 : 03:24:51