Copying SQL DTS jobs between servers

In the past I’ve moved DTS packages directly between servers by saving them to the target server as a DTS package but today had a problem and couldn’t connect to the target server from the source so copied the DTS jobs as files.   Both methods of copying I’ve used:

Moving the DTS packages between servers which can see one another:

1. Open the DTS package on the server you want to copy from, open as if you’re going to edit it.
2. From the menu – Package – Save As
3. Ensure “SQL Server” is selected in the location.
4. Select the target server in the “Server” drop down.
5. Select appropriate authentication method.
6. Click “OK”

Moving the DTS packages between servers which cannot see one another:

1. Open the DTS package on the server you want to copy from, open as if you’re going to edit it.
2. From the menu – Package – Save As
3. Ensure “Structured Storage File” is selected in the location.
4. Select the target file name.
5. Click “OK”
6. Copy the file to your target server or to a shared location your target server can access.

On the Target Server:
1. Right-click on the “Data Transformation Services” folder of the Server in server explorer.
2. Select “Open Package”.
3. Open the file containing the DTS package you copied earlier.
4. Select the package you want to import.
5. Click “OK” – the package will be imported.
6. Save the package.  From the menu:  Package – Save As – Location SQL Server –  Server Local

Warning : Depending on which method you use you may not be able to overwrite existing packages on the target server.  If this is the case you’ll either have to delete the old package (on the target server) or rename it before copying.
Remember : Any data sources in the package remain unchanged so you may need to change Server references in the DTS package you’ve just copied.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s