Creating and Loading Update Files

I need to load refreshed data daily to my client database located at a remote site. I prefer to bundle an update file and transfer it through FTP to the remote site so that DBAs there can run it and update their database. Can SQL Server create and load this type of update file instead of doing direct replication?

You have two options for creating and loading the file, depending on what works best with your system. You can set up snapshot replication and choose the FTP option to deliver the snapshot. Here's the procedure. After you set up the publication, go to the Publication Properties dialog box, then to the Snapshot Location tab. Select the Generate Snapshots in the following location option and the Subscribers can access this folder using FTP option. You can create a pull subscription to this publication and download the snapshot through FTP.

Alternatively, you can use Data Transformation Services (DTS), which you can access through Enterprise Manager. This tool includes an FTP task and other options that let you FTP the file, load it into SQL Server, validate its contents, and apply the updates to the system. However, with DTS, you need to handle updates, inserts, and deletes yourself, something that replication does for you.