By Kalman Toth, M.Phil., M.Phil., MCDBA

March 15, 2005

There are several ways of exporting or importing data from/to a SQL Server database. One method is using bcp - bulk copy program. Another way of importing is BULK INSERT. You can also manually export data from the query result window.

Most popular data movement method today is DTS - Data Transformation Services. It owes its popularity to the GUI interface and the powerful functionality.

Normally you execute a DTS package by running it manually or scheduling it through SQL Server Agent as a job.

What if you want to execute a DTS package from a stored procedure which is called by a Visual Basic program?

You have to use DTSRUN. This is the command exec utility for DTS package execution.

Here is an example:

EXEC master..xp_cmdshell 'dtsrun /N "AccountExport" /S SRVRA999 /E', NO_OUTPUT

This assumes windows authentication.

Prior to testing it from a stored procedure, you should test it in a command shell. Once you get it working, you can test it from a stored proc. Finally, you can test it by calling it from an application like a VB program.

Here are a few useful hints.

The DTS package should be bulletproof because error processing is very difficult. Mostly the DTS error log is the best tool for debugging.

When it is being called from an application, the security context for execution must be appropriate especially if another server is involved. For all practical purposes the login used for execution must have admin rights on all servers involved.

You can use DTSRUNUI to manually fetch a DTS package from the command shell window.

You can use the /~Z construct to indicate that the parameter is encrypted such as login or password.