| Author |
move records for archiving
|
|
| John230873 2006-01-21, 3:23 am |
| Is there a SSIS componet that can move data from one table to another (not
copy but move)?
| |
| Allan Mitchell 2006-01-21, 7:23 am |
| 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)?
>
| |
| John230873 2006-01-21, 8:23 pm |
| 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
>
>
>
>
| |
| Allan Mitchell 2006-01-21, 8:23 pm |
| 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/...br />
umns.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
[color=darkred]
> 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:
>
| |
| John230873 2006-01-21, 8:23 pm |
| Thanks Allan, even if it is not want I want at least it is another source of
informaiton on the SSIS
"Allan Mitchell" wrote:
> 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/...br />
umns.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
>
>
>
>
>
>
>
| |
| John230873 2006-01-22, 8:23 pm |
| 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:
> 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/...br />
umns.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
>
>
>
>
>
>
>
|
|
|
|