How to schedule a daily job to move data from a 2005 server to a 2008 server
Hi, All, I am looking for some lights on how I could achieve my task. Source server, 2005 version; Destination server, 2008 version; I am trying to move less than 1 million records every day. I used the Import/Export wizard to do the job. It works, very fast, in several minutes. However, I cannot schedule a job, since the server agent in the destination 2008 server is not up to run. So I could not schedule any job there. I set an SSIS package on the source 2005 server, using select into with index, which suppose to speed up the process. However, it would still take more than 1 DAY to finish after I schedule it on the 2005 server! Is there a way I could shorten the time? Thank you for any advises!
August 11th, 2011 3:42pm

I assume you have created a SSIS off what the Data Export Import Wizard gave you. Then you can schedule the [Agent , I assume again] job to run on the SQL 2005 ver, box. Another option is to use DTExec run from a bat file. Then you do not need the Agent at all. To speed up the execution you need to open the data Flow Transformation and inspect if the fast load option was used: Turn on Table Lock This option is available in the OLEDB Destination Editor. Selecting "Table Lock" also enables fast load, which tells the adapter to use the IRowsetFastload bulk insert interface for loading. (taken from http://www.simple-talk.com/sql/ssis/sql-server-2005-ssis-tuning-the-dataflow-task/)Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
August 11th, 2011 4:05pm

Best choice using SSIS when moving data cross is to use the DataFlow task in SSIS. Create a connection manager of type "SQL Server" (dont use OLEDB for source since "SQL Server" connection manager is faster and suits your situation too as the job/ssis package will run on your 2005 server which is your data source as well). Hook it up to a OLEDB connection manager that points to the SQL 2008 server, which is your destination. Map the columns between source and destination table and you are all set. DataFlow task is much faster since it makes use of the buffer-based architecture in SSIS. Run the SSIS package manually in BIDS and watch the progress by lookign at the number of rows as they are being transferred between source and destination. That'l also give you an idea of whether the progress is slowwr that you expect. If so, post back your findings in this thread and you'll get more help around perf tuning the package. Hops this helps! Cheers!! Muqadder.
August 11th, 2011 7:58pm

follow others suggestions about destination settings in oledb destination. but my question is : why you don't start the sql server agent on sql server 2008 machine and schedule the package there? what is the problem of sql server agent on that machine? also note that you should save your package at the last steps of import/export wizard to schedule it from sql server agent job or to change its configuration in BIDS.http://www.rad.pasfu.com My Submitted sessions at sqlbits.com
Free Windows Admin Tool Kit Click here and download it now
August 12th, 2011 3:09am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics