• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 150
  • Last Modified:

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

Experts,

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.

Thanks!
0
MIKE
Asked:
MIKE
  • 2
1 Solution
 
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.
0
 
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
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
just to remind you this would be a nightly update that I need.
0
 
awking00Commented:
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?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now