April 23, 2012 at 3:17 pm
Thanks in advance for your suggestion!!
My Question is i need to load the data from one table to my destination table regularly and i need only to add the newly added rows at source to my destination regularly ..so i like to hear from u all with any advice ..on this topic ..
April 23, 2012 at 3:34 pm
Since you're talking SSIS is it safe to assume the source and destination are on different instances?
Proceeding with that assumption...
How big are the tables? If the source is of non-trivial size then you'll want to keep track of the last row transferred to the destination so you can pickup new or updated rows to send to the destination. Sometimes this can be a last updated date, or an identity column.
Without more information it is impossible to say what might be most effective for you. If you post the DDL of the source and destination, and some information about the data (e.g. is there an identity column in the source that is transferred to the destination? how can you tell if a row in the source should be transferred to the destination) I am confident targeted recommendations can be made.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 23, 2012 at 4:55 pm
Have you looked at Merge? http://msdn.microsoft.com/en-us/library/bb522522%28v=sql.105%29.aspx
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 23, 2012 at 11:45 pm
RamSteve (4/23/2012)
Thanks in advance for your suggestion!!My Question is i need to load the data from one table to my destination table regularly and i need only to add the newly added rows at source to my destination regularly ..so i like to hear from u all with any advice ..on this topic ..
You need something to track the changes.
If the source is SQL Server (which you didn't share with us), you can use CDC, Change Tracking or just simple created/updated datetime columns.
To make the SSIS package run regularly, you can use SQL Server Agent to schedule it.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 25, 2012 at 4:12 am
You can use lookup in dataflow task in SSIS. After lookup, use conditional split for either updation/insertion in destination table. But in order to achieve this, you should have a primary key in your table defination for lookup to work.
April 25, 2012 at 4:59 am
abhinav_kapoor (4/25/2012)
You can use lookup in dataflow task in SSIS. After lookup, use conditional split for either updation/insertion in destination table. But in order to achieve this, you should have a primary key in your table defination for lookup to work.
How will a Lookup component discover the newly added rows in the source?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 25, 2012 at 5:32 am
Fetch PK from source. Use PK as we need a distinct value for each row for Lookup in the destination table. In Lookup task, define ur destination and use PK from source to fetch data from destination. After lookup, use conditional split. Use isnull in condition to check if data is coming for that particular row. If data is not coming (that means new rows), use OLE DB Destination to insert. Otherwise use OLE DB Command to update.
April 25, 2012 at 5:40 am
abhinav_kapoor (4/25/2012)
Fetch PK from source. Use PK as we need a distinct value for each row for Lookup in the destination table. In Lookup task, define ur destination and use PK from source to fetch data from destination. After lookup, use conditional split. Use isnull in condition to check if data is coming for that particular row. If data is not coming (that means new rows), use OLE DB Destination to insert. Otherwise use OLE DB Command to update.
Yes, that will find you all inserted and updated rows.
But you'll be reading ALL the rows from the source. You need to find a way to retrieve only new rows from the source, otherwise you won't be doing a delta load.
ps: ole db command == horrible performance
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 25, 2012 at 5:53 am
Yeah, it's a work around. Another option can be use a flag in source table like boolean 0/1 or char T/F etc. Use the flag to determine new rows. But in this case also, u need to read the whole table.
PS: thanks for the information. new to DB and SSIS 🙂
April 25, 2012 at 8:02 am
Thanks every one for the suggestions and from all above suggestions can we only insert newly added rows with out the updated rows ?. I do have one identity column with primary key on source table ...
April 25, 2012 at 8:11 am
Is the identity column present in the destination? If so you could start your package with an Execute SQL Task to find the MAX value in the destination. Then, using that number select all rows in the source with an identity greater than the max from the destination and those will be the rows you transfer.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply