MS SQL Server DTS - Flow question

Author

Flow question

JuiceN

2006-01-26, 4:58 pm

Hi

I am writing a package that i want to have email me on two possible success
scenarios.

essentially, this is the conditions:

IF @result = 0
BEGIN
EXEC master..xp_cmdshell @copyfile, NO_OUTPUT
PRINT 'Operation Successful'
END
ELSE
BEGIN
PRINT 'This operation failed. Error Code:01. The source and destination
files are identical.'
END

If the first condition is met, I want to fire off an email stating success.
If the second condition is met then i want to fire off a different email.

Now, the problem is, I am not sure how to flow it. Both conditions are
successful, thus it always fires off the success email if i use an "On
Success" flow.

How do i capture the error i want to create before the email to tell it
which email to send?

Does this make sense?

Ryan

2006-01-26, 4:58 pm

This article explains in detail how to branch your workflow.

http://www.sqldts.com/default.aspx?218

--
HTH. Ryan
"JuiceN" <JuiceN@discussions.microsoft.com> wrote in message
news:B5DEA8C6-A42E-4BD0-B16B- DF0CBF545E83@microso
ft.com...
> Hi
>
> I am writing a package that i want to have email me on two possible
> success
> scenarios.
>
> essentially, this is the conditions:
>
> IF @result = 0
> BEGIN
> EXEC master..xp_cmdshell @copyfile, NO_OUTPUT
> PRINT 'Operation Successful'
> END
> ELSE
> BEGIN
> PRINT 'This operation failed. Error Code:01. The source and destination
> files are identical.'
> END
>
>
> If the first condition is met, I want to fire off an email stating
> success.
> If the second condition is met then i want to fire off a different email.
>
> Now, the problem is, I am not sure how to flow it. Both conditions are
> successful, thus it always fires off the success email if i use an "On
> Success" flow.
>
> How do i capture the error i want to create before the email to tell it
> which email to send?
>
> Does this make sense?
>

JuiceN

2006-01-26, 4:58 pm

This will work, sort of.

How do i capture the event from the query in my SQL task so that I do not
have a message box requesting which path to chose? I don't want a user to
have to chose. I want the path chosen based on the result of the SQL task.

"Ryan" wrote:

> This article explains in detail how to branch your workflow.
>
> http://www.sqldts.com/default.aspx?218
>
> --
> HTH. Ryan
> "JuiceN" <JuiceN@discussions.microsoft.com> wrote in message
> news:B5DEA8C6-A42E-4BD0-B16B- DF0CBF545E83@microso
ft.com...
>
>
>

Allan Mitchell

2006-01-26, 4:58 pm

Hello JuiceN,

So you want a Query result to be the determinant pf path?

Have the Execute SQL task read into a Global Variable the returned value.
Now in the Active X Script task you can choose based on the value in the
Global variable the path to take.

Allan
[color=darkred]
> This will work, sort of.
>
> How do i capture the event from the query in my SQL task so that I do
> not have a message box requesting which path to chose? I don't want a
> user to have to chose. I want the path chosen based on the result of
> the SQL task.
>
> "Ryan" wrote:
>

Sponsored Links