SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
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
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kumarm111
Starting Member

1 Posts

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

40 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

visakh16
Very Important crosS Applying yaK Herder

India
52249 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
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.06 seconds. Powered By: Snitz Forums 2000