Post reply

running a DTS package from a stored procedure

  • March 24, 2005 at 11:35 am

    #88446

    i created a DTS package to import data from a text file to a MSSQL table and it works well. then i created a stored procedure to run the DTS package:

    CREATE PROCEDURE dbo.erehabDTS_import AS

    EXEC master.dbo.xp_cmdshell 'dtsrun /S localhost /U MyColdFusionUser /P mypassword /N erehab_import', NO_OUTPUT

    GO

    and i wanted to run this stored PROC from a coldfusion template.

    when i try to run this i got an error about not having permissions to the xp_cmdshell procedure.

    so i followed the procedure found at http://www.novicksoftware.com/Articles/SQL-Server-2000-SP3-and-xp_cmdshell-Woes.htm

    and now when i run it from the CF template i dont get an error but the table is not populated.

    if i run the above dtsrun from the command line on the SQL server, it populates the table so that leads me to believe that the problem is still with xp_cmdshell but i dont know where to go from here - any advice?

  • March 24, 2005 at 1:14 pm

    #548837

    I think probably the problem is with your DTS package.  DTS runs in the context of the person/machine running it.  If your flat file is on the c:\ drive of the server, and you run the package from your desktop, it'll look for the flat file on YOUR c:\ drive.  If your file is on a mapped drive, and you log in to the server and run the package, if you have access to the mapped drive, it'll work.  But when you schedule it, if the id that DTS is running under doesn't have that drive mapped or has it mapped as a different drive, it'll fail.  Try changing the file specs in the DTS package to a UNC path, and see if it works then ( \\MyServerName\c$\foldername\flatfile.txt) (edit... stupid emoticons!) .  Oh, you'll need to be sure that the id that DTS is running under is a local admin if you use the administrative share as in my example.

    Steve

  • March 24, 2005 at 2:29 pm

    #548858

    that was it - i had the source file in the DTS set to a mapped drive on the server - i didnt realize it was specific to the machine running the CF template. i changed it to \\servername\c$\path\file and now it works nicely.

    thanks!!!!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply