Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Multiple Expressions In Precedence Constraints


Is it possible in a precedence constraint to have multiple expressions evaluated?  IE

 

Operation set to Expression and expression to evaluate is: @File = x or @File = y

 

 

Thanks


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Precedence Constraint Expressions
 

Hi all
I've got a package that checks the length of chars in a flatfile and if it's correct it executes the dataflow task.
 
How can i write an expression in the precedense constraint editor to skip the dataflow task if there is an error in the script task?
 
 
Thank you

View Replies !   View Related
Precedence Constraints
Hello,
I've recently begun exploring Integration Services and have a question regarding a Control Flow element.
 
I'm trying to understand precedence constraints.  Specificially, the logical AND and logic OR components.  If I have two constaints A and B where A is using a logical AND and B is using a logical OR, how are the constraints evaluated?  The Evaluation Operation is set to "Expression and Constraint" for both constraints.
 
If my understanding is correct, path A would be followed if both A AND B are true.  Path B would be followed if A OR B is true.     Is this correct?
 
Regards,
Orlanzo

View Replies !   View Related
Disappearing Precedence Constraints
Has anyone seen precedence constraints disappear in a package after closing and opening again? In this case, it's not package-wide. Only constraints inside one Foreach Loop container disappeared. Any idea what causes this to happen?
 
p.s: Yes, I did Save All. Most of these contraints were saved in the package for more than a week anyway.
 

View Replies !   View Related
Problem With Precedence Constraints
Hello,

I have a package that executes 2 sql tasks then another SSIS package.  My precedence constraints between sql task 1 and sql task 2, and sql task 2 and the SSIS package check that @ErrorValue == 0. 

@ErrorValue is set after each sql task (both of which execute stored procs) with the value of the output parameter for the stored proc.  I have run both stored procs on the server and both return 0 in the output parameter. 

When I run the package the two sql tasks execute as expected (tasks turn green indicating success) but the package stops at that point and the SSIS package task never executes. 

If I remove the second sql task and connect the first sql task to the package task with the @ErrorValue == 0 precendence constraint the package task still does not execute (even though the constraint worked to get the sql task 2 to execute).

The package only runs if I remove the expression in the second precedence constraint (between sql task 2 and the package) thus changing it to simply 'on success'. 

Please help!

View Replies !   View Related
Objects In Precedence Constraints?
Is it possible to use objects in precedence constraints?  For example, I have a collection object that may or may not be populated in a script at some point and want to be able to execute different paths depending upon whether the Count property equals zero or not.

I realize I could very easily just create another int variable and populate it accordingly, but I am just curious about this.  I couldn't get it to work, but then I am really just starting out here and just not sure if I am doing it wrong.

Thanks!

Brandon

View Replies !   View Related
Conditional Workflow Via Precedence Constraints
 I have three control flow tasks that are executing in consecutive order.  Tasks 1 and 3 will always execute, but sometimes (based on an expression) task 2 will not.  I would like to use precedence constraints in such a way that task 3 will execute regardless of whether task 2 executes, but in the event task 2 does execute, task 3 will only execute AFTER task 2 completes.  Is there a way to accomplish this without setting the disabled property of task 2 at runtime?

View Replies !   View Related
ScriptTask Not Running With Precedence Constraints
I have created a task that uses a ForEachFile Loop. Within that loop I load 5 files. Once those 5 files have loaded successfully I want to run a stored procedure. I placed this stored procedure into a script task. The script task has 5 precedence constraints,. They are a success on the load of the files. When I run the task, the 5 files load, but the script task will not start. I have gone so far as to check each load individually to see if it would start the script task, which they do. It's only when I add more than 1 precedence constraint that the script task will not run. The precedence constraints are set to AND, so they all are required. They all succeed. What am I doing wrong?

View Replies !   View Related
Precedence Constraints And Sequence Containers
I have about 12 sequence containers mapped out to execute separately based on a precedence contraint and an expression. These lie at the same level and order in my sequence. Only one of these will execute based on an expression. After any of these executes, I have a consecutive sequence container that I'm attempting to execute. I've set the precedence contraint from all twelve of the prior sequence containers to this single sequence container that I would like to run after any of the 12. My problem is that the package will only allow one of these twelve sequence containers to become a precedence of this second single sequence container at a time. The package will let me graphically attach the precedence constraint from all 12 to this single sequence container, but when the package runs, it fails to follow through to this single sequence container. I'm trying to figure out why this is the case and how I can get what I would like to work -- work. Thanks.

View Replies !   View Related
Strange Issue With Precedence Constraints And Set Values
Hey everyone,
I have used the set values option of the execute package utility many times to change values on variables in the package with no problem before, however have run into a stumbling block when coupling this with precedence constraints.  I have a package that goes down a different path based on a precedence constraint that works perfectly if the default value of a variable is used....however when I pass in a new value via Set Values in the execute package utility the package will error out with this:

The expression "@[User::IVR_FLAG] == 1" must evaluate to True or False.  Change the expression to evaluate to a Boolean value.

IVR_FLAG is an int with a default value of 1, and if left that way(or changing it to another value in the package, as long as the default is used) this error will not occur....it will go down the proper path.  But when Set Values is used to run the package to change IVR_FLAG, the above error occurs, which doesn't make sense since the above expression should still evaluate to either true or false.  Anyone have any thoughts or suggetions?
Thanks,
Adrian

View Replies !   View Related
SSIS Loses Layout Of Precedence Constraints When Saving.
This is more a medium level annoyance than a problem. When I save a package, close it and later re-open it, the precendence constraints end up all over the place making my neatly layed out diagram a shambles.

Call me anally retentive if you like (you wont be the first ), but I find this extremely annoying. Does anybody else have this problem? Does anyone have a solution?

 

