February 17, 2010 at 1:41 pm
I'm executing a DTS package with the DTSRun command inside an SP. I'm doing an Insert Exec to capture the output of the DTSRun so I can log what's happening. When I do this I have to search the Output for a "Package Execution Complete" to ensure it successfully ran.
Is there another way to know whether the Package ran successfully?
Here is sample code form my SP:
Create Table #ImportLog (Output varchar(1000))....
SET @CMD = 'DTSRun /S ....."
Insert #ImportLog (OutPut)
exec master.dbo.xp_Cmdshell @CMD
select @Log = @Log + Output from #ImportLog
IF charindex('Package Execution Complete',@Log) = 0
Print 'Failure'
TIA!
February 17, 2010 at 1:46 pm
If you call xp_cmdshell like
DECLARE @retval int
DECLARE @cmd varchar(8000)
SET @cmd = 'SET' -- Good command
EXEC @retval = master.dbo.xp_cmdshell @cmd
SELECT @retval
Should return 0
DECLARE @retval int
DECLARE @cmd varchar(8000)
SET @cmd = 'IsABadCommand' -- Bad command
EXEC @retval = master.dbo.xp_cmdshell @cmd
SELECT @retval
Should return 1
That is the overall status..
CEWII
February 17, 2010 at 1:53 pm
Elliott W,
Thanks for the reply. I wasn't referring to the execution of xp_CMDShell. What I'm looking for is if one of the steps failed within the package resulting in the package reporting a failure.
If you use DTSRun in EM or QA it'll return a recordset like so:
DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDynamicPropertiesTask_1 DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 15 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 15 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete.
I'm trying to determine if that package actually failed or successfully completed without using that recordset.
February 17, 2010 at 4:35 pm
If you enable package logging to SQL Server, it will log errors in msdb.dbo.sysdtspackagelog and msdb.dbo.sysdtssteplog. You can query those.
Enable logging by editing the package, opening Package Properties and setting it on the Logging tab.
Greg
February 17, 2010 at 7:40 pm
I'm going with Greg on this, look at package logging.. The only downside is you have to read the tables directly since SSMS in SQL 2005/2008 don't have a way to display it. Other than that...
CEWII
February 18, 2010 at 8:38 am
Package logging eh? Cool....
Like I said I'm currently dumping the record set from xp_CmdShell into a table so I can output and store the results but hopefully the Package Logging will offer easier results to parse thru. Thanks much!
February 18, 2010 at 8:45 am
AVB (2/18/2010)
Package logging eh? Cool....Like I said I'm currently dumping the record set from xp_CmdShell into a table so I can output and store the results but hopefully the Package Logging will offer easier results to parse thru. Thanks much!
It should be much easier to parse since it is much more easily treated as data. I personally hate to parse output from xp_cmdshell.
CEWII
February 18, 2010 at 8:51 am
Yeah the output from xp_CmdShell is good for an overall picture of what happened but trying to obtain any specific results like Records imported etc. is a pain.
February 18, 2010 at 8:55 am
I often catch the output that gets dropped into a log file.. But you are absoutely right, picking out data from that is a huge hassle since it is meant for us humans to read..
CEWII
March 4, 2010 at 11:28 am
Its really Helpful.. Thank you so much..
You made my day...
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply