0

Sorry if this questioned was asked before, but i am posting it, since was not able to find anything related in forum. So any help would be appreciated. Here is my scenario.

We have an SQL table view A (just read access) with set of data located on Server A on domain A. We have our SQL database with table B located on Server B on Domain B. We have got the port opened up between 2 domains.

We need to write a SSIS job (runs per day) which will get the data from View A and insert in Table B. So what could be the best way for achieving this.

Note:

  • table View on server doesn't have deleted or last modified columns So wont be able to figure out what are the changes. (if there is any other way please do let me know)
  • Database data is around 50k records

Approach 1:

  • Export data from View
  • Transform it
  • Drop table on Server B
  • Insert the transformed data

Approach 2:

  • Export data from view
  • Transform it
  • Insert into a temp table
  • Make a log entry into log table
  • When the log entry says successfully copied, trigger to delete all the rows and copy the data from temp table to Table B.
  • If something went wrong, rollback.
Nikunj
  • 556
  • 2
  • 10

2 Answers2

0

I recommend a third approach applying a new staging table and history table in Database B on Server B. The staging table will mostly mirror your Table B, but will not contain any constraints and will have an additional bit column defining Status. The history table will mostly mirror the Table B structure but will contain two additional columns (ChangeDate and ChangeMade). Lastly, for this approach you will need to identify the column(s) used to define the records from the View as unique.

  • Truncate Staging table (from previous results).
  • Export data from View (Server A) to staging table (Server B).
  • Run SQL task to check for consistency and quality of each record. Those records that pass, set the Status bit field value to 1, otherwise set to 0.
  • Apply a MERGE call in a SQL Task to both transform the data from the source (Staging table) to the target table (Table B) and update the History table. This is only applied for those records that have a Status = 1. With the MERGE you can also OUTPUT the history of what was changed to the new History table indicating "I" for insert, "U" for update or "D" for delete based on the matching defined in the MERGE.
  • For those records in the Staging table with a value of 0, send an email to whoever needs to know that X number of problem records were found.

The idea here is that the process doesn't come to a halt and you do not need to force a rollback if a single bad record is found. Also, you can monitor the daily process by viewing the Staging table. I've taken this approach in the past and integrated into it sending email alerts with links to SSRS reports reporting on the problem records. Doing so allowed me to proactively find patterns in the problem records and work with those upstream of me to resolve the problems. If you have over a million records being pulled by the view, then you may want to add to the Staging table a surrogate key (set up as a primary) with an identity starting at 1 and that automatically increments by 1 with each new record imported from the view. Just before running the MERGE, set up a clustered index using the surrogate key. This will greatly improve the performance of the MERGE. Just before truncating Staging table in the first step, drop the index.

Hope this helps.

user3662215
  • 526
  • 4
  • 12
  • Thanks for the response I liked your idea but can you please explain bit more on truncation, n on sql task. – Nikunj Apr 25 '15 at 20:58
  • 1
    The first step or task in the SSIS package would be to truncate the Staging table (removing all records from the table and resetting any identities). Then repopulate the Staging table with the current records from the view. Instead of using an SSIS package, this can all be easily done in a stored procedure. Applying a Staging table and a History table with the MERGE provides you with the means to identify bad records and track historical changes. – user3662215 Apr 27 '15 at 16:55
  • The following link provides a good description of a staging table: [Staging table link](http://stackoverflow.com/questions/29317162/what-is-a-staging-table) – user3662215 Apr 27 '15 at 16:56
  • 1
    The following link provides a good description of a MERGE statement: [MERGE link] (http://www.purplefrogsystems.com/blog/2011/12/introduction-to-t-sql-merge-basics/) – user3662215 Apr 27 '15 at 17:00
  • Lastly, the following link shows how to apply the MERGE with the OUTPUT clause for sending information to the History table tracking when records where inserted, updated and deleted: [MERGE with OUTPUT link](http://sqlblog.com/blogs/rob_farley/archive/2012/06/12/merge-gives-better-output-options.aspx) – user3662215 Apr 27 '15 at 17:05
  • If any of the information helps, please mark my initial response as having answered your question. Thanks. – user3662215 Apr 27 '15 at 17:07
0

Simply you can achieve that by creating a SQL stored procedure that has three statements - insert -- for inserting new records from A to B -Update -- to update changes happened in records copied before as job will run daily -delete -- to delete any deleted records in B if any records were deleted in A

if you need any clarification about this solution I'll be glad

Ahmed Fayed
  • 141
  • 2
  • 10
  • Thanks for reply but how do you track if the record has been updated deleted, note I don't have any flags on original view – Nikunj Apr 25 '15 at 20:56
  • You just need (1)in update you make inner join between A and B so that you get the common records and then update for example – Ahmed Fayed Apr 25 '15 at 21:49
  • --(2) in insert you make left join between A and B where A is the left side and B is the right side and you insert just where the ID in B is null which mean you insert new records only .for example : ( insert into B(col1,col2,etc) select (A.col1,A.col2,etc) from A left outer join B.ID on A.id = b.id where B is null --- – Ahmed Fayed Apr 25 '15 at 21:55
  • (3)in delete you will also make a left outer join statement between B and A but this time B with be the left side and check the records where A.id is null which means those records where exist but deleted for example [delete from B left outer join A on A.id =B.id where A.id is null . If you need any more clarification I'll be glad – Ahmed Fayed Apr 25 '15 at 21:55