?
Solved

SSIS: The expression results must be Boolean for a Conditional Split

Posted on 2014-09-25
12
Medium Priority
?
1,732 Views
Last Modified: 2016-02-11
Hello,

I created a SSIS package in Visual Studio 2008.
In the Data Flow tab I've add a source that does an import of Active Directory user with
a specified set of LDAP attributes.

In the second step I add a conditional split with the condition:
FINDSTRING(distinguishedName,"OU=technic",1) > 0

I add this also as screenshot here (condisplit.jpg).


When I run now this package, I receive an error in the Execution Results tab:

[OU Filter [110]] Error: The expression "FINDSTRING(distinguishedName,"OU=DE",1) > 0" on "output "Filtered" (271)" evaluated to NULL, but the "component "OU Filter" (110)" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row).  The expression results must be Boolean for a Conditional Split.  A NULL expression result is an error.

And

[OU Filter [110]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "component "OU Filter" (110)" failed because error code 0xC020902B occurred, and the error row disposition on "output "Filtered" (271)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.

And

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "OU Filter" (110) failed with error code 0xC0209029 while processing input "Conditional Split Input" (111). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.


I don't know how I can fix this, maybe one of you can help me.

KR

insi01
conditsplit.JPG
0
Comment
Question by:insi01
  • 5
  • 4
  • 3
12 Comments
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 40345597
That means that you have rows where distinguishedName is null.
Try to use the ISNULL function:
FINDSTRING(ISNULL(distinguishedName,''),"OU=technic",1) > 0

Open in new window

0
 

Author Comment

by:insi01
ID: 40345673
Hi Vitor,

Thank you very much!

Is there a blank between the two inverted comma?
0
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 40345676
No spaces. You can Copy & Paste if you want.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:insi01
ID: 40345680
Hi,

Ok, done, but get this error:


TITLE: Microsoft Visual Studio
------------------------------

Error at Data Flow Task [OU Filter [110]]: Parsing the expression "FINDSTRING(ISNULL(distinguishedName,''),"OU=technic",1) > 0" failed. The single quotation mark at line number "1", character number "37", was not expected.

Error at Data Flow Task [OU Filter [110]]: Cannot parse the expression "FINDSTRING(ISNULL(distinguishedName,''),"OU=technic",1) > 0". The expression was not valid, or there is an out-of-memory error.

Error at Data Flow Task [OU Filter [110]]: The expression "FINDSTRING(ISNULL(distinguishedName,''),"OU=technic",1) > 0" on "output "FILTERED" (1067)" is not valid.

Error at Data Flow Task [OU Filter [110]]: Failed to set property "Expression" on "output "FILTERED" (1067)".

------------------------------
ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap)
CondSpliterror.JPG
0
 
LVL 53

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 1800 total points
ID: 40345683
Looks like in SSIS it's different from SQL Server Engine.

Found an article that could explain how it works. Please try this one:
FINDSTRING( (ISNULL(distinguishedName) ? '' : distinguishedName)   ,"OU=technic",1) > 0

Open in new window

0
 

Author Comment

by:insi01
ID: 40345691
NO, I received this error:

Error at Data Flow Task [OU Filter [110]]: Parsing the expression "FINDSTRING( (ISNULL(distinguishedName) ? '' : distinguishedName)   ,"OU=technic",1) > 0" failed. The single quotation mark at line number "1", character number "42", was not expected.

Error at Data Flow Task [OU Filter [110]]: Cannot parse the expression "FINDSTRING( (ISNULL(distinguishedName) ? '' : distinguishedName)   ,"OU=technic",1) > 0". The expression was not valid, or there is an out-of-memory error.

Error at Data Flow Task [OU Filter [110]]: The expression "FINDSTRING( (ISNULL(distinguishedName) ? '' : distinguishedName)   ,"OU=technic",1) > 0" on "output "FILTERED" (1067)" is not valid.

Error at Data Flow Task [OU Filter [110]]: Failed to set property "Expression" on "output "FILTERED" (1067)".
-----------------------------
ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap)
0
 
LVL 35

Accepted Solution

by:
James0628 earned 200 total points
ID: 40345703
This is just a guess, but try replacing the two single quotes after the question mark with two double quotes.

FINDSTRING( (ISNULL(distinguishedName) ? "" : distinguishedName)   ,"OU=technic",1) > 0

 James
0
 

Author Comment

by:insi01
ID: 40345706
@James0628
That worked, I will check now the complete data flow.

Coming back to you asap

insi01
0
 

Author Comment

by:insi01
ID: 40345738
Works perfect, thank you!

I would like to ask you a second question in a second Case.
Is it possible that you can Help me as well?


I would assign 100 Points to Vitor and 400 to you James.

Is this fair for you?

insi
0
 
LVL 35

Expert Comment

by:James0628
ID: 40345843
I don't need any points.  Vitor did all of the work.  I just posted a small correction/adjustment.  At the very least, he should get most of the points.  But, really, I don't need any.

 As for asking another question, if it's a separate issue, you should probably start a new question (which may also be more likely to get the attention of new/more people).

 James
0
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 40345851
Thanks James.

Points are not the main reason for we being here. Experts are volunteers so we are here because we like to help and also to learn. In this case I learnt that SSIS treats the ISNULL in a different way of the SQL Server engine.

I think I need to give points to Insi01 as well :)
0
 
LVL 35

Expert Comment

by:James0628
ID: 40346038
Points are not the main reason for we being here. Experts are volunteers so we are here because we like to help and also to learn.

 Agreed, on all points.

 James
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

621 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