Execute SP if returns data

Hi all,
I keep getting this error message:
Server: Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'Else'.
I just want to execute a stored procedure if something is returned by
this: "Select hl7file from notes where FileWasCreated is null"
could be a large text file or nothing. If nothing then should do nothing.
I'm calling this from a SQLcommand object in VB.NET
Declare @.Cnt int
set @.Cnt = (Select count(*) from notes where FileWasCreated is null)
If @.Cnt = 0
Else
BEGIN
Select hl7file from notes where FileWasCreated is null
EXECUTE InsertIncrementnumber
END
thanks
gvDo the opposite
If @.Cnt <> 0
BEGIN
..
END
"gv" <viatorg@.musc.edu> wrote in message
news:OxvPKGGaFHA.1148@.tk2msftngp13.phx.gbl...
> Hi all,
> I keep getting this error message:
> Server: Msg 156, Level 15, State 1, Line 10
> Incorrect syntax near the keyword 'Else'.
> I just want to execute a stored procedure if something is returned by
> this: "Select hl7file from notes where FileWasCreated is null"
> could be a large text file or nothing. If nothing then should do nothing.
> I'm calling this from a SQLcommand object in VB.NET
>
> Declare @.Cnt int
> set @.Cnt = (Select count(*) from notes where FileWasCreated is null)
> If @.Cnt = 0
> Else
> BEGIN
> Select hl7file from notes where FileWasCreated is null
> EXECUTE InsertIncrementnumber
> END
>
> thanks
> gv
>|||thanks
gv
"news.microsoft.com" <zheka212@.hotmail.com> wrote in message
news:edjeJJGaFHA.3120@.TK2MSFTNGP12.phx.gbl...
> Do the opposite
> If @.Cnt <> 0
> BEGIN
> ...
> END
> "gv" <viatorg@.musc.edu> wrote in message
> news:OxvPKGGaFHA.1148@.tk2msftngp13.phx.gbl...
>|||On Fri, 3 Jun 2005 13:27:36 -0400, gv wrote:

