SQL Server 2012 Package Execution utility

  • I'm trying to write a sproc that executes a SQL Server 2012 SSIS package (which I've already written.) When I execute the sproc:

    alter PROCEDURE [dbo].[cpas_execute_ssis]

    -- Add the parameters for the stored procedure here

    @command varchar(1000) = null

    AS

    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    DECLARE @cmd_command varchar(1000)

    print @command

    SET @cmd_command = 'dtexec.exe ' + @command

    print @cmd_command

    EXEC xp_cmdshell @cmd_command

    END

    With the following:

    exec dbo.[cpas_execute_ssis] @command=' /f "\\winntdom\common\mtv\secure\Construction\Access\CPAS WorkArea\Jim\Projects\AllProjectsImport\AllProjectsImport\Package.dtsx" '

    Well, the first thing I notice is it's executing an old version of dtexec (Version 9.00.5000.00 for 32-bit)...is there a more current version?

    Then, it can't find the package. Can't tell if it's a UNC issue, a security issue, or because the file is newer than the Version 9 dtexec...

    Error: 2013-10-16 15:03:48.18

    Code: 0xC0011007

    Source: {49B15C9C-D17C-46C3-9B6A-429422AC1C11}

    Description: Unable to load the package as XML because of package does not have a valid XML format. A specific XML parser error will be posted.

    End Error

    Error: 2013-10-16 15:03:48.20

    Code: 0xC0011002

    Source: {49B15C9C-D17C-46C3-9B6A-429422AC1C11}

    Description: Failed to open package file "\\winntdom\common\mtv\secure\Construction\Access\CPAS WorkArea\Jim\Projects\AllProjectsImport\AllProjectsImport\Package.dtsx" due to error 0x800C0006 "The system cannot locate the object specified.". This happ

    ens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect

    format.

    End Error

    I assume when this sproc runs, it uses my account, and I can see that directory and file.

    Ideas?

    Jim

  • It's probably the space in your path.

    Try replacing "CPAS WorkArea" with "CPAS~1" and see if that helps.

  • Thanks for your reply. I determined the job is getting "access denied". I've troubleshot that as far as I can. It's in the hands of the IT folks now.

    Jim

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

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