December 20, 2012 at 10:02 pm
Hi
I Have two sql server databases i need move data between databases using querry i need insert and update on changed values
can any specify steps for creation of the package
Thanks
with Best regards
pradeep
December 20, 2012 at 11:56 pm
What is the version of SQL Server you have?
Raunak J
December 21, 2012 at 12:03 am
If 2005:
Use a dataflow with an OLE DB source to read the data. Use a lookup on the destination table to retrieve the business key and the primary key. Do a lookup on the business key to find out if the incoming row is an update (match) or an insert (no match). SSIS 2005 doesn't have a no-match output, so you need to redirect no matches to the error output. Connect the match output to an OLE DB destination that writes the updates and the primary key of the destination table to a staging table. Connect the error output to another OLE DB destination which writes the inserts directly into the destination table.
Use an Execute SQL Task after the dataflow to do the updates. Since you know the primary key, it should be pretty fast.
If 2008/2012: Either use the same scenario as above but use the no match output now, or use the TSQL MERGE statement in an Execute SQL Task.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 21, 2012 at 12:28 am
I have slightly different requirement here.May be you people can help me out.
Initially my destination(reference) table is empty... I am trying to load data from my staging table to a Customer Dimension table(dimension table so, each customer will have only one record with their customer id, name etc )...
Here is my sample input data from staging
custName Custid Type
A XX1 Hid
A XX Did
B YY1 Hid
B YY Did
C ZZ1 Hid
And Here is my customer Dimension Table and this is empty.
So, when I look up CustNumber in both these table since there is no data in the reference table everything goes to the NO MATCH OUTPUT. Since these are new records I want to insert these records into the Destination(Ref) table.
IS there a way I can get a result in Dimension table like mentioned below ?
custName Hid Did
A XX1 xx
B YY1 yy
C ZZ1
The logic would be as follow..
1.only one record should be in dimension table
2.If type is Hid it should update the Hid column from Custid of staging and if type id Did it should update the same record in Did column with Custid.
3.Need to implment this using Look up only.
December 21, 2012 at 1:00 am
Why didn't you start your own thread instead of hijacking an existing one with different requirements?
Check out the pivot transformation, it suits your requirement nicely.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 21, 2012 at 2:16 am
Koen Verbeeck (12/21/2012)
Why didn't you start your own thread instead of hijacking an existing one with different requirements?Check out the pivot transformation, it suits your requirement nicely.
Atually I posted the same here but did not get the proper reply.
Well do you think pivot will work over here as far as I know pivot is for cross tab but here I need to process duplicate(two records for a customer)records using upsert into the destination table.
December 21, 2012 at 2:19 am
With the pivot you make your two records into one single record, which you can feed into an update statement.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply