My query is related to the answers i got for questions i had posted earlier in the same forum. I have a copy of a client database which is attached to SQL Server where the Central Database exists. The copy already contains the updated data. I just want to transfer the updates from that copy to Central Database both holding same schema and are present in the same server using C# .NET. One of the solution i got was to create SSIS package and run it from the UI. I want to know as how i can create SSIS Package to achieve this. I am new to SSIS. I am using SQL Server 2005 Standard Edition and Visual Studio 2008 SP1 installed. I learnt that BIDS 2005 is used to create packages which comes by default with SQL Server 2005. Can someone please give me a example as i am new to this.
views:
500answers:
2If you are merely updating data in a table from data in another table, I would say SSIS is overkill for this. I would create a proc that your .NET package can call which would simplify the process and ease the learning curve.
There is no easy way to do this in SSIS that I know of. For each table, you would have to write a proc which can be called in SSIS using the Execute SQL Taske. For example:
src table
SSN First_Name Last_Name
11111111 Jeff Williams
22222222 Sara Jenkins
33333333 George Anderson
dest Table
SSN First_Name Last_Name
11111111 Jeff Williams
22222222 Sara Flowers
55555555 Jessica Billows
Proc:
INSERT INTO dest(SSN, First_name, Last_Name)
SELECT s.SSN,
s.first_name,
s.last_name
FROM @src s
LEFT JOIN @dest d
ON d.SSN = s.SSN
WHERE d.SSN IS NULL
UPDATE dest
SET dest.First_Name = src.First_Name,
dest.Last_Name = src.Last_Name
FROM dest
JOIN src
ON src.SSN = dest.SSN
Producing this type of logic could prove to be a little tedious. You could use a handy tool like red gates sql compare to accomplish this a lot quicker: http://www.red-gate.com/products/SQL_Data_Compare/index.htm
Good Luck!