.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Conditional Split according to commas

Posted By:      Posted Date: October 22, 2010    Points: 0   Category :Sql Server

Hi Guys,


Source Input:

H, 2, 240190, 08, 50, 01, 02, 2009, complete

H, 1, 353474, 06, 52, 01, 01, 2009

H, 1, 1197222, 07, 05, 01, 04, 2010, complete, Sale, 28.20, 21.80


I need a conditional split expression to split above rows according to the , count. Count of commas are 7, 8 and 11.

So I need to split above lines into three separate files.


All I need is to split above lines using comma count, I amusing below expression but its not working.


FINDSTRING([Column 0],",",7) == 0

FINDSTRING([Column 0],",",8) > 0 && FINDSTRING([Column 0],",",8) < 8

FINDSTRING([Column 0],",",11) > 0 && FINDSTRING([Column 0],",",8) == 11


Thanks in Advance,



View Complete Post

More Related Resource Links

Using Conditional Split data Transfer in SSIS 2008

This article uses the Integration Services Conditional Split Data Transformation element to filter and transfer data from a set of flat text files to SQL Server database table. The concept can be easily extended to apply to any other source or destination such as Microsoft Excel. This scenario is useful in creating denormalized database tables in a reporting and analysis situation.

Using User Variables (ONLY) in SSIS Conditional Split

After considerable effort I have not been able to perform a successful conditional split on a user variable. The idea is that I am reading a flat file, and I place the number of rows read into a variable (variable A). I then do some data conversion and perform another row count (variable B) I would like to do a conditional split if the value of variable A <> value of variable B. I am not referring to any of the query columns in making this comparison. It is always evaluating to the value of the variables before the package is run. For example if variable a=0 and variable b=0 at the start of the package, the conditional split evaluates to true (since 0=0) What I am failing to see is a's value getting updated and b's value getting updated in the conditional split. I have added watches for the variable and they are updating, but the boolean a==b is not. Any help is appreciated.

how to use split in iif conditional expression in ssrs 2008 r2



how can i use conditional iif combined with split?

i'm trying to get values from check boxes(from sharepoint custom list), the condition is like this :

if the value is null/blank, the return value would be "-"

if the value is not blank, the return value would be the value of the check box field that already splitted from the ";#" character

as you might know, values from check box is written along with ";#" character before and after the value itself.

i tried to use this expression:

=IIF(Fields!Action__For_User_Access_.Value Is Nothing," - ",(Split(Fields!Action__For_User_Access_.Value, ";#")(1)))

the split worked just fine, but the conditional iif didn't work.

can anybody help me?

i used that expression in the calculated field.

btw, i'm using report builder content type from ssrs 2008 r2


thx in advance.



Conditional Split



I have a problem using conditional split,

I've got the raw txt data that i need to insert into sql database, having set the delimiter as ",", i encountered a problem;

Raw data example,



00101,Andrew, Jackson, Nurse


Data inserted to SQL ended up to be...

ID Name Job
00100 Harris Student
00101 Andrew Jackson ,Nurse

But when i initiate the conditional

If it is inserted correctly to the SQL Database,

It should be... (Take note of "Jackson")

Conditional split?


Hi All,

I have a package which imports several files on a regular bases . For the import I use a staging table. Now I want to split that stagingtable into two other (existing) tables with keeping the relation ( third table??) . In each record there is a dealerID which has to be injected also in the other two tables (the splittables).

How can I use a conditional split to extract the right columns to the right table? I only see that i can enter a expression for each column, but in fact i only want to split the table by "fingerpointing" the right columns. is that possible? And how should i put the relation between them in 'relation' table?


I hope someone van help me,


Thanks in advance,




Conditional Split Error


Hi Guys,

Source Files - String Datatype



H, 1, 353474, 06, 52, 01, 01, 2009

C, 4

I, MV6106, 1, 3.85, , 3.85, R,  , 

T, 65496, -0.35

T, 1, 20.00

H, 1, 353475, 06, 53, 01, 01, 2009

C, 4

Reference: Cashout_TX_353476

I, 30159, 1, 1.00, , 1.00, R,  , 

I, 458287, 1, 6.91, , 6.91, R,  , 

I, 394085, 1, 2.15, , 2.15, R,  , 

I, MV1293, 1, 2.80, , 2.80, R,  , 

I, 132700, 1, 6.54, , 6.54, R,  , 

T, 65496, -0.88

T, 8, 19.40

H, 1, 353476, 06, 53,

Conditional Split Function Error


Hi Guys,

Source Files - String Datatype


H, 1, 353474, 06, 52, 01, 01, 2009

C, 4

I, MV6106, 1, 3.85, , 3.85, R,  , 

T, 65496, -0.35

T, 1, 20.00

H, 1, 353475, 06, 53, 01, 01, 2009

C, 4

Reference: Cashout_TX_353476

I, 30159, 1, 1.00, , 1.00, R,  , 

I, 458287, 1, 6.91, , 6.91, R,  , 

I, 394085, 1, 2.15, , 2.15, R,  , 

I, MV1293, 1, 2.80, , 2.80, R,  , 

