Using SSIS to move (not copy!) between to SQL Server instances

Hello,

Is it possible to use SSIS 2005 to move (not copy!) data  between to SQL Server instances?

I am considering to use this technique to archive some large tables from my OLTP DB.

Note: the transfer should be transactional.

Thanks,

PK
dgbAsked:
Who is Participating?
 
CboudrozConnect With a Mentor Commented:
To have transactional operation in SSIS you can used Container to put many task and put the TransactionOption property of the container to "Required".

used this to move the row :

DELETE dbo.LiveTable
OUTPUT DELETED.*
INTO LINKSERVER.DATABASE.dbo.ArchivesTable
FROM dbo.LiveTable WITH (TABLOCKX)



everything is explain there:
http://www.mssqltips.com/tip.asp?tip=1585
0
 
radcaesarCommented:
Copy the data and then truncate the source table in the next step if the copy was successful.
0
 
dgbAuthor Commented:
radcaesar,

I don't want to move all the data in the table, only a part of it. A T-SQL should decide which rows should be moved.

By the way what if the copy was only partly successful? Will a rollback take place?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
AmmarRCommented:
Dear dgb:

There is no command in SQL server that will move Data from a table to the other

what radcaesar: suggested is right

you need to copy the data you want e.g

Insert into DestTable
Select * from sourcetable
where a = ? --what ever condition

then
do th following

Delete from sourcetable where a = ?

this will in other words move.
-------

and by the way this is the move concept even in windows
its a copy and then delete of source..

0
 
subhashpuniaCommented:
You can move data from one SQL server to another by two methods:
1. Using four part naming. It would require linked server.
2. SSIS pkag.

As for simple SQL to SQL data tranfer, no ssis specific feature is used so I would prefer to use four part naming method to avoid unnecessary deployment, debugging complexity of SSIS.  
0
 
dgbAuthor Commented:
Exactly what I was looking for.
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.

All Courses

From novice to tech pro — start learning today.