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.