>Hi all,
>I keep getting this error message:
>Server: Msg 156, Level 15, State 1, Line 10
>Incorrect syntax near the keyword 'Else'.
>I just want to execute a stored procedure if something is returned by
>this: "Select hl7file from notes where FileWasCreated is null"
>could be a large text file or nothing. If nothing then should do nothing.
>I'm calling this from a SQLcommand object in VB.NET
>
> Declare @.Cnt int
> set @.Cnt = (Select count(*) from notes where FileWasCreated is null)
> If @.Cnt = 0
> Else
> BEGIN
> Select hl7file from notes where FileWasCreated is null
> EXECUTE InsertIncrementnumber
> END
>
>thanks
>gv
>
Hi gv,
Don't use COUNT(*) if you only want to know if there is none or at least
one - use EXISTS instead. (EXISTS will stop processing after finding a
row; COUNT will continue to count the other 300 million matching rows,
which is quite a shame if all you do is compare the result to 0).
IF EXISTS (SELECT * FROM notes WHERE FileWasCreated IS NULL
BEGIN
SELECT hl7file FROM notes WHERE FileWasCreated IS NULL
EXECUTE InsertIncrementnumber
END
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Execute Process Task using StandardInputVariable

Greetings!

I am using a Excute Process Task calling a selfwritten EXE and I'm trying to pass a command line argument to it. The code for the EXE is quit simple:

Public Sub Main()
Try
Dim info As String = My.Application.CommandLineArgs.Item(0)
MessageBox.Show("Info:" & info)
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End Sub

According to the help files there are two ways to pass an argument, using the property Arguments, which I guess is for hard-coded stuff, and the StandardInputVariable for passing dynamic info.

When I use the Arguments property everything works fine. I get the info messagebox with correct data. But when I leave that blank and instead use the StandardInputVariable property setting it to my package variable User::Info then I crash into an exception meaning that no arguments existed (array is out of bonds).

The variable User::Info was at first filled from a previous SSIS task and using the OnPreExecute breakpoint I verified that it contained a stringvalue. I then hardcoded a string value into the variable, but nothing helps. The task refuses to start my EXE with the data in the StandardInputVariable as an argument.

Why is this not working?

When you use the StandardInputVariable property setting, are you declaring the StandardInputVariable property in the code for the EXE?|||

I am not sure what you mean by this. The StandardInputVariable is just a property in the SSIS task Execute Process. It's supposed to just pass the content of the selected variable as a start-up argument to the selected EXE. Is there some kind of a global variable inside my VS2005 VB project that automatically gets some info that is not passed as an argument? Or can I declare something like that?

All my EXE file should need to do is look at the arguments (the first only in this case) passed to it like this:

Dim arg as string = My.Application.CommandLineArgs.Item(0)

If i use the Argument propert it works fine. But that is for hard coded start-up values like standard /H for help or /Q for quiet. I need to pass a dynamic value and that's what StandardInputVariable is for. I have verified that my SSIS variable contains the correct data. Yet no argument is delivered to the EXE file.

|||

Use an expression on the Arguments property for passing both static and dynamic command line arguments to an execute process task.

By using an expression, you can incorporate any combination. For example, the expression on Arguments could be: "/Q" + @.User::FilePath

The StandardInputVariable is a task property pointing to a variable who's contents will be streamed in on the process' standard input file handle. If you log the Execute Process specific event entitled "ExecuteProcessVariableRouting", and make use of the StandardInputVariable task property, note that that the log message says, "Routing stdin from variable "User:<your variable here>".

|||

It turns out that you need to include a call to Console.Readline, as follows:

Dim info As String = Console.Readline

And, of course, make sure that the StandardInputVariable property is set to the package variable containing the value you want to pass. I also left the Auguments property setting blank when I tested this procedure.

Carla

|||

Thank you very much. This solved the problem.

I never thought of using Console since my application is a Windows Forms executable, but it worked like a charm.

|||

Please!

Is it possible to link two o more system variables (StartTime + UserName) in a user variable?

I tried Name=User::MyVar and Value=System::StartTime + "Test" but when I try to read by using your

Dim Info As String = Console.ReadLine

it give me the string "System::StartTime + "Test""

Thanks in advance.

Alex.|||

Not sure if I completely understand your question, but it sounds like you need to use an expression for your variable. You can do this by setting the EvaluateAsExpression property of the variable to TRUE, and then enter your expression (i.e., User::MyVar + User::MyVar2) into the Expression property.

You may need to cast the variables to concatenate them. The expression builder will help you validate that the expression is correct.

Execute Process Task using StandardInputVariable

Greetings!

I am using a Excute Process Task calling a selfwritten EXE and I'm trying to pass a command line argument to it. The code for the EXE is quit simple:

Public Sub Main()
Try
Dim info As String = My.Application.CommandLineArgs.Item(0)
MessageBox.Show("Info:" & info)
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End Sub

According to the help files there are two ways to pass an argument, using the property Arguments, which I guess is for hard-coded stuff, and the StandardInputVariable for passing dynamic info.

When I use the Arguments property everything works fine. I get the info messagebox with correct data. But when I leave that blank and instead use the StandardInputVariable property setting it to my package variable User::Info then I crash into an exception meaning that no arguments existed (array is out of bonds).

The variable User::Info was at first filled from a previous SSIS task and using the OnPreExecute breakpoint I verified that it contained a stringvalue. I then hardcoded a string value into the variable, but nothing helps. The task refuses to start my EXE with the data in the StandardInputVariable as an argument.

Why is this not working?

When you use the StandardInputVariable property setting, are you declaring the StandardInputVariable property in the code for the EXE?|||

I am not sure what you mean by this. The StandardInputVariable is just a property in the SSIS task Execute Process. It's supposed to just pass the content of the selected variable as a start-up argument to the selected EXE. Is there some kind of a global variable inside my VS2005 VB project that automatically gets some info that is not passed as an argument? Or can I declare something like that?

All my EXE file should need to do is look at the arguments (the first only in this case) passed to it like this:

Dim arg as string = My.Application.CommandLineArgs.Item(0)

If i use the Argument propert it works fine. But that is for hard coded start-up values like standard /H for help or /Q for quiet. I need to pass a dynamic value and that's what StandardInputVariable is for. I have verified that my SSIS variable contains the correct data. Yet no argument is delivered to the EXE file.

|||

Use an expression on the Arguments property for passing both static and dynamic command line arguments to an execute process task.

By using an expression, you can incorporate any combination. For example, the expression on Arguments could be: "/Q" + @.User::FilePath

The StandardInputVariable is a task property pointing to a variable who's contents will be streamed in on the process' standard input file handle. If you log the Execute Process specific event entitled "ExecuteProcessVariableRouting", and make use of the StandardInputVariable task property, note that that the log message says, "Routing stdin from variable "User:<your variable here>".

|||

It turns out that you need to include a call to Console.Readline, as follows:

Dim info As String = Console.Readline

And, of course, make sure that the StandardInputVariable property is set to the package variable containing the value you want to pass. I also left the Auguments property setting blank when I tested this procedure.

Carla

|||

Thank you very much. This solved the problem.

I never thought of using Console since my application is a Windows Forms executable, but it worked like a charm.

|||

Please!

Is it possible to link two o more system variables (StartTime + UserName) in a user variable?

I tried Name=User::MyVar and Value=System::StartTime + "Test" but when I try to read by using your

Dim Info As String = Console.ReadLine

it give me the string "System::StartTime + "Test""

Thanks in advance.

Alex.|||

Not sure if I completely understand your question, but it sounds like you need to use an expression for your variable. You can do this by setting the EvaluateAsExpression property of the variable to TRUE, and then enter your expression (i.e., User::MyVar + User::MyVar2) into the Expression property.

You may need to cast the variables to concatenate them. The expression builder will help you validate that the expression is correct.

execute package task can't continue when the child package failed?

I use a execute package task to run a child package in which I run some sql task.

as the error handle I insert a script task and link a line from execute package task to script task

of course the line is red,

but I found when the child package failed, the execute package task turns red,it stopped

the script task can't be run, I don't konw why?

I tried it again.

I found that if the child package successed then the parent package can run in the success flow

but if child package falied then the parent package will stop at the execute package task, can't run the failure flow.

Anybody konws this....

|||

ycjj wrote:

I tried it again.

I found that if the child package successed then the parent package can run in the success flow

but if child package falied then the parent package will stop at the execute package task, can't run the failure flow.

Anybody konws this....

i'm not sure if this will work. however, try setting the FailParentOnFailure property of the child package to "false".|||

thank you for the answer.

I also think it seems strange....

I tried it. The default value of FailParentOnFailure is false.

I tried it by setting false and true

the result is same.The child package stopped when it failed.

|||

ycjj wrote:

thank you for the answer.

I also think it seems strange....

I tried it. The default value of FailParentOnFailure is false.

I tried it by setting false and true

the result is same.The child package stopped when it failed.

ok. the child package will always stop executing when it fails. my understanding was that you wanted control to return to the parent package when the child package fails. to execute a script task when the child package fails, an on failure precedence constraint from the child package should be connected to the script task.|||

>my understanding was that you wanted control to return to the parent package when the child package fails. to execute a script task when the child package fails, an on failure precedence constraint from the child package should be connected to the script task.

first thanks for your attention.

yes, I want to return to parent package after the child package failed and I hope that the parent package will continue to execute from the execute package task on the failure precedence constraint to a script task as a error handle.but now the execute package task stops if the child package fails.

maybe the failure precedence constraint is wrong......

|||

I found the prpperty "LogicalAnd" of the failure precedence constraint from execute package task to script task(error handle)

I change the value of property "LogicalAnd" from "True" to "False", it works well, but I still don't know the reason.

|||

I made some study and know the reason now .

in my parent package, some execute package tasks have a common error handle task, but I set property "LogicalAnd" of these failure precedence constraint "false"(dedault), it means all of execute package tasks fail will cause error handle task running, of course this is not right,

it must be one of of execute package tasks fails will cause error handle task running, so the value of property "LogicalAnd" is "True" is correctly.

thanks.