Solved

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

Posted on 2014-09-25
12
1,443 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 48

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 48

Expert Comment

by:Vitor Montalvão
ID: 40345676
No spaces. You can Copy & Paste if you want.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 48

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 450 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 34

Accepted Solution

by:
James0628 earned 50 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 34

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 48

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 34

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Suggested Solutions

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

820 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