CODEWITHSUNDEEP
sql-serverssis
Graeme
Graeme

Reputation: 1667

Copying Data Between SQL Servers with SSIS

I need to copy 3000 odd rows from one SQL Server instance to another.

The destination has existing data which is used by a live website.

Using the Data Flow in SSIS, a Transfer has been inserted which grabs the data from the source using a SQL View and then imports it into the destination table. This ensures the data is kept up-to-date on our external site.

What I'm wanting to achieve is that if for whatever reason the SSIS fails, the destination table is not left empty, hence breaking our external site's functionality.

Since only one task is being used in the package, will SSIS make this atomic? i.e. if it fails while copying the data, it won't leave the destination table either empty or partially complete?

Update: Thanks to those who pointed out the "Replace" didn't make sense - that was from a previous attempt we had where we used the Copy Object function instead.

Currently with the Copy, it's putting in the 3000 rows, but then the next time it runs, it puts in the same 3000 rows again! What I need to happen is to have the following

What is the best way of doing this in SSIS?

Upvotes: 0

Views: 2832

Answers (3)

Raj More
Raj More

Reputation: 48016

Instead of a kill-and-fill strategy, consider merging the data.

  • Lets called your original table BASE
  • Create a STG table with the same column set as the original
  • Truncate and import into STG
  • Update BASE with any new data from STG (join by business key)
  • Insert into BASE from STG where not exists

This method has the advantage of having zero down-time and seamless integration.

Upvotes: 0

DOK
DOK

Reputation: 32841

It sounds like you're looking for a transaction, which will rollback (undo) if anything fails along the way.

It's all or nothing.

SSIS handles transactions quite nicely.

This article describes a situation similar to yours, wherein it starts off by deleting some rows.

One important step there is:

The TransactionOption property exists at the package level, container level (e.g. For Loop, Foreach Loop, Sequence, etc.), as well as just about any Control Flow task (e.g. Execute SQL task, Data Flow task, etc.). TransactionOption can be set to one of the following:

* Required - if a transaction exists join it else start a new one
* Supported - if a transaction exists join it (this is the default)
* NotSupported - do not join an existing transaction

Upvotes: 1

Beth
Beth

Reputation: 9607

I've had a single task fail (pk violation, for example) and the copied rows do not get rolled back, I have to delete them before re-starting the task.

You should test it.

I don't know what you mean by 'replace,' either.

you're replacing existing rows? updating existing rows?

Upvotes: 0

Related Questions