?
Solved

SSIS help

Posted on 2011-10-25
34
Medium Priority
?
657 Views
Last Modified: 2013-11-18
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
Comment
Question by:cheryl9063
  • 18
  • 16
34 Comments
 
LVL 6

Expert Comment

by:mcp111
ID: 37026411
What's the query?
0
 
LVL 1

Author Comment

by:cheryl9063
ID: 37026442
Thank You.. I have been struggling with this all day..

SELECT     SUM(CURRENT_ONHAND) AS COH
FROM         SFA.Z_ITEM_SUPPLY
0
 
LVL 6

Expert Comment

by:mcp111
ID: 37026501
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Author Comment

by:cheryl9063
ID: 37026542
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
 
LVL 6

Expert Comment

by:mcp111
ID: 37026606
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
 
LVL 1

Author Comment

by:cheryl9063
ID: 37026620
Should the result set be set to Single Row?
0
 
LVL 1

Author Comment

by:cheryl9063
ID: 37026631
Also, do I get to Parameter mappings, resultset or expressions to assign the value to a variable?
0
 
LVL 6

Expert Comment

by:mcp111
ID: 37026704
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
 
LVL 1

Author Comment

by:cheryl9063
ID: 37026733
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
 
LVL 1

Author Comment

by:cheryl9063
ID: 37026744
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
 
LVL 6

Expert Comment

by:mcp111
ID: 37026859
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
 
LVL 1

Author Comment

by:cheryl9063
ID: 37026972
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
 
LVL 6

Expert Comment

by:mcp111
ID: 37027026
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
 
LVL 1

Author Comment

by:cheryl9063
ID: 37027108
That data type is not one of my choices.. See my screen print
example.bmp
0
 
LVL 6

Expert Comment

by:mcp111
ID: 37027236
0
 
LVL 1

Author Comment

by:cheryl9063
ID: 37027257
But it's not a choice in the drop down for the variable..Where do I make this happen?
0
 
LVL 6

Expert Comment

by:mcp111
ID: 37027403
What are the choices you have?
0
 
LVL 1

Author Comment

by:cheryl9063
ID: 37027634
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
 
LVL 6

Expert Comment

by:mcp111
ID: 37027696
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
 
LVL 1

Author Comment

by:cheryl9063
ID: 37027718
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
 
LVL 6

Expert Comment

by:mcp111
ID: 37027767
use double
0
 
LVL 1

Author Comment

by:cheryl9063
ID: 37027788
[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
 
LVL 6

Expert Comment

by:mcp111
ID: 37027826
What's the datatype for CURRENT_ONHAND?
0
 
LVL 1

Author Comment

by:cheryl9063
ID: 37028047
Numeric
0
 
LVL 6

Expert Comment

by:mcp111
ID: 37028812
Is it pure integer or will it have decimal places?
0
 
LVL 1

Author Comment

by:cheryl9063
ID: 37029087
It is pure integer..I basically just want the package to fail if the sum of count on hand is 0
0
 
LVL 6

Expert Comment

by:mcp111
ID: 37031949
Try int32, int64, uint32 or uint64
0
 
LVL 1

Author Comment

by:cheryl9063
ID: 37032043
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
 
LVL 1

Author Comment

by:cheryl9063
ID: 37032069
actually the variable datatype is DBnull.. Its the only datatype that will work
0
 
LVL 6

Expert Comment

by:mcp111
ID: 37033145
if coh < 1 then
0
 
LVL 6

Expert Comment

by:mcp111
ID: 37033163
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
 
LVL 1

Author Comment

by:cheryl9063
ID: 37033524
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
 
LVL 6

Accepted Solution

by:
mcp111 earned 2000 total points
ID: 37034207
0
 
LVL 1

Author Closing Comment

by:cheryl9063
ID: 37038797
Thanks for all your help!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preface This is the third article about the EE Collaborative Login Project. A Better Website Login System (http://www.experts-exchange.com/A_2902.html) introduces the Login System and shows how to implement a login page. The EE Collaborative Logi…
Browsers only know CSS so your awesome SASS code needs to be translated into normal CSS. Here I'll try to explain what you should aim for in order to take full advantage of SASS.
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question