March 22, 2012 at 11:00 pm
I am very new to SSIS and have following to do
drop table #table
-- Prepare test data
CREATE 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 DMY
INSERT 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.
so I am using conditional spilit to get only those records which have 'C'
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.
how can I do this ?
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 .
I also need to update and calculate many other fields on the basis of this group based on the records present in group in newly created record.
for example there are three records in a group, I need to put some thing from first, some thing from middle and some thing from final.
how can I do this ?
Which approach I should follow, what would be control flow of my task after conditional Spilit.
March 23, 2012 at 12:33 pm
I have following conditions to meet initially.
1. Records will only be created for those recrods if the group has 'C' in IsgroupCorrected.
so I am using conditional spilit to get only those records which have 'C'
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.
how can I do this ?
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
1. Conditional split is the correct approach
2. I would take the output of the conditional split and do a look up against the destination table where you route records that don't match to another output. You will end up with 2 data flows out of this lookup, one where a record already exists, and another where it does not exist. You can then add a derived column for your "Grouped" column or just use the data appropriately in your destination.
3. I don't really understand this point.
March 24, 2012 at 4:48 am
I still have problem in implementing 2, can you elaborate it in more detail. Stil lI am confused how I am going to create a single record from the group in which there are 2,3,4 records.
point no.
3: let us take an example of 1 group where there are 2 records. . if there are 2 records in 1 group, than the record we are creating will contain adm_date from 1st record and sep date from 2nd record.
adm_date and sep_date are two columns in records,
i hope it makes more clearer to you now.
will b waiting for your reply.
March 24, 2012 at 7:37 am
Thank you for your reply, I solved the probelm,
1. conditional spilit
2. Used OLEDB command to update bundled
3. Used aggregate to group records, use max and min functions to get dates
4. used drived column to update other columns
5. used destination to store it.
thanks for your reply.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply