July 13, 2007 at 9:10 am
I am curious to know if I was to have an Execute SQL task which had some code such as
SELECT COUNT(*) FROM TableA WHERE FileName = 'SomeFile'
and I get a record count back is there a way to FAIL the step which would fail the package?
July 13, 2007 at 9:27 am
Yes:
if exists (
select filename
from tableA
where Filename = 'SomeFile') begin
raiserror('File found', 16, 1)
end
J
July 13, 2007 at 9:30 am
So by using the RaiseError command that will fail the package? which would allow me to branch some logic (on failure/on success)?
Thanks for the quick reply.
Leeland
July 13, 2007 at 9:38 am
What you are asking for is conditional processing based on a return value rather than fail a step. There was a post on here somewhere (can't find it at the moment) which discussed this exact point.
I think what you need to do is to put the return value into a global variable and then use an ActiveX script to enable or disable particular steps which would branch you DTS package.
If I find the other post , I'll let you know.
Have a look at http://www.sqldts.com/246.aspx for the general idea.
J
July 13, 2007 at 12:09 pm
I got it figured out and it was pretty simple once I got it to work. I used the SQLExecute Task to execute a query (SELECT COUNT(*) FROM Table_A WHERE Table_A = ?)
The question mark is setup for an input parameter which is a global variable @FileName...which I get from a previous process in the DTS Job.
The query results are then sent to another global variable @RowCount as an output parameter.
The next step is an activeX script that does a simple If statement to check the value of @RowCount. If @RowCount > 0 then fail package, move file in question to Error Directory, and Send email message of the issue.
Thanks for getting me on the right path Jeremy.
Leeland
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply