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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 how to update 10 tables by comparing it with a tex
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

1 Posts

Posted - 03/09/2013 :  13:41:50  Show Profile  Reply with Quote
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.

Starting Member

47 Posts

Posted - 03/12/2013 :  08:23:10  Show Profile  Reply with Quote
What is the format of the text file? Can you import it into a table? Then a stored procedure can perform the updates.
Go to Top of Page

Very Important crosS Applying yaK Herder

52326 Posts

Posted - 03/12/2013 :  13:51:40  Show Profile  Reply with Quote
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

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000