?
Solved

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

Posted on 2014-09-25
12
Medium Priority
?
1,601 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
12 Comments
 
LVL 51

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 51

Expert Comment

by:Vitor Montalvão
ID: 40345676
No spaces. You can Copy & Paste if you want.
0
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 

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 51

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 51

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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even w…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Suggested Courses

770 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