There are two main methods of executing a DTS package from within T-SQL,
either using the DTSRUN utility to perform the execution, or going through
the DTS object model.
DTSRUN is a command line utility for executing packages. This can be called from within
T-SQL using the xp_cmdshell extended stored procedure. Both are fully documented
in SQL Server Books Online. If you have trouble building the command line a quick tip is to
use the Schedule Package option (Right-Click the package in Enterprise Manager). This
creates a SQL Server Agent Job, from which you can extract the generated command line string.
If you have SQL Server 2000 then the DTSRUNUI utility has a Generate function, amongst the
advanced options.
The slightly more complicated, but more flexible method is to use the OLE stored procedures
to load and execute the package. There is a full explanation of this method in the article DTS Packages & OLE Stored Procedures.
The sample scripts are also mirrored on the Archive page.