move records for archiving

#1

Is there a SSIS componet that can move data from one table to another (not
copy but move)?

#2

Hello John230873,

In one component? No.

You will have to do the transfer and then delete the data.

Allan

Quote:

Is there a SSIS componet that can move data from one table to another
(not copy but move)?

#3

Can I use the loop for each componet, if I could I have not idea how, any
good reading on SSIS as I have the hot labs and the microsoft on line couse
btu I can't seem to find much examples on SSIS and how to use it.

If all this fails I think of using a cursor with a select into and delete
command.

"Allan Mitchell" wrote:

Quote:

Hello John230873,

In one component? No.

You will have to do the transfer and then delete the data.

Allan

Is there a SSIS componet that can move data from one table to another
(not copy but move)?

#4

Hello John230873,

Here's a funky solution. You have a proc which does the inserts and you
have one that does the deletes. You use an OLEDB Command Transform.

Have a look at this artice and see if it gives you any ideas.

http://wiki.sqlis.com/default.aspx/S...tyColumns.html

Of course this will be Row* Row so maybe you would like to read from a recordset
using the ForEach loop similar to

http://www.sqlis.com/default.aspx?59

Allan

Quote:

Can I use the loop for each componet, if I could I have not idea how,
any good reading on SSIS as I have the hot labs and the microsoft on
line couse btu I can't seem to find much examples on SSIS and how to
use it.

If all this fails I think of using a cursor with a select into and
delete command.

"Allan Mitchell" wrote:

Hello John230873,

In one component? No.

You will have to do the transfer and then delete the data.

Allan

Is there a SSIS componet that can move data from one table to
another (not copy but move)?

#5

Thanks Allan, even if it is not want I want at least it is another source of
informaiton on the SSIS

"Allan Mitchell" wrote:

Quote:

Hello John230873,

Here's a funky solution. You have a proc which does the inserts and you
have one that does the deletes. You use an OLEDB Command Transform.

Have a look at this artice and see if it gives you any ideas.

http://wiki.sqlis.com/default.aspx/S...tyColumns.html

Of course this will be Row* Row so maybe you would like to read from a recordset
using the ForEach loop similar to

http://www.sqlis.com/default.aspx?59

Allan

Can I use the loop for each componet, if I could I have not idea how,
any good reading on SSIS as I have the hot labs and the microsoft on
line couse btu I can't seem to find much examples on SSIS and how to
use it.

If all this fails I think of using a cursor with a select into and
delete command.

"Allan Mitchell" wrote:

Hello John230873,

In one component? No.

You will have to do the transfer and then delete the data.

Allan

Is there a SSIS componet that can move data from one table to
another (not copy but move)?

#6

I have now had some thing to look at this and see if it is want I want. It is
a very good starting point and I have leant a bit form it but it doesn't
quite fit. I require some thing that can insert and delete at row level, this
does it at record set ie insert all records then delete all records. I would
like it at row level so the records that can not be inserted do not get
deleted. I have tried a insert and delete statement in the ADO command but if
one fails it still runs the second part (I have also place them in a begin
trans, no joy). If I do have a procedure and send the values to the procedure
it will work but due to the amount of tables I need to archive I was hoping I
could write it all in the SSIS.

The other this I thought of is to run the dataflow on a select top 1 and the
dataflow could be in a foreach loop but I am unsure if I can get the foreach
loop to run once for each record in the record

Cheers for you help so far it has sent be down the right track

"Allan Mitchell" wrote:

Quote:

Hello John230873,

Here's a funky solution. You have a proc which does the inserts and you
have one that does the deletes. You use an OLEDB Command Transform.

Have a look at this artice and see if it gives you any ideas.

http://wiki.sqlis.com/default.aspx/S...tyColumns.html

Of course this will be Row* Row so maybe you would like to read from a recordset
using the ForEach loop similar to

http://www.sqlis.com/default.aspx?59

Allan

Can I use the loop for each componet, if I could I have not idea how,
any good reading on SSIS as I have the hot labs and the microsoft on
line couse btu I can't seem to find much examples on SSIS and how to
use it.

If all this fails I think of using a cursor with a select into and
delete command.

"Allan Mitchell" wrote:

Hello John230873,

In one component? No.

You will have to do the transfer and then delete the data.

Allan

Is there a SSIS componet that can move data from one table to
another (not copy but move)?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

move records for archiving

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

move records for archiving - 01-20-2006 , 09:55 PM

Re: move records for archiving - 01-21-2006 , 04:44 AM

Re: move records for archiving - 01-21-2006 , 02:08 PM

Re: move records for archiving - 01-21-2006 , 02:40 PM

Re: move records for archiving - 01-21-2006 , 03:09 PM

Re: move records for archiving - 01-22-2006 , 03:54 PM