SSIS help

What's the query?

Thank You.. I have been struggling with this all day..

SELECT     SUM(CURRENT_ONHAND) AS COH
FROM         SFA.Z_ITEM_SUPPLY

Use a conditional dataflow like this

http://blogs.msdn.com/b/mattm/archive/2006/11/01/conditional-constraints.aspx

I dont know how to write a vb script task.. How do I write this.. I pay for this site and I'm having so much trouble getting help today.. Keep getting sent to links on the internet..

Use an executesql task, put your sql in that and assign the result to a variable. If value =0, run email task otherwise go to next task using conditional dataflow

Should the result set be set to Single Row?

Also, do I get to Parameter mappings, resultset or expressions to assign the value to a variable?

yes resultset should be single row since it's a count. Use parameter mappings to assign the value
Refer this article

http://technet.microsoft.com/en-us/library/ms140355.aspx

Should the container name be the name of the SQL TAsk editor or the name of the package.. The link you sent me was to vague.. I need more

When I run it I get the error below

[Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".

Container name should be the name of the task

Check these steps

at package level, create the variable(s).
in the SQL task, general pane, set to single row. ensure all columns in the select are given an alias.
In the Result Set pane, set the result name = the alias('s, if more than one), with the variable(s) in Variable Name column
this will map the result of the SQL query to the variable(s) and populate the variable(s) accordingly.

Refer this thread for more details

http://www.sqlservercentral.com/Forums/Topic849240-391-1.aspx#bm850247

I did step by step what you told me to do and I get the error below.. The data type is NUMBER( this is Oracle) I set the package variable data type to int64 and then int32 and then single.. Keep getting error below

Error: The type of the value being assigned to variable "User::COH" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

since it is a sum it will probably have decimal values. So try DT_DECIMAL. It may not be a pure integer.

Refer the different SSIS datatypes

http://msdn.microsoft.com/en-us/library/ms141036.aspx

That data type is not one of my choices.. See my screen print
example.bmp

Try DT_NUMERIC.
Refer this table for datatype mapping

http://www.bidn.com/blogs/DevinKnight/ssis/1387/ssis-to-sql-server-data-type-translations

But it's not a choice in the drop down for the variable..Where do I make this happen?

What are the choices you have?

There are to many to list.. Non of the ones you suggested are on there which makes me think I'm on the wrong path.. DId you see my screen shot?

All the options are not visible in the screen shot since it's a combobox. That's why it will be helpful if you list all of them.

Boolean, Bite, Char, datetime,dbnull,double,int16,32,64,Object sbite,single,string,uint32/64,Sbite.. None of the SSIS datatypes are listed.. NOne of them which is why I'm confused..

use double

[Execute SQL Task] Error: An error occurred while assigning a value to variable "COH": "The type of the value being assigned to variable "User::COH" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
".

What's the datatype for CURRENT_ONHAND?

Numeric

Is it pure integer or will it have decimal places?

It is pure integer..I basically just want the package to fail if the sum of count on hand is 0

Try int32, int64, uint32 or uint64

Here is what I did, I populated a table in SQL Server called tblCOH with one column called COH data type int.. Then I added another script task with the code below that checks the col for data.. I can now populate a variable(it works) called COH with the record.. Now that my variable called COH is finally populated.. How do I write a VBscript task to fail the package is the the COH variable is <1?

actually the variable datatype is DBnull.. Its the only datatype that will work

if coh < 1 then

if coh < 1 then
 exit
else
 execute next task
end if

See this for detailed info

http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/08bfcf33-50bb-46cb-b681-15d7a32403d9/

I put this in a VBSCRIPT editor and get the error below
Public Sub Main()
        If Dts.Variables("User::COH").Value.Equals(0) Then
            Dts.TaskResult = Dts.Results.Failure
        Else
            Dts.TaskResult = Dts.Results.Success
        End If
        Dts.TaskResult = ScriptResults.Success
    End Sub


Error      1      'Results' is not a member of 'Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel'.      C:\Documents and Settings\csmith\Local Settings\Temp\aa\SSIS\85256562f4cd4259a2bd29a1253c01af\ScriptMain.vb      39      30      st_9c94ac60e2914f0b9043db4d66c290de

Thanks for all your help!