Division In A Precedence Constraint Expression Does Not Work Properly - Huh?
Dec 13, 2006
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...
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.
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.
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?
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?
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?
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"?
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??
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.
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?
I have (6) sequence containers, each with a data flow task and script task. The script task renames the file and the data flow uses an OLE DB Source connection to export to a flat file using the Flat File Destination connection.For sI have been unable to move from the 1st sequence container to the 2nd (and the next and so on...). I am trying to do this "on success" (on failure I generate an email notification within each container), but am apparently missing a piece to this because after the sequence container executes, which it does successfully, nothing happens.
(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?
I want to return a BIT from an outer join. I want the result type to be BIT. However, the usage of ISNULL is forbidden by code standards. The first expression returns an INT. Which of the bottom expressions do you think would work best in a query?
And why would ISNULL be absolutely forbidden? I was told that Microsoft deprecated it along with TINYINT, which I'm often prevented from using even when it's the appropriate data type.
I am creating one SSIS package where my source is oracle. I have transferred the data from Oracle to flat file as per client requirement.I have to create single package for 2 country 1 is US and another is CANADA Columns are below
ZONE_ID, ZONE_NAME Zone Id having data like 10001,10002,10003,20001,2002,2003
Where zone_id start with 1000 is US Zone and Zone_Id start with 2000 is Canada Zone.
For US: 1. Load geography data from DB tables into flat files 2. Load geography data from flat files to Spectrum DB tables
For Canada: 1. Load geography data from DB tables into flat files 2. Load geography data from flat files to Spectrum DB tables
Now I want to look from flat file if Zone_id start with 1000 then it must go to US_DFT and if Zone_id start with 2000 then it must go to CANADA_DFT.
I've my SSIS Packages, having multiple Configuration stored in SQL Server Table named [SSIS Configurations].
And it's devloped on BHUDEV Server ON Devlopmentdb Database. So durring development Default Cofigurations are set for BHUDEV Server.
Problem is that when I install my Package on SQL Server on different Server for example JOHN, and now my database is ProductionDB and [SSIS Configurations] table will be on ProductionDB database. And I've made required changes in [SSIS Configurations] table. So when I run the Package even then it picks configuration information from BHUDEV.Developmentdb.dbo.[SSIS Configurations] table. That's my problem.
But If when during execution time, I change the connection information then it works fine.
Is SQL 2000 clustering on windows 2000 any good ??
We are looking at spending *quite* a bit of money to implement it, but I need opinions of what its like from the people that actually use it & look after it.
e.g.
DOES IT WORK LIKE IT SHOULD???????? Is it reliable? Is it resource hungry? Are there lots of bugs?
All repsonses very welcome. No response too small.
hi guyz i am trying to trap the duplicate records . cannot trap the below dupes any idea. i also used ltrim and rtrim it still thinking it is a duplicate but it is not. what is the best way doing it.
select count(*) , muzenbr from muzealbums_1 where muzenbr = '1000082' group by MuzeNbr, PNOTES, CNOTES, CAT3, Performer having count(*) > 1
MuzeNbr nvarchar no 14 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS PNOTES nvarchar no -1 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS CNOTES nvarchar no -1 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS CAT3 nvarchar no -1 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS Performer nvarchar no 510 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
Does sseutil work properly on vista? I need a way to programmatically detach a user instance on Vista. The following launches and runs ok on XP. Doesn't run on Vista? Is there some problem with Process on Vista? sseutil runs ok from the command line in Vista.
public void DetachDatabase(string filePath) { ProcessStartInfo startInfo = new ProcessStartInfo("SSEUtil.exe"); startInfo.WindowStyle = ProcessWindowStyle.Normal; //.Hidden; startInfo.Arguments = "-d "" + filePath + """; Process detachProcess = Process.Start(startInfo); while(!detachProcess.HasExited) { Thread.Sleep(1000); }
//we're not using system shell to process execution
startInfo.UseShellExecute = false;
//adding start info
setupProcess.StartInfo = startInfo;
setupProcess.EnableRaisingEvents = true;
setupProcess.OutputDataReceived += new DataReceivedEventHandler(setupProcess_OutputDataReceived);
setupProcess.ErrorDataReceived += new DataReceivedEventHandler(setupProcess_ErrorDataReceived);
setupProcess.Exited += new EventHandler(setupProcess_Exited);
//running the process
return (setupProcess.Start());
}
private string BuildCommandLine()
{
string strCommandLine = null;
MessageBox.Show("SQL");
if (this.sqlSecurityMode)
{
strCommandLine = "/qb";
strCommandLine += " INSTANCENAME=SQLEXPRESS";
strCommandLine += " ADDLOCAL=All";
strCommandLine += " SECURITYMODE=SQL";
strCommandLine += " SQLACCOUNT=" + sqlAccount;
strCommandLine += " SQLPASSWORD=" + sqlPassword;
strCommandLine += " AGTACCOUNT=" + sqlAccount;
strCommandLine += " AGTPASSWORD=" + sqlPassword;
strCommandLine += " ASACCOUNT=" + sqlAccount;
strCommandLine += " ASPASSWORD=" + sqlPassword;
strCommandLine += " SAPWD=" + SysadminPassword;
return strCommandLine;
}
else
{
return strCommandLine;
}
}
When I specify command with literal value it works fine but when I try to use function or variable installer prompt error message:
SQL Server Setup could not validate the service accounts. Either the service accounts have not been provided for all of the services being installed, or the specified username or password is incorrect. For each service, specify a valid username, password, and domain, or specify a built-in system account.
I don't know why it's wrong and be gracefull for help
I try to go to a command prompt to get this to run right with no luck. Is there something that I am not doing right? It gives me the help library, but thats about it. Also, I have dealt with Oracle in the past. And was wondering is there a way to access the SQLCMD utility on a workstation with a login of some kind?
Hello all,The followinq qurey returns sometimes values of null to some of this columns, byK,byT,byD. the column F wil not contains any nulls, and 0 will be populated in it at any case of .Now, the problem is that when sorting out F the sort will not work when there is null parameters in byK because teh fact that a 0 values is greater then NULL value, and the sort of F will not take in considiration. So I guess the question is how can I sort NULL values and 0 values to be the same weight in the sort by command? SELECT A.gym_id as gym,s_id, week, gym_name, boxer, league, sum(points) points,sum(byK)as byK, sum(byT) as byT,sum(byPoints) as byPoints , sum(byD) as byD, count(C.gym) as F FROM A inner JOIN B ON A.gym_id = B.gym_id left JOIN C ON A.gym_id = C.gym WHERE (B.l_id = ?text group by A.gym_id order by points DESC,byK DESC,byT DESC, byPoints DESC, byD DESC,F ASC
Hi Friends "I wonder why the boolean values Set to "Disable Property" using expressions does not work as expected (though Precedence constraints work fine). I find no way to disable individual task. "
I have a boolen variable V, (False) enabled by default at design time for a task T, I disable T based on a condition (if A ! = 'a') during runtime.
Now first time when I run the Package, Task gets disabled (works fine on the condition given). However T gets permanently disabled even after we stop debug mode, which is not the expected behavior. Further on when you run, it wont work at all.
Is it true that a task disabled at design time cannot be enabled during runtime via expressions ?
If things are working fine in your case, pls let me know what settings are required.
Hello, I tried to use bookmarks in OLEDB consumer templates and I have problems with using MoveToBookmark. This method sets cursor position correctly on bookmarked row, but it seems to me it doesn't set the right cursor position in DB. When I call MovePrev after MoveToBookmark, I will not get previous row of the bookmarked row. Simplified Example - I get bookmark of the first row, then move to the 3th row. Call MoveToBookmark (current row is no the first row). But when I call MovePrev I'will not get DB_S_ENDOFROWSET but the second row:
MoveFirst() bk = GetBookmark() MoveNext() MoveNext() // Now I'm on third row MoveToBookmark(bk) GetData() - Now I get data from 1st row MovePrev() GetData() - Now I get data from the second row!!
All calls to OLEDB returs S_OK. Does somebody now, whats wrong?
I was trying to enter a constraint expression into the dialog box. I have three fields of the data type "bit". I want to make sure that of the three, only one equals "1". Using the documentation I found in Books Online, I attempted to use the following expression, but it would not validate.
if i have a table which defines a rule as "combination of two fieldmust be unique", how can I write this in a constraint expressionsection?i started learning more about ms sql side to handle all the necessaryrules in back-end instead of front-end.also any good learning links, references, or book recommandations?thanks
Hi!I have a table with a check constraint. But unfortunately it does notwork like I wanted.CREATE TABLE MAP([R_ID] [T_D_ID] NOT NULL,[R_ID1] [T_D_ID] NULL,CONSTRAINT CHECK_ID1 CHECK (R_ID1 = R_ID OR R_ID1 = NULL),CONSTRAINT [PK_MAP] PRIMARY KEY ([R_ID]))R_ID1 should always have the value of R_ID or NullThe following statements should cause errors:insert into map (R_ID, R_ID1)values(1,2);update map set R_ID1=3 where R_ID=1;But there occur no errors. Does anyone have an idea? It is an SQL Server2000.TIASusanne
Code Block WITH YesterdayCTE AS ( SELECT type = 'Members Joined Yesterday' , Borrowers = (select count(*) from LoanApplication INNER JOIN Member ON LoanApplication.MemberFK = Member.Id AND LoanApplication.Id = Member.LastLoanApplicationFK INNER JOIN CreditUnion ON Member.CreditUnionFK = CreditUnion.Id where (LoanApplication.SubmittedOn >= GETDATE()-1) AND (Member.CuStatus = 'Approved') GROUP BY CreditUnion.Name ) , Depositors = (select count(*) from CDOrder INNER JOIN Member ON CDOrder.MemberFK = Member.Id AND CDOrder.Id = Member.LastCDOrderFK INNER JOIN CreditUnion ON Member.CreditUnionFK = CreditUnion.Id where (CDOrder.SubmittedOn >= GETDATE()-1) AND (Member.CuStatus = 'Approved') GROUP BY CreditUnion.Name )
),
MonthlyCTE AS
( SELECT type = 'Members Joined Last Month' , Borrowers = (select count(*) from LoanApplication INNER JOIN Member ON LoanApplication.MemberFK = Member.Id AND LoanApplication.Id = Member.LastLoanApplicationFK INNER JOIN CreditUnion ON Member.CreditUnionFK = CreditUnion.Id where (LoanApplication.SubmittedOn >= GETDATE()-30) AND (Member.CuStatus = 'Approved') GROUP BY CreditUnion.Name )
, Depositors = (select count(*) from CDOrder INNER JOIN Member ON CDOrder.MemberFK = Member.Id AND CDOrder.Id = Member.LastCDOrderFK INNER JOIN CreditUnion ON Member.CreditUnionFK = CreditUnion.Id where (CDOrder.SubmittedOn >= GETDATE()-30) AND (Member.CuStatus = 'Approved') GROUP BY CreditUnion.Name )
),
YearlyCTE AS
( SELECT type = 'Members Joined Last year' , Borrowers = (select count(*) from LoanApplication INNER JOIN Member ON LoanApplication.MemberFK = Member.Id AND LoanApplication.Id = Member.LastLoanApplicationFK INNER JOIN CreditUnion ON Member.CreditUnionFK = CreditUnion.Id where (LoanApplication.SubmittedOn >= GETDATE()-360) AND (Member.CuStatus = 'Approved') GROUP BY CreditUnion.Name ) , Depositors = (select count(*) from CDOrder INNER JOIN Member ON CDOrder.MemberFK = Member.Id AND CDOrder.Id = Member.LastCDOrderFK INNER JOIN CreditUnion ON Member.CreditUnionFK = CreditUnion.Id where (CDOrder.SubmittedOn >= GETDATE()-360) AND (Member.CuStatus = 'Approved') GROUP BY CreditUnion.Name )
),
combinedCTE AS
( SELECT * FROM YesterdayCTE UNION ALL SELECT * FROM MonthlyCTE UNION ALL SELECT * FROM YearlyCTE
)
SELECT * , Members = Borrowers + Depositors FROM combinedCTE
But I get the following error message.
An error occurred while reading data from the query result set. Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. (Microsoft Report Designer) =================================== Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. (.Net SqlClient Data Provider)
