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

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
insi01Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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
insi01Author Commented:
Hi Vitor,

Thank you very much!

Is there a blank between the two inverted comma?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
No spaces. You can Copy & Paste if you want.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

insi01Author Commented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
insi01Author Commented:
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
James0628Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
insi01Author Commented:
@James0628
That worked, I will check now the complete data flow.

Coming back to you asap

insi01
0
insi01Author Commented:
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
James0628Commented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
James0628Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.