SSIS - How to Create Package to INSERT and/or UPDATE a table's Columns if they CHANGE?


I need basic information on how to create a SSIS Package that will load data into a  table on a Daily basis.

1. Source = DB1.Table1
2. Destination = DB2.Table2
3. Need to load all NEW Records, which are records NOT already in Table2, but are in Table1
3. Also need to UPDATE any changes to certain columns in Table2 for any changes to same records in Table1

In a nutshell, I need to know the basic Tasks / Transform Items to use for a "general" SSIS Package like this.

Please ask me any questions you may have, but I'm looking for basic information on how to accomplish this and what to look-out for in the development of this package.

LVL 17
MIKESoftware Solutions ConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>3. Need to load all NEW Records, which are records NOT already in Table2, but are in Table1
'Full Load' approach would be to have Table1 as a Source, then a Lookup that has Table2, joined by whatever columns relate the two tables, and the 'No Match' precedence costraint goes to a Table2 destination to insert rows.

'Incremental' approach, assuming that Table2 is never edited outside of this load from Table1, is to store the last time the package was ran somewhere (variable, in a table, on a boat, with a goat, whatever), then in your Table1 source add a WHERE clause that does a 'WHERE the_date_time_stamp > that_datetime_variable'.  

>3. Also need to UPDATE any changes to certain columns in Table2 for any changes to same records in Table1
Is there a single column in Table1 that is a 'date modified?  

If yes, then adding to the above 'full load' approach the 'Match' precedence constraint goes to a Table 2 bulk insert task, again joined by whatever columns relate to tables. I use the Pragmatic Works Task Factory bulk insert task.

If no, then the above lookup is going to have to add a second lookup that JOINS on every non-key column, and a 'No match' out of THAT one (matches PK's + does not match all other columns) results in a bulk insert destination.  To be avoided at all costs, especially if the number of columns is large.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MIKESoftware Solutions ConsultantAuthor Commented:
thank you for your help and input this table is updated outside of this SSI S package it's constantly updated throughout the day that's table 2.

what I really need is just to insert all new records based on an account ID and then update any of the columns  in the table2 that have any changed data from table one

with this in mind can you please again give me a high-level summary of which tasks or objects to pull onto the SSI S package and then how to configure them just briefly thank you
MIKESoftware Solutions ConsultantAuthor Commented:
just to remind you this would be a nightly update that I need.
awking00Information Technology SpecialistCommented:
I think you might want to look at the merge statement. Can you provide some sample data and what you're expected results should be?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.