View Replies !   View Related
Failure Precedence Constraints Stopped Working In My Package
I had a Send email task linked to my Sequence Containers in my package and it was working fine. Everytime the container fails it would send an email to myself.

At some point all Failure constraints stopped working. Failure constraints work if I add brand new tasks, but with the existing tasks, they don't work. The Task which fails, turns red and execution stops. Next failure task is not executed. 

I am not sure what triggered it to stop working. I cannot get anything on the log

Any help is appreciated.

 

 

View Replies !   View Related
Adding Precedence To Multiple Files
hi all,

i have a package here which updates a DB from a flat file source.now the problem is i may get multiple files.i have used a for each loop to handle this. it takes files based on the files name9(file names has a timestamp in it).but i want to give files in order of its Creation time.

Please help me on this.i have written a script task before the for each loop and i have got the minimum creation date from all the files,i am not able to going forward from here.

does any body has an idea!!

View Replies !   View Related
Multiple Expressions.
Good Morning all.

Is it possible to put multiple expressions in one cell.

 

Here is an example of the expressions I'm using. I'm currently having to put them horizontally in a seperate cell.

 

=Count(IIf (Trim(Fields!NOB_Pickup_L_D_T.Value) = "Y", 1, Nothing))

=Count(IIf (Trim(Fields!NOB_Pickup_L_D_T.Value) = "N", 1, Nothing))

=Count(IIf (Trim(Fields!NOB_Pickup_L_D_T.Value) = "NA", 1, Nothing))

 

Desired output will look sim to this in one cell

 

 

 

Y = 5

N = 3

NA = 0

 

Thanks,

Rick

 

View Replies !   View Related
Multiple Expressions.
Good Morning all.

Is it possible to put multiple expressions in one cell.

 

Here is an example of the expressions I'm using. I'm currently having to put them horizontally in a seperate cell.

 

=Count(IIf (Trim(Fields!NOB_Pickup_L_D_T.Value) = "Y", 1, Nothing))

=Count(IIf (Trim(Fields!NOB_Pickup_L_D_T.Value) = "N", 1, Nothing))

=Count(IIf (Trim(Fields!NOB_Pickup_L_D_T.Value) = "NA", 1, Nothing))

 

Desired output will look sim to this in one cell

 

 

 

Y = 5

N = 3

NA = 0

 

Thanks,

Rick

 

View Replies !   View Related
Dataset Filtering With Multiple Expressions
I would like to know how to dynamically change the And/Or value on the Filter tab within a dataset.
 
Here are my two expressions within the filter:
 



Code Snippet
=Switch(Parameters!TotalCostFilterOp.Value = "=",Fields!tcost.Value = Parameters!TotalCost.Value,Parameters!TotalCostFilterOp.Value = ">",Fields!tcost.Value > Parameters!TotalCost.Value,Parameters!TotalCostFilterOp.Value = ">=",Fields!tcost.Value >= Parameters!TotalCost.Value,Parameters!TotalCostFilterOp.Value = "between",Fields!tcost.Value >= Parameters!TotalCost.Value AND Fields!tcost.Value <= Parameters!TotalCost2.Value,Parameters!TotalCostFilterOp.Value = "ie", ISNothing(Fields!tcost.Value), Parameters!TotalCostFilterOp.Value= "<",Fields!tcost.Value < Parameters!TotalCost.Value,Parameters!TotalCostFilterOp.Value = "<=",Fields!tcost.Value <= Parameters!TotalCost.Value,Parameters!TotalCostFilterOp.Value = "<>",Fields!tcost.Value <> Parameters!TotalCost.Value)
 
 



 



Code Snippet
=Switch(Parameters!CompanyFilterOp.Value = "%",LCASE(Fields!company.Value) like "*" & LCASE(Parameters!Company.Value) & "*", Parameters!CompanyFilterOp.Value = "=",Fields!company.Value = Parameters!Company.Value, Parameters!CompanyFilterOp.Value = "ie", ISNothing(Fields!company.Value), Parameters!CompanyFilterOp.Value = "?", LCASE(Fields!company.Value) LIKE LCASE(Parameters!Company.Value), Parameters!CompanyFilterOp.Value = "<>", Fields!company.Value <> Parameters!Company.Value, Parameters!CompanyFilterOp.Value = "?%", LCASE(Fields!company.Value) LIKE LCASE(Parameters!Company.Value) & "*")
 
 



 
The operator for both these is set to "=", and the value is set to "=true".
 
Based on user selection in a report wizard, the 4th column (And/Or) may need to be changed from 'and' to 'or', or vice-versa.
Is there anyway to accomplish this programatically?
 
Thanks in advance for you help!

View Replies !   View Related
Creating Expressions For Multiple Choices
 

Hi,
 
I have to create an expression that makes bold the text for some possibilities, otherwise the font will keep same, is there any staff for creating an expression like "IF ID_Name IN (x,y,z...) THEN bla-bla" like querying in SQL? Cuz I have to use multiple possibilites for same condition

View Replies !   View Related
Multiple Select And Sum Expressions On The Same Table SQL…??
Little complex but I'll try to explain the best I canI have the following table…
 
UserID___Year___Month_____A1____A2____B1____B2
__1_______4______5______45_____5____43____0
__1_______4______6______10_____12___1_____3
__1_______5______5______12_____22___8_____7
__1_______5______6______11_____10___5_____0
 
__2_______4______5______9______65___33____66
__2_______4______6______3______3____10____11
__2_______5______5______44_____2___77_____1
 
 
I want to pull out the data to show the sum of the totals for each year the user is in. The idea is to then be able to see a comparison for year on year on a month by month basis
Eg.
 
UserID____TotalAYear4____TotalBYear4___TotalAYear5____TotalBYear5___Month
__1___________50_____________43___________34____________15__________5
__1___________22_____________4____________21____________5___________6
__2___________74_____________99___________21____________5___________5
 
 
There would be no data returned for UserID2 for month 6 as there is no data for them for year 5 month 6 therefore no comparison can be made.
 
I’ve having real difficulty getting the correct SQL syntax to do this. Any ideas or pointers would be great thanks

View Replies !   View Related
Problems With Multiple Constraints
Hi all,
 
I am having a problem setting multiple constraints over a table from other table. To be more clear, I have the following code:
 
ALTER TABLE Solicitud_Servicio
            DROP CONSTRAINT FK_Solicitud_Servicio_Domicilio;
 
ALTER TABLE Solicitud_Servicio
            DROP CONSTRAINT FK_Solicitud_Servicio_Domicilio1;
 
ALTER TABLE Solicitud_Servicio ADD CONSTRAINT
            FK_Solicitud_Servicio_Domicilio1 FOREIGN KEY
            (
            ID_Domicilio_Envio
            ) REFERENCES Domicilio
            (
            ID_Domicilio
            ) ON UPDATE CASCADE;
 
ALTER TABLE Solicitud_Servicio ADD CONSTRAINT
            FK_Solicitud_Servicio_Domicilio FOREIGN KEY
            (
            ID_Domicilio_Facturacion
            ) REFERENCES Domicilio
            (
            ID_Domicilio
            ) ON UPDATE CASCADE;
 
As you see, I want an update on the field Domicilio.ID_Domicilio to change the fields Solicitud_Servicio.ID_Domicilio_Envio and Solicitud_Servicio.ID_Domicilio_Facturacion from my point of view there should be no problem and the weird part is that if I try this with only one constraint (either one) it works, as soon as I try to apply the other constraint I get a very annoying
 
€œThe referential relationship will result in a cyclical reference that is not allowed€?
 
Maybe Sql Server Mobile 2005 does not support multiple constraints on a table from the same other table?
 
Thanks,
 
Flaker

View Replies !   View Related
Select Query - Multiple Constraints In One Field
Hi all,

I want to retrieve the datas from the table with condition if DetailID is not null and DetailID is not guid.empty then it return only the DetailID = @DetailID

can any one help on this.

Thanks,
Sajith

View Replies !   View Related
Valid Expressions Are Constants, Constant Expressions, And (in Some Contexts) Variables. Column Names Are Not Permitted.
I want to have this query insert a bunch of XML but i get this error...
 

Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 117

The name "ExpenseRptID" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 151

The name "DateWorked" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
 
What am i doing wrong...Can anyone help me out!! Thanks!!
 
p.s I know this query looks crazy...
 

Code Block
 
IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME = 'InsertTimeCard' AND type = 'P' AND uid=(Select uid from sysusers where name=current_user))
BEGIN
DROP PROCEDURE InsertTimeCard
END
go
/*********************************************************************************************************
** PROC NAME : InsertTimeCardHoursWorked
**
** AUTHOR : Demetrius Powers
**
** TODO/ISSUES
** ------------------------------------------------------------------------------------
**
**
** MODIFICATIONS
** ------------------------------------------------------------------------------------
** Name Date Comment
** ------------------------------------------------------------------------------------
** Powers 12/11/2007 -Initial Creation
*********************************************************************************************************/
CREATE PROCEDURE InsertTimeCard
@DateCreated DateTime,
@EmployeeID int,
@DateEntered DateTime,
@SerializedXML text,
@Result int output
as
declare @NewTimeCardID int
select @NewTimeCardID = max(TimeCardID) from OPS_TimeCards
-- proc settings
SET NOCOUNT ON
 
-- local variables
DECLARE @intDoc int
DECLARE @bolOpen bit
SET @bolOpen = 0
--Prepare the XML document to be loaded
EXEC sp_xml_preparedocument @intDoc OUTPUT, @SerializedXML
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
--The document was prepared so set the boolean indicator so we know to close it if an error occurs.
SET @bolOpen = 1
 
 
--Create temp variable to store values inthe XML document
DECLARE @tempXMLTimeCardExpense TABLE
(
TimeCardExpenseID int not null identity(1,1),
TimeCardID int,
ExpenseRptID int,
ExpenseDate datetime,
ProjectID int,
ExpenseDescription nvarchar(510),
ExpenseAmount money,
ExpenseCodeID int,
AttachedRct bit,
SubmittoExpRep bit
)
DECLARE @tempXMLTimeCardWorked TABLE
(
TimeCardDetailID int not null identity(1,1),
TimeCardID int,
DateWorked DateTime,
ProjectID int,
WorkDescription nvarchar(510),
BillableHours float,
BillingRate money,
WorkCodeID int,
Location nvarchar(50)
)
-- begin trans
BEGIN TRANSACTION
insert OPS_TimeCards(NewTimeCardID, DateCreated, EmployeeID, DateEntered, Paid)
values (@NewTimeCardID, @DateCreated, @EmployeeID, @DateEntered, 0)
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
 
 
--Now use @intDoc with XPATH style queries on the XML
INSERT @tempXMLTimeCardExpense (TimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep)
SELECT @NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep
FROM OPENXML(@intDoc, '/ArrayOfTimeCardExpense/TimeCardExpense', 2)
WITH ( ExpenseRptID int 'ExpenseRptID',
ExpenseDate datetime 'ExpenseDate',
ProjectID int 'ProjectID',
ExpenseDescription nvarchar(510) 'ExpenseDescription',
ExpenseAmount money 'ExpenseAmount',
ExpenseCodeID int 'ExpenseCodeID',
AttachedRct bit 'AttachedRct',
SubmittoExpRep bit 'SubmittoExpRep')
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
 
