June 24, 2008 at 7:40 am
I have a SQL Server 2000 job.
It's executing xp_cmdshell "dtexec ..." command on a linked server
where SQL Server 2000 is installed + SSIS component.
The second step of this job is:
EXECUTE master.dbo.xp_cmdshell 'dtexec /f e:\ssis_packages\Niad.dtsx'
which returns an error in QA:
'dtexec' is not recognized as an internal or external command,
.....
But when I login to that server and run the same command from
DOS it works. Why it doesn't recognize DTEXEC command in T-SQL?
Even
EXECUTE master.dbo.xp_cmdshell 'dtexec /?'
fails.
I have new details now.
I actually logged to the target server
and ran this command in QA:
EXECUTE master.dbo.xp_cmdshell '"C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTexec.exe "'
It recognizes the utility. The output is:
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
NULL
At least one of the DTS, SQL, or File options must be specified.
But when I add "/f" and the command becomes
EXECUTE master.dbo.xp_cmdshell '"C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTexec.exe /f "'
it throws me an old error:
'C:\Program' is not recognized as an internal or external command,
In DOS the output is:
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Missing argument for option "file".
So what we have now?
DTEXEC is being recognized. But when adding more to the command
something breaks. Maybe I should play around with single quotes + double quotes?
June 24, 2008 at 8:25 am
The problem is solved.
This works.
declare @str1 varchar(100)
set @str1 = '"C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTexec.exe"'
set @str1 = @str1 + ' /f e:\ssis_packages\Niad.dtsx'
EXECUTE master.dbo.xp_cmdshell @str1
SSIS package is being executed now.
But the journey is not finished...
The package runs OK from withing BI Development Studio
but it fails using DTEXEC:
...
Error: 2008-06-24 10:09:10.39
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS: Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that
the correct key is available.
End Error
...
I have no idea what is protected XML node "DTS: Password".
Will GOOGLE again
June 24, 2008 at 10:03 am
I changed SSIS Properties.
Security/Protection Level
set to "DontSaveSensitive"
Now "DTS: Password" error is gone but I still get the errors
when running as "xp_cmdshell 'dtextec.....' " from QA
and actually it's the same error from the Command Line.
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connectionmanager "NiadServer" failed with error code 0xC0202009.
...
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'niade
tl'.".
So from Command Line SQL Server Authentication
using "niadetl" account for ConnectionManager fails....
But when running from BI Development Studio
it works fine. Maybe I should change the Owner of the package to
Everybody can READ/EXECUTE? Something like that?
Does anybody know how to do it?
Another option I see is to use Configuration file.
maybe this will force DTEXEC to use the correct credentials?
Some details:
-------------
I saved the package by going to:
File
Save Copy of Niad.dtsx As...
Package Location: File System
Authentication: SQL Server Athentication
"niadetl" is a SYSADMIN SQL Server account.
June 24, 2008 at 10:49 am
Slowly but problems are being solved..
I created Niad.dtsConfig file:
( Sorry. When this post gets saved XML code dissapears.
How to post code properly?
I use IFCode icon in the "Edit Post".
{ } doesn't save it either ...)
Saved the project.
Copied "Niad.dtsx" + "Niad.dtsConfig" files to
E:\ssis_packages\ folder
Executed DOS command:
dtexec /f e:\ssis_packages\Niad.dtsx /conf E:\ssis_packages\Niad.dtsConfig
and it worked OK.
Then in QA I executed:
declare @str1 varchar(100)
set @str1 = '"C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTexec.exe"'
set @str1 = @str1 + ' /f e:\ssis_packages\Niad.dtsx /conf E:\ssis_packages\Niad.dtsConfig'
EXECUTE master.dbo.xp_cmdshell @str1
Shoot!...
Error message:
Started: 12:39:52 PM
Warning: 2008-06-24 12:40:02.31
Code: 0x80012011
Source: Niad
Description: Cannot load the XML configuration file. The XML configuration file may be malformed or not valid.
End Warning
but the package actually ran OK.
The files were genertaed.
Should I ignore this "Cannot load the XML configuration file" error?
June 24, 2008 at 11:13 am
Don't have time to troubleshoot XML format problem.
I just changed it back to:
declare @str1 varchar(100)
set @str1 = '"C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTexec.exe"'
set @str1 = @str1 + ' /f e:\ssis_packages\Niad.dtsx'
EXECUTE master.dbo.xp_cmdshell @str1
and everything works.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply