• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 702
  • Last Modified:

SSIS help

If the result of my query below = 0 then I need an email sent other wise I need the next task to run.. How do I do this? I have tried precedense constraits and conditional flows.. Can get this to work..please help
0
cheryl9063
Asked:
cheryl9063
  • 18
  • 16
1 Solution
 
Partha MandayamTechnical DirectorCommented:
What's the query?
0
 
cheryl9063Author Commented:
Thank You.. I have been struggling with this all day..

SELECT     SUM(CURRENT_ONHAND) AS COH
FROM         SFA.Z_ITEM_SUPPLY
0
 
Partha MandayamTechnical DirectorCommented:
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
cheryl9063Author Commented:
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..
0
 
Partha MandayamTechnical DirectorCommented:
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
0
 
cheryl9063Author Commented:
Should the result set be set to Single Row?
0
 
cheryl9063Author Commented:
Also, do I get to Parameter mappings, resultset or expressions to assign the value to a variable?
0
 
Partha MandayamTechnical DirectorCommented:
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
0
 
cheryl9063Author Commented:
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
0
 
cheryl9063Author Commented:
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".
0
 
Partha MandayamTechnical DirectorCommented:
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
0
 
cheryl9063Author Commented:
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.
0
 
Partha MandayamTechnical DirectorCommented:
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
0
 
cheryl9063Author Commented:
That data type is not one of my choices.. See my screen print
example.bmp
0
 
Partha MandayamTechnical DirectorCommented:
0
 
cheryl9063Author Commented:
But it's not a choice in the drop down for the variable..Where do I make this happen?
0
 
Partha MandayamTechnical DirectorCommented:
What are the choices you have?
0
 
cheryl9063Author Commented:
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?
0
 
Partha MandayamTechnical DirectorCommented:
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.
0
 
cheryl9063Author Commented:
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..
0
 
Partha MandayamTechnical DirectorCommented:
use double
0
 
cheryl9063Author Commented:
[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.
".
0
 
Partha MandayamTechnical DirectorCommented:
What's the datatype for CURRENT_ONHAND?
0
 
cheryl9063Author Commented:
Numeric
0
 
Partha MandayamTechnical DirectorCommented:
Is it pure integer or will it have decimal places?
0
 
cheryl9063Author Commented:
It is pure integer..I basically just want the package to fail if the sum of count on hand is 0
0
 
Partha MandayamTechnical DirectorCommented:
Try int32, int64, uint32 or uint64
0
 
cheryl9063Author Commented:
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?
0
 
cheryl9063Author Commented:
actually the variable datatype is DBnull.. Its the only datatype that will work
0
 
Partha MandayamTechnical DirectorCommented:
if coh < 1 then
0
 
Partha MandayamTechnical DirectorCommented:
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/
0
 
cheryl9063Author Commented:
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


0
 
Partha MandayamTechnical DirectorCommented:
0
 
cheryl9063Author Commented:
Thanks for all your help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 18
  • 16
Tackle projects and never again get stuck behind a technical roadblock.
Join Now