December 22, 2009 at 1:19 pm
I have a table in a non-MSSQL database that tracks transactions. Everytime someone does a transaction, it creates a new record.
I want to bring this data into a table in SQL 2005.
I figured out how to use SSIS to basically bring in the whole table via ODBC and dump it into SSIS.
But since all the old records won't change, there's no reason for me to import all that data every time. Instead, I'd like a way to import just the new records (without creating duplicates or missing any).
What's the best way of doing this? I think I can do it by date (e.g. only bringing in transactions from today's date), but that seems like a blunt tool. (E.g. what if I want to synchronize it several times during the day).
I've done searching on Google and read through books, but haven't found any best practices for this situation.
Leonard
Madison, WI
December 22, 2009 at 2:51 pm
If you can modify the source table to include a column to control synchronization, that would be most excellent.
IsSynched = 1 in source, then SSIS ignores it and does anything Issynched = 0. Once it copies those that = 0, then it updates them to a 1.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 22, 2009 at 3:02 pm
There are several different ways to do this. This is just one of the ways that would work, but for your scenario, it may be what you are looking for.
You may want to build out a 'log' table in your SQL Server database to track the SSIS package run times and results. Work the table into the package flow as such:
1. First step (Control Flow): get the current system time and places into a package level variable.
2. Second step (Control Flow): query your package history table to get the last run datetime for the package and place that value into a package level variable.
3. Third step: data flow task that gets the data from your source system. The SQL code used to extract the data from the source system should get all rows that range from the last run time (from step 2) to the current system time (from step 1).
4. Fourth step (Control Flow): add an entry into the package history table with the datetime from the variable in step 1. Here you can log rows moved, results, or any other metric that you want to keep.
EDIT: This is assuming that one of the columns in your source table is a date value representing the date of the transaction.
December 23, 2009 at 9:06 am
Thank-you both for your suggestions!
@CirquedeSQLeil - Unfortunately, I don't have access to update the source table, but otherwise that would be a really elegant solution.
@john-2 - I do have a date/time value in the source table, so your solution sounds promising. I haven't worked with package level variables, so I have some reading to do, but I appreciate very much the pointer!
Leonard
Madison, WI
December 23, 2009 at 9:24 am
You're welcome
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply