Simple Package - Variables

[unmaxpine]

Hello,

I have created a view named V_XX_Plan on my SQL Server 2005 Database.

This view contain only one row and I select only one column like this:

select errors from V_XX_Plan
--> should always return 0

I would like to create a SSIS package to check if this value is = 0 or if not, to do a "send email" task.

For that I create a new SSIS package.

I have a variable called "Errors", package, Int32.

I created a SQL Task with the SQL query on the view and with this parameter:

ResultSet: Single Row
SQL Source: Direct Input
SQL Statement: select errors from V_XX_Plan
Result Name: 0 --> User::Errors



And know how can I do execute my "Email" task depending of this result (send only if <> 0) ?

Thanks in advance for your help

 

[mrdenny]

You will want to add a Script task and use VB.NET script to evulate the DTS variable. If the value is 0 then mark the task completed. If the value is not mark the task successful and move on to the email sending task.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[MDXer]

Another option is to use conditional work flows. I have packages where I split based upon an error count. I have a work flow that is executed if @err = 0 and a seperate that executes if @err > 0.

double click on your work flow which should open your Precedence COnstraint Editor. under Evaluation operation change it to Expression, in the expression box enter the expression.

in mine I send a workflow to the 0 error task and one to the error task.



Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[unmaxpine]

Sorry I was out off the office since some days and only look today your answers, many thanks.

As I have any knowledge in developement, I try this without success:

My variable is called: Plan_Errors / Package / Int32 / 0

Step 1
I do a select from a view: select errors from errorsview
This will always return only one value. The resultset is set to Single Row and the Result set 0 is mapped to my variable User:lan_Errors.

Setp 2 On succeed
As you adise I created a script task "Value Check" with a ReadWriteVariables User:lan_Errors. The script look like this:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain
	Public Sub Main()
        '
        'If CInt(Dts.Variables.Contains("User::Plan_Errors")) <> 0 Then
          MsgBox(Dts.Variables("User::Plan_Errors"))
          MsgBox("Error")
          Dts.TaskResult = Dts.Results.Failure
        End If
        MsgBox("OK")
        Dts.TaskResult = Dts.Results.Success
   End Sub
End Class

When I run this, I have this error message on the script task:

"The argument 'Prompt' could not be converted to string."

 

[RiverGuy]

Try:

Dts.Variables("VariableName").Value.ToString()

 

[unmaxpine]

You're right, thank you !