asked
SSIS Update based on Lookup between two different inputs
I am new to SSIS so I may be missing the obvious so bear with me. I am migrating from an existing data model to a new one. Many of the fields are the same, but some have been changed for the sake of Normalization etc. I have an input table (in the original schema) that has the following fields (only listing the relevant ones): tblStudent StudentID HSGradYear ... In the new model I have tblStudent StudentID HSGraduationTermID ... tblTerm TermID SchoolYear Semester ... Here is some Sample data so I can explain further tblStudent(original) StudentID HSGradYear 1 2005 2 2008 What I want to end up with in the new model is: tblTerm (Already have these) TermID SchoolYear Semester 1 2005 1 2 2005 2 ... 7 2008 1 8 2008 2 tblStudent (new model): StudentID HSGradTermID 1 2 2 8 Essentially, what I want to do is for each StudentID in the original model is to lookup the TermID in the new model for Semester=2 of the SchoolYear specified in the HSGradYear field in the original model and then update the new tblStudent HSGradTermID with the value. I know that I can do this with regular SQL scripts etc, but I'm trying to learn SSIS and I just don't see how to do this. Can anyone provide me with some pointers/guidance? Thanks in advance!
Paul - the model seems a little difficult to merge into. The original data only has a year and no term. Are you going to just say everything lands in term 2 for each year? I would not use SSIS for this (I know you want to learn it, just my opinion) as you will be basically building a cursor in SSIS to do the looping, inserting and updating. Otherwise, you need to build it up in data flow tasks with looping ([see Foreach Loop Container in BOL][1] - you use an
ADO.NET connection to get the data and iterate over it). Pseudo-code: Get tblStudent data for each entry read studentid and year find corresponding term in tblTerm (where semester = 2?) insert into new table repeat until you have run through tblStudent finish EDIT: I couldn't let it lie. Here is a quick example in T-SQL just so you can see that this approach - without the SSIS looping it would work so much better: /* Old Structure */ DECLARE @tblStudent_old AS TABLE (StudentId int, HSGradYear int) ; INSERT INTO @tblStudent_old (StudentId, HSGradYear) SELECT 1, 2005 UNION SELECT 2, 2008 ; /* New Structure */ DECLARE @tblTerm AS TABLE (TermID int, SchoolYear int, Semester int); INSERT @tblTerm (TermID, SchoolYear, Semester) SELECT 1,2005,1 UNION SELECT 2,2005,2 UNION SELECT 3,2006,1 UNION SELECT 4,2006,2 UNION SELECT 5,2007,1 UNION SELECT 6,2007,2 UNION SELECT 7,2008,1 UNION SELECT 8,2008,2 ; DECLARE @tblStudent AS TABLE (StudentId int, HSGraduationTermID int); /* Do the work - I assume you want to force Semester 2 for each year*/ INSERT @tblStudent (StudentId, HSGraduationTermID) SELECT TSO.StudentId, TT.TermID FROM @tblStudent_old TSO /* Join on Year and force Semester to be 2*/ INNER JOIN @tblTerm TT ON TSO.HSGradYear = TT.SchoolYear AND TT.Semester = 2 ; /* Show the results */ SELECT * FROM @tblStudent [1]:
http://msdn.microsoft.com/en-us/library/ms141724.aspx
