how to update 10 tables by comparing it with a tex

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

how to update 10 tables by comparing it with a tex

Author

Topic

kumarm111
Starting Member

1 Post

Posted - 2013-03-09 : 13:41:50

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.

Mar
Starting Member

47 Posts

Posted - 2013-03-12 : 08:23:10

What is the format of the text file? Can you import it into a table? Then a stored procedure can perform the updates.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-12 : 13:51:40

you can

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources