October 16, 2013 at 1:19 pm
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
October 18, 2013 at 10:43 am
It's probably the space in your path.
Try replacing "CPAS WorkArea" with "CPAS~1" and see if that helps.
October 19, 2013 at 10:07 am
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