May 2, 2006 at 8:29 am
Ok, so I've got a package that uses a looping mechanism to continually look for the existance of 5 files. As each file is received it kicks off a process to load the data. Here's the rub. I don't want to use a success/failure workflow model because if a file is not received, then it is reported as a failure in the log and for the package status - neither of which is really true (although technically it is). So, I've changed the workflow to use a completion/success model. Works fine. However, I want to be able to skip steps when a package has been processed. So, I created a global variable for each file and set that value to a 1 when it has been processed.
Next, I read that variable using an active x script to see if it is set to 1 or not. If so, I want to skip the step. So, I set the step equal to DTSStepScriptResults_DontExecuteTask. Which works. However, when the script is run in graphical mode, it returns a message stating that the step returns an invalid step result. I guess this is because the active x script is setup to return DTSTaskExecResult_Failure by default.
Anyone know of a way to skip the step and report a positive result from the script?
May 2, 2006 at 1:31 pm
I am not sure if this is the best way but I have a PROC that checks a directory. If the file is there it initiates the job. That way it will succeed or fail for real.
procedure
[dbo].[PROC_FILE_CHECK] @strPathAndFileName varchar(500) as
DECLARE
@strCMD varchar(1500), @strResponse varchar(100)
set
@strResponse = 'FAILURE'
CREATE
TABLE #tblResponse (outputColumn VARCHAR(500) NULL)
SET
@strCMD = 'master..xp_cmdshell ' + CHAR(039) + 'DIR ' + @strPathAndFileName + CHAR(039)
INSERT
INTO #tblResponse exec (@strCMD)
IF
(SELECT COUNT(*) FROM #tblResponse WHERE outputColumn like '%' +
(
select reverse( substring ( reverse( @strPathAndFileName) , 1 , patindex( '%\%' , reverse( @strPathAndFileName ) ) - 1 ) ) ) + '%' ) > 0
BEGIN
IF
(SELECT COUNT(*) FROM #tblResponse WHERE outputColumn like '%' +
(
select substring ( @strPathAndFileName , 1 , len ( @strPathAndFileName ) - patindex( '%\%' , reverse( @strPathAndFileName ))) ) + '%' ) > 0
Begin
set @strResponse = 'SUCCESS'
END
END
DROP
TABLE #tblResponse
IF
@strResponse = 'SUCCESS'
Return 1
ELSE
Return 0
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply