Execute a DTS package from X++
Recently someone asked how to execute a DTS package from X++. This was a great question and great timing because I had just recently completed a project that had to execute a DTS package. Below is the code that will allow you to do this:
X++:
protected void runDTSpkg()
{
OdbcConnection cn;
ResultSet rs;
Statement s;
str SQL;
str temp;
;
// Open up a connection to the Master Database, and execute the DTS package
cn = this.CN("master");
s = cn.createStatement();
SQL = "xp_cmdshell 'dtsrun /SVSQL /Usomeuser /Pblocked /NPumpOverCS_SHIP'";
rs = s.executeQuery(SQL);
while(rs.next())
{
// Here you could see if an error occurs, or just use this loop to allow the DTS to finsih.
temp = rs.getString(1);
MMI_ErrLog::logError("PkgScoop",strfmt("%1",temp));
}
}So there are a couple of things I want to point out here. One is the actual statement used to do this:
xp_cmdshell 'dtsrun /SVSQL /Usomeuser /Pblocked /NPumpOverCS_SHIP'
The /S = Server Name, /U = User Name, /P = Password /N = Name of DTS package to execute. Notice that the parms are ran together with what your passing. This Must happen or you get an error.
Another thing I wanted to point out was that I do a while(rs.next()) {...} for the resultset. When executing a DTS package through the stored procedure xp_cmdshell results are returned telling you about what is going on. You can read these results and act on them, or do like I do and just put them into a general log table. Whatever you do though, you must preform the while(rs.next()) {...} so you can allow the DTS to fully finish before you continue to your next block of code that I assume would operate on the data that the DTS package brought in / manipulated, etc.