Solved

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

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

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 46

Expert Comment

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

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 46

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
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.

 
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 46

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Problem to open text file 11 68
SQL Query 34 80
Export import database 4 41
MSSQL: Replace text (typo) 7 28
Lessons learned during ten years of interviewing for SQL Server Integration Services (SSIS) and other Extract-Transform-Load (ETL) contract roles and two years of staff manager interviewing contractors.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

914 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now