Method for Archiving Rows

I need to archive some rows from a production database. Any ideas on good
principles to follow ? I would absolutlely want to ensure that each row is
properly archived prior to deleting it from the production table. Anyone
have ideas that they used and wish to share ?
ThanksRob wrote:
> I need to archive some rows from a production database. Any ideas on go
od
> principles to follow ? I would absolutlely want to ensure that each row
is
> properly archived prior to deleting it from the production table. Anyone
> have ideas that they used and wish to share ?
Do the archive process during "off-hours."
Wrap the append (to the archive) and delete (from the production db)
statements in BEGIN TRANS and COMMIT. Use ROLLBACK if an error occurs.
See the BOL for more info.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)|||Thanks for responding...
Something I was thinking about was linking the key from the "archive db" to
the Key to the "poduction db" and updating a flag on the prodcution. This
would ensure that the row existed in the Archive prior to deleting from
production.
"MGFoster" <me@.privacy.com> wrote in message
news:0F1Yd.6545$cN6.2489@.newsread1.news.pas.earthlink.net...
> Rob wrote:
> Do the archive process during "off-hours."
> Wrap the append (to the archive) and delete (from the production db)
> statements in BEGIN TRANS and COMMIT. Use ROLLBACK if an error occurs.
> See the BOL for more info.
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)|||Rob wrote:
> Thanks for responding...
> Something I was thinking about was linking the key from the "archive db" t
o
> the Key to the "poduction db" and updating a flag on the prodcution. This
> would ensure that the row existed in the Archive prior to deleting from
> production.
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
You could update that flag after the archive process runs, but, again,
that can be done inside the transaction. You don't need to "link"
anything between the archive & production. IOW, the production table(s)
would have an "Archived" column. The archive process would only select
rows that do not have the Archived column set to true.
An example of the process:
begin tran
insert into archive_table (<column list> )
select <column list>
from table_name
where archived = 0 -- False will be zero & True will be 1
-- the default for archived should be zero
if @.@.error <> 0 goto err_
update table_name
set archived = 1
where archived = 0
if @.@.error <> 0 goto err_
commit
goto exit_
err_:
rollback
exit_:
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQjCzOYechKqOuFEgEQJu+gCePIIvgGgSjDh3
9lQY0uy3JVrybfYAoJTV
xPUUKn7k08dcCAr42/VJI7zf
=pp/t
--END PGP SIGNATURE--|||I just copy the records to the ARchive DB and then my purge solution does an
inner join to the Archive table (Or WHERE Exists()) to ensure existence
prior to deletion from prod.
works like a champ as long as proper indexes are in place, etc.
GAJ

Merging, and loading dynamic flat file.

I have number of csv files in a folder, all of them with same columns, need to be merged into one table and imported to sql server.

-The first row of the csv file is a header.
-The csv files are updated everyday
-The destination table is replace by new table with new info in the csv.
-The new csv files can be created and old csv files may no longer exist, but we are only interested in information contain in current csv files in the folder.
-I need SSIS to combine all the csv files in the folder and merge into one table.
-Other issue is that the field names may change in csv, so can the SSIS package recognize the change in field name and made necessary change in destination table as well.

Any insight on this issues will be greatly appreciated?

Put simply this is not a scenario that SSIS supports. The key problem is the dynamic nature of the file structure, which SSIS has no run-time support for. The target scenarios for SSIS revolve around unattended/automated data transfer, and any kind of dynamic format is just never going to work well with SSIS.

What use would a new table be that the system has never seen before? For you, you obviously then interpret this, but unless you tell SSIS to do that up front, it would never make sense for it.