Best way to DTS

Does anyone know of a better way I could go about doing running a DTS
package where I basically delete all the data from the SQL server and
then move (re-populate) it with data from our Navision (ERP) database?
Right now I have connections which connect to each other through
connections. The first deletes the data from the tables, the next 40 or
so move the data from our ERP database back to the SQL server. The
reason we do so is because we cannot figure out a way how to move only
the data that has changed in the ERP database to to the SQL server, so
instead we just erase the SQL server every night and copy all the data
from the ERP database into it so we know we are getting current data
once a day. The main reason I need help is because this process is
taking 5+ hours now and has grown with the amount of data we have input
over time. I would really appreciate ANY input on my situation, it
would help tremendously.If you don't know how to identify changed data only, maybe SQL Server can
help you - do please check basic Replication topics in Books OnLine if this
is what you need. Also, as a quick solution - backup of the database on
production server and restore on standby server should be much faster than
deleting and inserting all of the data.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
<barhoc11@.yahoo.com> wrote in message
news:1124116978.660087.4840@.g47g2000cwa.googlegroups.com...
> Does anyone know of a better way I could go about doing running a DTS
> package where I basically delete all the data from the SQL server and
> then move (re-populate) it with data from our Navision (ERP) database?
> Right now I have connections which connect to each other through
> connections. The first deletes the data from the tables, the next 40 or
> so move the data from our ERP database back to the SQL server. The
> reason we do so is because we cannot figure out a way how to move only
> the data that has changed in the ERP database to to the SQL server, so
> instead we just erase the SQL server every night and copy all the data
> from the ERP database into it so we know we are getting current data
> once a day. The main reason I need help is because this process is
> taking 5+ hours now and has grown with the amount of data we have input
> over time. I would really appreciate ANY input on my situation, it
> would help tremendously.
>|||Thanks so much Dejan for the quick response I just have a couple
questions for you or anyone who has an answer, if you have the time to
answer I would greatful.
Is this possible to replicate a Navision(ERP) database to a SQL server?
Can you explain the quick solution you mentioned...- would this include
dumping the data to a temporary database then inserting it in the SQL
server database?|||> Is this possible to replicate a Navision(ERP) database to a SQL server?
Uh-uh...
AFAIK Navision has two possibilities: to use it's native db or to use SQL
Server as RDBMS. I guessed you have SQL Server version of Navision. This way
replication would be quite simple. But from your answer I infer you use
native Navision db. If this is true, then you would have to write custom
Publisher in order to use the replication, and I guess this is not an option
for you. Also, it is not possible to restore backup of Navision native db to
SQL Server. So I guess your best option is to find which rows have changed
and create more efficient DTS packages. Maybe you can ask in some Navision
group how to find only changed rows?
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com