I, 132700, 1, 6.54, , 6.54, R,  , 

T, 65496, -0.88

T, 8, 19.40

H, 1, 353476, 06, 53, 01, 01, 2009

C, 4


I am using condition split to get only H rows. 

SUBSTRING([Column 0],1,FINDSTRING([Column 0],",",1) - 1) == "H"




[H ROWS ONLY [54]] Error: An error occurred while evaluating the function.


[Conditional Split [54]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. 

 The "component "Conditional Split" (54)" failed because error code 0xC0049067 occurred,

 and the error row disposition on "output "HRows" (63)" specifies failure on error. 

 An error

Adding exception table to incremental Load with SSIS Lookup task and conditional split



I have built an incremental load ETL Process to load some flat files with an SSIS Lookup and Conditional Split. I only have one path in the conditional split and that is for New Records.

I have two questions:

1.       For the new records path, I have to check to see if a record exists and I don’t have a single key that is unique, therefore, I have to use a multiple keys to make the record unique.


Below is what I have put in the conditional transformation editor for my first output condition:


DateTime Validation in SSIS using conditional split



I am facing issue with null validation for DateTime. I have input csv file which contains DateTime field as Date datatype. In the destination database table, I am storing this field as datetime. I am using conditional split feature of SSIS package and try to validate this field before inserting into the database.

The value which is going into the db is "1899-12-30 00:00:00.000". I dont know why?

I have tried various expressions like ISNULL(DateTime), LEN(DTSTR, DateTime) == 0, ISNULL(DateTime) ? False : DateTime. Nothing is working.

Req is simple, if date is valid insert in db otherwise write is as bad data in error log file.

Can anyone provide suggestion on this.




Conditional Split Expression


Hi Guys,

My input columns are 

TotalSales                         Trans_Type

-0.10000000000000001 S

0.10000000000000001 S

-0.10000000000000001 V

-0.20000000000000001 S

I need to separate all the negative rows, according to trans_type. I am using below expression none of them are working.

Expression used:

Trans_Type = S

DataType = String

TotalAmount = Negative field

SUBSTRING(TotalSales,1,FINDSTRING(TotalSales,"-",1)) > 0 && Trans_Type == "S"

SUBSTRING(TotalSales,1,FINDSTRING(TotalSales,"-",1)) == "-" && Trans_Type == "S"


Output Required whenever Trans_Type is S and negative in amount I need to slit those row.

TotalSales                      Trans_Type



Testing for Valid Date or Null Value -- Using Conditional Split


I have a Flat File Source with several columns of data that should be valid dates.  Sometimes however they may either be NULL or not valid dates.  I understand the expresstion ISNULL([ColumnName]) will test for the NULL condition.  What is the syntax to test if the value is a valid date?  It might be a string such as "baddate" or it could be a date that is too old.  Could you provide an example of an expression that will test for both of these conditions?




About Conditional Split and Error Control


Hi there,

I need help for output flat files under different conditions.

Currently, I'm using Conditional Split to divide records into different groups. In the case that the particular group doesn't have any records, I wish the following output files won't be executed.

Thanks & Regards,


SSIS Conditional Split


 i created a small DTS flow which is fetching data from OLD DB source to two  flat files using condition given in Conditional Split Transformation


Conditional Spilt has two condition according to source table column as given

Case1      Department == "IT"

Case2      Department == "Management"

Case3      Department == "Sales"

now as per this Case "IT", "Management" and "Sales" i am generating the flatfiles in three location C:\IT , C:\Management , C:\ Sales folder respectevly


this package is running fine but here the logical problem occurs, All the files are generating with accurate records in there respective folders but simultanuasly two other file also generating in all folders. which is not required,

it seems the condition which get satisfied executes and generate the file in there respective folders and fill the record in that file but other 2 case2 is also executing simultaneously and generating blank files

Assistance is highly appreciable.

Thanks in advance






Split Name In SQL Server

One common task performed in database applications is given a full name, how can this be split into the first name and last name. In this article, three different methods are discussed to perform this task using three different functions in SQL Server. It is assumed in this article that the full name is just made up of one first name and one last name.

split with javascript



i have this garbache value %B463822546831472^MILIN JONE/OBAMA         ^1308226223780000731000000?;E?

now i wana slit with javascript and show on my aspx page like this(bold words)




How can i split with java script.plz help in this issue

Inline conditional output


Is it possible to somehow output some content based upon some conditional check in Razor? If not, I hope this possibility will be added in the future. What I want to do is the following:

<option value="1" @if(ListMode == 1) { = "selected=\"true\"" }>Full list</option>

As I can't figure out a way to do this, I have to either make a helper that does it for me or write duplicate code.

Web Set-up Project Conditional Dialogs


Hi all,

I'm currently putting together a web set-up project to deploy a web site on a customer site. What I'd like to be able to do is have different routes through deployment depending on the installation technicians response.

For example asking: Use existing or create new membership database? Then depending on the response prompting for database location etc.

Not sure if this is possible with web set-up projects. I understand about adding custom actions and additional dialog boxes, but cant quite understand how to add custom dialogs, or conditional dialogs.

Can anybody point me in the right direction?


ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend