Post reply

Execute packages from within a stored procedure in SQL 2005

  • December 17, 2008 at 12:06 pm

    #155034

    I am curious to know if I can execute a package from within a stored procedure in SQL Server 2005.

    In Sql 2000 one could use the following command in your procedure.

    exec master.dbo.xp_cmdshell 'dtsrun /s ... /u ... /p ... /n (packagename goes here)'

    This works well for data imports that dont follow a schedule, but are at the whim of the user.

    I have activated this stored proc in 2005, but it says it cant find my sql package

    it says:

    Error string: The specified DTS Package ('Name = 'pkgMebittView10'; ID.VersionID = {[not specified]}.{[not specified]}') does not exist.

    I have not supplied the id or versionid because It is not apparent from the server.

    Thx,

    RGG

  • December 17, 2008 at 3:18 pm

    #914204

    In SQL 2005, dtsrun was replaced with dtexec. dtsrun, if still installed, probably doesn't understand the 2005 package structure.

    Try replacing the dtsrun in your package with dtexec and see if it works. (You might also need to double check any other parameters you were passing to see if they remained consistent between dtsrun and dtexec.)

  • December 18, 2008 at 7:41 am

    #914541

    Thanks... I had tried the dtexec route before, but received an error that seemed to imply that dtexec was not available.

    It worked, however, when I typed the following line in my Stored proc:

    EXEC xp_cmdshell 'dtexec /sq xxxxxxxxxxxxxxx' where xxxxxxxxxxx = my package stored in SQL Server 2005.

    Thanks again,

    RGG

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

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