Hi, I have 10 tables each corresponding to patients list in each ward in a hospital with column names patient name and status At the end of each day a text file with details regarding the patients who got discharged on that day is produced We have to compare the patients list in this 10 tables with text file one by one and the status of the patients who got discharged on that day have to be changed to discharged
How can we implement this using packages in ssis 2008 Waiting for quick reply Thanks in advance.
first step is to truncate staging table required to store patient data (table has to created before hand outside package) next setp would be a data flow task which will transfer data from file to staging table truncated in last step this would be followed by a for each loop which will have list of tables (your 10 tables defined on it). You need to define a variable to take value of table for each iteration inside. (say User::IterationValue) Create another variable to form dynamic update query using variable value (User::UpdateQuery). the variable will be made dynamic by setting EvaluateAsExpression property true in variable properties and expression will be like = "UPDATE t SET t.Status = 'Discharged' FROM " + @[User::IterationValue] + "t INNER JOIN Staging s ON s.patientname = t.patientname"
then add a execute sql task inside for each loop, make sql commandtype as variable and in dropdown choose above variable (User::UpdateQuery)
On running packages, you'll get all 10 tables updated based on data in the file
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
how to update 10 tables by comparing it with a tex
Posted - 03/09/2013 : 13:41:50