-- remove XML doc from memory
EXEC sp_xml_removedocument @intDoc
SET @bolOpen = 0
 
 
INSERT OPS_TimeCardExpenses(TimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep)
Values(@NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep)
select @NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep
from @tempXMLTimeCardExpense
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
 
-- For time worked...
INSERT @tempXMLTimeCardWorked(TimeCardID, DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
SELECT @NewTimeCardID, DateWorked, ProjectID, WorkDescription, BilliableHours, BillingRate, WorkCodeID, Location
FROM OPENXML(@intDoc, '/ArrayOfTimeCardWorked/TimeCardWorked', 2)
WITH ( DateWorked DateTime 'DateWorked',
ProjectID datetime 'ProjectID',
WorkDescription nvarchar(max) 'WorkDescription',
BilliableHours float 'BilliableHours',
BillingRate money 'BillingRate',
WorkCodeID int 'WorkCodeID',
Location nvarchar(50)'Location')
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
 
-- remove XML doc from memory
EXEC sp_xml_removedocument @intDoc
SET @bolOpen = 0
 
 
INSERT OPS_TimeCardHours(TimeCardID, DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
Values(@NewTimeCardID,DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
select @NewTimeCardID ,DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location
from @tempXMLTimeCardWorked
 
 
-- commit transaction, and exit
COMMIT TRANSACTION
set @Result = @NewTimeCardID
RETURN 0
 
-- Error Handler
ErrorHandler:
-- see if transaction is open
IF @@TRANCOUNT > 0
BEGIN
-- rollback tran
ROLLBACK TRANSACTION
END
-- set failure values
SET @Result = -1
RETURN -1
 
go
 
 

View Replies !   View Related
Failed To Enable Constraints. One Or More Rows Contain Values Violating Non-null, Unique, Or Foreign-key Constraints.
Hi,
    I am getting the above error when trying to load a report into my Web Application, I have tracked the error down to one specific field in my database. Even though this field is a NVarChar field and is of size 30 it would seem that  there is an issue returning the value from the field. I can write it into the database no problems but when I try to get it out of the database it returns the above error.
e.g
MOB 401.908.804 - Fails
0401.907.324 - okay
8239 9082 (pager) - fails
Anyone got an idea on how to fix this????
Regards..
Peter.

View Replies !   View Related
UGH! Failed To Enable Constraints. One Or More Rows Contain Values Violating Non-null, Unique, Or Foreign-key Constraints.
I know this is probably a flick of a switch but I cannot figure out which switch.  Setup is SQL Server / Stored Procedures / DAL / BLL(skipped for testing) / PL.  The stored procedure queries from only one table and two columns are ignored because they are being phased out.  I can run the stored procedure and preview the data in the DAL but when I create a page with an ODS linked to the DAL and a GridView I get this error.  I checked every column that does not allow nulls and they all have values.  I checked unique columns (ID is the only unique and is Identity=Yes in the table definition).  I checked foreign-key columns for values that are not in the foreign table and there are none.  Any ideas why do I get this? 
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

View Replies !   View Related
GROUP BY Precedence
How is the column precedence determined when more then one column is included in the group by clause? I am using T-SQL.

for example:

SELECT VendorName, VendorState, SUM(Invoices.InvoiceTotal) AS InvoiceSum
FROM Vendors JOIN Invoices ON
Vendors.VendorID = Invoices.InvoiceID
GROUP BY VendorState, VendorName

I would expect this statement to group by VendorState first but my result set is grouping by VendorName and then VendorState.

Am I missing something?

View Replies !   View Related
Precedence Constrains
Hi there!

I've a few tasks in my dataflow. Some of them are executed depending on an precedence constrain. Works fine.

The problem is following: I want to write a success message (execute SQL task), when all the tasks who have been executed have done this with success. For example, i ve ten tasks, and 2 of them are executed (successfully). This is good, i want to execute my task to write the success message.

I tried to set these precedence constraints with EvalOP = ExpressionOrConstraint, Values = Success and an expression, what returns TRUE, when the task above is not executed (the condition from a precedence constraint above). But the task to write my message will never be executed, because it seems the task does'nt give any result (neither success nor failure) when they ar not executed, and so my AND-Constraint for the success message task will only get TRUE, when ALL task are executed (and successful).

Is there any chance to get somethink like "isSuccess OR IsNotExecuted" into a precedence constraint?

Thanks, Torsten

View Replies !   View Related
Precedence Puzzle
Hello folks,

I have probably a dumb newbie question but I can't find the answer anywhere.

On my Control Flow design pane I have two objects:  a SQL task object and a Data flow task object.  The first 'points' to the second.  From my digging I believed that by indicating with the arrow from 1 to 2 that 1 would execute to finish before 2 was started.

My SQL taks is to truncate a table to receive the new data coming from the data flow task object.  Instead 2 executes first and then 1.  You can imagine producing an empty table was not my goal for this package.

Can anybody give me a clue?

Thanks....

Jim

View Replies !   View Related
Precedence Constraint Being Ignored?
hi all - this is the first time i've worked with SSIS / Business Intelligence, and I'm having an issue with a piece of basic functinoality.

I'm working with some dtsx jobs which were all conversions from Sql Server 2000 DTS jobs. The conversion process "seemed" to go smoothly. I used the Migration Wizard, and it successfully converted my outputted DTS job files into DTSX files.

In the Business Intelligence design environment (Control Flow) for a specific dtsx package, I have two tasks that are linked together by a Precedence Constraint.

* Task A Truncates all data in Database B --> Table A
* the precedence constraint says that if Task A is successful, then go on to Task B (or at least i think it does)
* Task B copies the newest data from Database A --> Table A over to Database B --> Table A

Everything appears to be correct, but if i right-click Task A and perform an "Execute Task" command, Task A will run successfully, but then stops short without running Task B.

Are there any common "gotchas" or issues that I might be missing which would cause this behavior? I deleted the existing precedence constraint, and added back a new one with the same settings, but Task B still will not run.

thanks for any help on this

View Replies !   View Related
String Matching Precedence
An application filters records based on names found in them. Forexample, looking through property buyer names, looking for banks andrelocation companies.I have a table of names and patterns:CREATE TABLE #Filters (Pattern varchar(100), IfWildCard int, Categoryint)INSERT #Filters SELECT 'Bank Of America', 0, 1INSERT #Filters SELECT '% Bank %', 1, 2INSERT #Filters SELECT 'Bank %', 1, 2INSERT #Filters SELECT '% Bank', 1, 2INSERT #Filters SELECT 'Credant Reloc%', 1, 3INSERT #Filters SELECT '%Relocation%, 1, 3The filtering matches the table of candidate names against the filters,and returns Category, where the where clause or join is(Candidate = Pattern AND IfWildCard = 0)OR (Candidate LIKE Pattern AND IfWildCard = 1)"Bank of America" matches an exact pattern, and also a wildcardpattern, and the two different matches give different values forCategory. Is there a way to control which match takes precedence, oris necessary to do it multiple times in the desired order, removingthose that hae already matched from consideration?Thanks,Jim Geissman

View Replies !   View Related
Operator Precedence Challenge
I'm having problems with the query below. It works fine until the "NOT IN" part and I'm not sure why. Basically, I'm getting records where their educ_audio field is set to "no". I've messed with the parens to try to force SQL to process the NOT IN part before the other clauses without avail. Can someone shed some light?

TIA

SELECT distinct contacts.fname, contacts.lname, contacts.company, contacts.contact_id, contacts.business_phone, contacts.emailAddress, contacts.dateLastContact FROM journal INNER JOIN contacts ON journal.contact_id = contacts.contact_id INNER JOIN products ON journal.product_code = products.product_code WHERE ( journal.product_code IN ('ABLE') ) OR (( journal.product_code IN ('JOBS') ) AND ( journal.product_status IN ('12','14','15') )) OR (( products.prod_design IN ('audio') ) AND ( products.library_code IN ('hrss') )) AND (journal.journal_id NOT IN (SELECT journal.journal_id FROM journal INNER JOIN contacts ON journal.contact_id = contacts.contact_id INNER JOIN products ON products.product_code = journal.product_code where ( contacts.educ_audio IN ('no') ) )) ORDER BY contacts.lname asc, contacts.fname

View Replies !   View Related
Precedence Constraint Problem
I have two packages - one works, the other doesn't. Both have two tasks: an Execute SQL Task and a dependent Send Mail Task. I am using a precedence constraint where the evaluation operation is an expression evaluating a variable created by a select count(*) as variable in the query. Both variables are defined as int32, initial value of 0 with a scope for the entire package.

The expression that works tests for an error_counts > 0 condition; the one that doesn't tests for load_counts < 24. The > 0 sends mail when the error_count is greater than 0 but doesn't send mail when it is 0, which is as I intended. The less than 24 package always sends mail even though the variable resultset_singlerow is usually 24. When I look at both properties for the Execute SQL Task steps side by side, they have no differences. When I look at the properties for the constraint, the only difference is in the EvaluatesTrue, which is set to False for the one that works and True for the one that doesn't. Unfortunately, that property is grayed out (not modifiable) and doesn't seem to be an option when I edit via the Precedence Constraint Editor.

I've looked a Books Online & Google with no references to a similar situation. Does anyone have a solution? Is the EvaluatesTrue the problem? And if it is, how can I change the setting? If not, what is the problem?

Thanks in advance for any help.

View Replies !   View Related
Workflow - Precedence Constraint
 

Hi,
 
I have 3 data flows connected sequentially on my workflow before processing dimensions and facts  which checks data from different databases and if conditions does not meet then writes to log table. So if it writes to log table , I have to quit and finish the workflow.
How can I do that?

 
thanks,
J

View Replies !   View Related
Precedence Constraint Issue
 

I am struggling here with precendece constraints and variables.
 
I have the following scenario:
 






Task A





Task B





Task C





@var1!=0





@var1==0
Task A, Task B and TaskC
 
Task A (Execute SQL Task which assigns a value to a variable say, var1) determines if TaskB will execute i.e.
 
IF var1 !=0 Execute Task B  and then Execute Task C
 
IF var1== Execute Task C
 
Is this possible in SSIS?
 
Problem:
Each time I try to do this, the package never executes Task C. It always either Executes Task A or Task A and Task B.

 
For example if var1 !=0 it executes Task A and Task B
 
but if var1 == 0 it executes only Task A.
 
 

View Replies !   View Related
Precedence Constraint Problem
I have two packages - one works, the other doesn't. Both have two tasks: an Execute SQL Task and a dependent Send Mail Task. I am using a precedence constraint where the evaluation operation is an expression evaluating a variable created by a select count(*)  as variable in the query. Both variables are  defined as int32, initial value of 0 with a scope for the entire package. The expression that works tests for an error_counts > 0 condition; the one that doesn't tests for load_counts < 24. The > 0 sends mail when the error_count is greater than 0 but doesn't send mail when it is 0, which is as I intended. The less than 24 package always sends mail even though the variable resultset_singlerow is usually 24.

When I look at both properties for the Execute SQL Task steps side by side, they have no differences. When I look at the properties for the constraint, the only difference is in the EvaluatesTrue, which is set to False for the one that works and True for the one that doesn't. Unfortunately, that property is greyed out (not modifiable) and doesn't seem to be an option when I edit via the Precedence Constraint Editor.

I've looked a Books Online & Google with no references to a similar situation.  Does anyone have a solution?  Is the EvaluatesTrue the problem? And if it is, how can I change the setting?

Thanks in advance for any help.

View Replies !   View Related
Problem With Precedence Expression
Hello,
I have a SQL Task the executes the following statement;
 
SELECT COUNT(SettleDate) AS CountResult
FROM         SettleDateCount
 
I have the SQL Task Editor configured for a Single Row result set. The Result Name is CountResult, and the variable it populates is RowCountRes. RowCountRes is Int32 with EvaluateAsExpression set to False.
 
I have a boolean variable called RowCountStatus. It evaluates as the following expression;
 
@[User::RowCountRes] > 1
 
From the SQL Task, I have two different constraints, both of which evaluate with an expression. One constraint evaluates this way; @[User::RowCountStatus] = True
The other evaluates this way; @[User::RowCountStatus] = False
 
The idea is that if the count comes back from the sql statement greater than 1 then execution continues down one path, else it goes down the other path. Currently, the result comes back = 1. I expect that the 'False' path will execute, but the 'True' path executes instead.
 
What am I doing wrong?
 
Thank you for your help!
 
cdun2

View Replies !   View Related
SSIS Precedence Constrains Not Showing
I have DTSX package where all the Control Flow Boxes are connected by precedence constraints (the Green Lines).
However, only half of them visible!
If I try and add another it says:

Cannot create connector.
Only one workflow can exist between the same two executables.
I don't want to have to drop and create them all the constraints (because there are a lot of them!)
But I can't even do that - since I can't see them, I can't select them; and I don't think I can delete them without being able to select them. Am I missing something? Zooming in makes everything larger, but it doesn't make my Green Lines reappear.
Help!
 
And yes, I'm using the latest version of everything:-
 
Microsoft Visual Studio 2005
Version 8.9.50727.762 (SP.050727-7600)
 
SQL SERVER 2005
9.00.3054.00 SP2 Developer Edition

View Replies !   View Related
Qestion On Precedence Constraint Editor
 I am confused with Precedence constraint editor regarding Evaluation Operation and Multiple Constraints options. In the Evaluation Operation dropdown list, there are: Expression and Constraint and Expression OR Constraint. Why it still needs LOGICAL AND  and LOGICAL OR in the multiple constraints options? Is it clear that Expression and Constraint is "LOGICAL AND" and "Expression OR Constraint" is "LOGICAL OR"?

View Replies !   View Related
SSIS - Precedence Constraint Editor
 

Dear Friends,
In my controlFlow I have 2 sequence container... Sequence1 goes to Sequence2 if an expression valuate to TRUE.
If the expression value is true, than there isn's any problem and sequence2 runs, but if the expression is FALSE the sequence2 container doesn't run.
 
OK?
In the case of expression valueate to FALSE I need to generate an error or an information in order to get it in the eventhandler automatic...
I can add a script task to make the package failure... but what I insert in the precedence constraint editor from Sequence1 to the scripttask?
 
Imagine... the expression from Sequence1 to Sequence2 is a>=b
The expression from Sequence1 to ScriotTask os a<b? (How can I define this only to run when the expression (seq1 to Seq2) is false??
 
 
 
Someone know how?
Regards!

View Replies !   View Related
DTS Execute Package Task Step Precedence Bug
This is a bug that I submitted to Microsoft. I wanted to see if anyone could assist me with a work-around.

1. Create a new DTS Package
2. Add an ActiveX Task and change 'Main = DTSTaskExecResult_Success' to 'Main = DTSTaskExecResult_Failure'.
3. Running this package will result in a Failed package.
4. Save Package as 'Failure' to local server

5. Create another new DTS Package.
6. Add an 'Execute Package' task, and configure the properties to have it launch the saved 'Failure' child package.
7. Add an ActiveX task and then click ok to close it.
8. Select the Execute Package task (which executes the failure package), hold control and click the ActiveX task. Add an 'On Success' Precendence constraint from the 'Workflow' menu.

9. Now execute the package. Notice that the ActiveX task will execute even though the Execute Package task FAILED.

10. If you change the Precendence constraint from 'On Success' to 'On Failure' the ActiveX task will not run, because the package thinks that the Execute Package task completed with success.

In summary... if a child package, executed by an Execute Package task fails, the Execute Package Task ALWAYS "reports" success to the following precedence constraint and the package continues to run. Even if 'Fail Package on first error' is enabled. Although you will see that the task actually failed while watching the status of the package execution.

I am currently trying to write vbscript in an ActiveX task that will follow an Execute Package Task. I am trying to make the vbscript check and see if the previous task really did succeed or fail, and then make the ActiveX task succeed or fail as required to make the precedence constraints work properly.

I have replicated this problem on many different machines.

---------------------------------------------------------------------------

Any ideas for my ActiveX task? I am trying to access the StepExecutionResult from the Execute Package task, and I have been unable to. If I can get the Step Execution reult, then I can in turn cause the ActiveX task to suceed or fail, based on the execution result of the Execute Package task.

Thank you for your time,

David Zebrowitz
dzebrowitz@apartners.com

View Replies !   View Related
Maintenance Place Precedence Constraint Editor
SQL Server 2005 now provides the ability to use expressions in the plans instead of just the Failed/Success/Completed constraints.

Can anyone tell me what language is used for these expressions. Where can I find a guide on this?



Thanks


Drew

View Replies !   View Related
Failure Precedence On Nested Sequence Containers
 I have a Sequence containser(named One) and 2 Sequence containers( named two and three) nested inside container One. In Container two and three I have execute SQL tasks to execute Stored Procedure. Then I have send email task linked to my sequence containter One on failure constraint.

Failure contrainst to send email is not working. I want each sequence containters(two and three) to execute SQL tasks and if one fails wait for other to execute and then failure task should execute.

Any help is appreciated.

Amar

 

 

 

 

View Replies !   View Related
How To Avoid Precedence Constraint Lines From Getting Distorted
Hi,
 
Whenever I open my package, the Precedence Constraint lines get distorted. This makes my package look messy. Is there any way to stop this from happening?
 
Thanks for your help!
Subha
 
 

View Replies !   View Related
Precedence Constraint In SSIS Didn't Work
Dear all,
    I've been searching the article for error handling in SSIS but seems no article have same problem exactly as mine. In my package there's container, it contains Data Flow task and some of Script tasks. I put an error precedence from Data Flow task into Script task that contains script for log an error that might be occured. The Data Flow task imports data from flat file into SQL Server 2005 and I've set a semicolon as the column delimiter. Unfortunately there is a data in flat file that had one column contain semicolon as its value.
    This would trigger an error and I hope the error would be logged into a table as I wrote inside Script task. But it didn't work. The error precedence won't work, the package stop in flat file source instead. I've been trying the event handler but it didn't work either. Maybe I got wrong implementation, anybody can help me explain the error handler and solve the problem ?
    Here is the capture of my package, since I didn't know how to attach the picture in this forum.
    Thanks in advance.
 
 
Best regards,
 
Hery

View Replies !   View Related
How To Enforce Precedence At Data Flow Level?
I wish to use the same data to update 2 different tables.

There is no green arrow output from the OLE DB data destination, so I can't have another component following on from the first insert.

This means I have to use the Multicast to 'copy' the data prior to the first table insert.

I can then use the data to perform inserts to both tables.

However, there is an FK constraint between these two tables, so I need to wait until the first table insert has finished before performing the second table insert.

How can I do this? How can I make the second insert dependent on the first?

View Replies !   View Related
Establishing Precedence In ORDERBY Condition Causing Problems.
Hi.I really need some advice on fine-tuning a stored procedurethat is the meat of the search logic on my site. Customersare allowed to save searches, which dumps the search logicin a table called SavedSearches for later access to the search.My problem started with the ORDERBY condition used for zipcodesearches. The condition did something like:"order by CASE WHEN userID=67 THEN 1 WHEN userID=103 THEN 2 WHENuserID=102 THEN 3 WHEN userID=81 THEN 4"Of course, this fails when a customer described in the saved searchresults deletes his profile.I have since attempted to brace against this problem by adding aUserPrecendence table with the following columns: email_address,up_order (or, user precedence order), and userID.Since I have made the precedence changes, I have been unsuccessful ingetting any results (data) back from the query. I think it has to dowith the change but am not quite sure what I am doing wrong.I would appreciate it is somebody could take a look at my sproc withparticular attention to how precedence is handled in the ORDERBYcondition. Maybe you can see something I can not?As always, much appreciated.PS - In addition to the UserPrecedence change, I have attempted to addpaging - returning N amount of pages per request based on passed-inparamaters. I'd appreciate it if you could take a quick glance herealso just to make sure my logic is OK.----------------------------------------------------------------------------------------ALTER PROCEDURE [dbo].[sp_PeopleSearch]@pagenum INT = 1,@perpage INT = 10ASBEGINSET NOCOUNT ONDECLARE@ubound INT,@lbound INT,@pages INT,@rows INTSELECT@rows = COUNT(*),@pages = COUNT(*) / @perpageFROM(select distinct emailAddressfrom Customers with(nolock) union select distinct user_namefrom CustomerPhotos with(nolock) union select distinct email_addressfrom EditProfile with(nolock) union select distinct email_addressfrom SavedSearches with(nolock) union select distinct email_addressfrom UserPrecedence with(nolock) union select distinct email_addressfrom RecentLogin with(nolock)) drvLeft Join Customers tab1 on (drv.emailAddress = tab1.emailAddress)Inner Join UserPrecedence tab5 on tab5.UserID=tab1.UserIDLeft Join CustomerPhotos tab2 on (drv.emailAddress = tab2.user_name)Left Join RecentLogin tab4 on (drv.emailAddress =tab4.email_address)Left Join EditProfile tab3 on (drv.emailAddress = tab3.email_address)Left Join SavedSearches tab6 on (drv.emailAddress =tab6.email_address)IF @rows % @perpage != 0 SET @pages = @pages + 1IF @pagenum @pages SET @pagenum = @pagesIF @pagenum < 1 SET @pagenum = 1SET @ubound = @perpage * @pagenumSET @lbound = @ubound - (@perpage - 1)SELECTCurrentPage = @pagenum,PageSize = @perpage,TotalPages = @pages,TotalRows = @rows,UpperBoundary = @ubound,LowerBoundary = @lbound-- this method determines the string values-- for the first desired row, then sets the-- rowcount to get it, plus the next n rowsDECLARE@gender VARCHAR(50),@country VARCHAR(50),@orderby INTEGER,@low VARCHAR(50),@high VARCHAR(50),@photo VARCHAR(50),@sort INTEGERSET ROWCOUNT @lboundSELECT@gender = saved_sex,@country = saved_country,@orderby = saved_orderby,@low = saved_fage,@high = saved_tage,@sort = saved_sort,@photo = saved_photo_stringFROM(select distinct emailAddressfrom Customers with(nolock) union select distinct user_namefrom CustomerPhotos with(nolock) union select distinct email_addressfrom EditProfile with(nolock) union select distinct email_addressfrom SavedSearches with(nolock) union select distinct email_addressfrom UserPrecedence with(nolock) union select distinct email_addressfrom RecentLogin with(nolock)) drvLeft Join Customers tab1 on (drv.emailAddress = tab1.emailAddress)Inner Join UserPrecedence tab5 on tab5.UserID=tab1.UserIDLeft Join CustomerPhotos tab2 on (drv.emailAddress = tab2.user_name)Left Join RecentLogin tab4 on (drv.emailAddress =tab4.email_address)Left Join EditProfile tab3 on (drv.emailAddress = tab3.email_address)Left Join SavedSearches tab6 on (drv.emailAddress =tab6.email_address)ORDER BY CASE @sortWHEN 1 THEN tab1.registerDateWHEN 2 THEN tab3.edit_dateWHEN 3 THEN tab4.login_dateWHEN 4 THEN tab5.up_orderEND DESCSET ROWCOUNT @perPageSELECT COALESCE(tab1.emailAddress,tab2.user_name,tab3.email_address,tab4.email_address,tab5.email_address,tab6.email_address)id ,tab1.bday_day ,tab1.bday_month ,tab1.bday_year ,tab1.gender ,tab1.zipCode ,tab1.siteId ,tab1.userID ,tab2.photo_location ,tab2.photo_name ,tab2.photo_default ,tab2.no_photo ,tab3.headline ,tab3.about_me ,tab4.login_date ,tab4.login_isonline,tab5.up_order,tab6.saved_orderby,tab6.saved_sort,tab6.saved_fage,tab6.saved_tageFROM(select distinct emailAddressfrom Customers with(nolock) union select distinct user_namefrom CustomerPhotos with(nolock) union select distinct email_addressfrom EditProfile with(nolock) union select distinct email_addressfrom SavedSearches with(nolock) union select distinct email_addressfrom UserPrecedence with(nolock) union select distinct email_addressfrom RecentLogin with(nolock)) drvLeft Join Customers tab1 on (drv.emailAddress = tab1.emailAddress)Inner Join UserPrecedence tab5 on tab5.UserID=tab1.UserIDLeft Join CustomerPhotos tab2 on (drv.emailAddress = tab2.user_name)Left Join RecentLogin tab4 on (drv.emailAddress =tab4.email_address)Left Join EditProfile tab3 on (drv.emailAddress = tab3.email_address)Left Join SavedSearches tab6 on (drv.emailAddress =tab6.email_address)WHEREtab1.gender = @genderAND tab1.country = @countryAND tab1.bday_year BETWEEN @low AND @highAND tab2.photo_default = 1 + @photo--and not tab2.no_photo = 1--firstName + '~' + lastName-->= @fname + '~' + @lnameORDER BY CASE @sortWHEN 1 THEN tab1.registerDateWHEN 2 THEN tab3.edit_dateWHEN 3 THEN tab4.login_dateWHEN 4 THEN tab5.up_orderEND DESCSET ROWCOUNT 0END

View Replies !   View Related
Division In A Precedence Constraint Expression Does Not Work Properly - Huh?
Greetings,

I have an expression in a precedence constraint that is returning false when it should return true.  This is the expression that returns false:

((5500 / 9990) * 100) > 10

The following expression returns true.  I did the division (5500 / 9990) myself and substituted the resulting value:

((0.55055055055055055055055055055055) * 100) > 10

Why is the first expression returning false?  I'm stuck in the mud up to my axles on this and I know I'll probably feel like a fool when I learn the answer...

Thanks,

BCB

View Replies !   View Related
SSIS Upward Precedence Execution Flow Issues
Hi,
 
I'm building a package wherein I perform a SQL task(A) if the error log is not empty. This same SQL task(A) is also being used by another data flow task(B). The precedence points from B to A bottom to top. When I execute, all the tasks in the downward direction (precedence pointing downward/sideways) execute but this one doesnt as it points updwards.  I can copy and paste task A and make B point to A downward, but I dont want to duplicate A in the same package.
 
Is there any other approach?
 
If you dont understand the above, see the flow:
 
   X (SQL task) ----on success-------------> A (SQL task)
   |                                                             ^
   |                                                             |
    ...(sequence of steps)                              | 
   |                                                             |
   |                                                             |
   B(Data Flow Task) ---------failure --------|
 
Execution flow doesnt move from B to A, even though its a failure condition. Hope this explains the problem.
 
Thanks and Regards,
Subha Fernando

View Replies !   View Related
Checking The Validity Of A System.Object In A Precedence Constraint?
I have a Package that is being invoked by a master package. In the master package I have defined a recordset object as a variable which I populate with a SQL Command.  From the child package I want to verify that the parent recordset is valid, and if it is not I want to generate it on my own with the same SQL Command. 

 

Basically I want to have the ability to pass in a variable resultset into a child package and be able to validate whether its good or not, and if it isnt make it good by pointing it to the results of a sql command.

 

 

View Replies !   View Related
Enforcing Precedence Constraint While NOT Failing Package(On Failure Logic Ignored)
I have designed an SSIS package that :
 
(1) contains a for loop task (in which all the logic is contained) that loops through a particular folder for excel files
WITHIN THE FOR LOOP:
(2) pulls data from an excel file into SQL Tables(Data Transformation Task)
(3) run stored proc to validate data (Execute SQL TASK)
(4)ON SUCCESS of executing the SQL Task (Script Task - move file to success or reject folder based on value returned from sproc)
(5)ON FAILURE of executing the stored proc (Script Task - move file to bad format failure)
 
NOTE:
I have modified the MaximumErrorcount property of (1 FOR LOOP) and (3 EXECUTE SQL TASK) and the package itself to 0.
In order to deal with badly formatted excel files...I do not want the package to stop for every missing tab in excel file or data entry error. I simply want the badly formatted file to be moved to a special folder
 
PROBLEM:
on failure logic is never executed (I have 2 options after step (3)) on success do step (4) on failure do step (5)
step (3) fails...then it simply iterates to the next file
step 5 is never executed
 
Is this because I changed the maximumerrorcount property?
What am I doing wrong witht he Precendence Logic?

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved