Conditional split with dependence?

I have setup a SSIS package that takes a flat file fixed width input, and stores it to two SQL server tables in the same database. The flat file contains two types of records, lets call them Type1 and Type2. The two types of records are formatted differently, and the first character determines what type the record is. I used a conditional split to send record type1 down one path, and type2 down the other. On each of those I use a derived column task to build all the fields and then store to the table with the OLE destination. I put any errors that occur (like truncation) into an error table by setting the "redirected row" feature vs "Fail Component". This all works well and I have no issues.

The dilema is as follows. Type1 is essentially a parent record and the Type2 record is a child. There is a shared primary key / foreign key relationship field. I want errors when processing type1 to cause the associated type2 to also be redirected to the error table vs being inserted.

If anyone has suggestions on how this could be done, reference articles, etc... please let me know.

Thanks.

Perhaps use a merge join on the error output of the Type1 flow together with the Type 2 data flow. Then use a conditional split to look for matches. If you have a match, you direct the Type 2 record (along with the Type1 record) down a separate error-handling flow. If you don't have a match, the Type2 records can be processed accordingly.|||

I'm trying your suggestion and I think it will work. But I am having an issue. I have my original flat file source, which I read into the SSIS package as just rows. So I do CRLF search to bring in as one column. I then send it to a derived column component after a conditional split to perform all the "substrings" to get the actual columns out of the data.

In order to do a merge join you must use sorted columns. I was able to set sorted column on the flat file data source and single column, which does me no good. I need to be able to set the sorted column on the derived columns after the data has been put into columns. Is there any way to set the sort column on a derived column? If I can do that it will solve my issues.

Thanks.

Conditional Split Transformation

Hi all,

I have set up a conditional split task which i want to use with a flat file data source. The flat file consists of multiple rows of data where the first column is an ID. The conditional split is based on the first column value.

What i'd like to know is if in the conditional split once it splits the data can the output be transformed. e.g. If one of the values coming from the flat file requires to be either split up into two values or requires to be passed into a stored procedure to manipulate it, can this be done?

Hope that makes sense.

All help is greatly appreciated, TIA.

Cheers,

Grant

Well in least words, YES!

Output of conditional split can be simply passed to any other control to manipulate in whatever way u like

|||Hi,

thanks for the reply. I have just realised what a stupid question it was. I have just dragged a constraint from the conditional loop and see that a dialog box allows you to select the output. My apologies, and thanks for the help.

Cheers,

Grant|||Hi Again,

Out of interest once i have the row of data i want to process, how would i go about doing the actually processing.

The first this i need to do is to pass once of the row values into a stored procedure and return a variable. Whta would be the best command for this. In the control flow i would have used an Execute SQL task, but this doesn't appear to be available.
Do i have to script anything like this once i have the row?

Thanks again,

Grant

Conditional Split Transformation

Hi

Can any one please tell me how do I give multiple conditions in Conditional Split Transformation.

Exp:

I have few columns as

ReturnSUK

TimeSUK

EntitySUK

PeriodSUK

Now the condition should be :

! ISNULL (ReturnSUK) & ! ISNULL (TimeSUK) & ! ISNULL (EntitySUK) &! ISNULL (PeriodSUK)

Please provide me the proper condition for the above mentioned requirement.

Thank you

Use two & symbols:

!ISNULL(ReturnSUK) && !ISNULL(TimeSUK) && ....|||

Thank you Its Working

If i need to give the same condition for OR (^) rather then AND (&) so the condition would be

this :

ISNULL(ReturnSUK) ^ ISNULL(TimeSUK) ^ ISNULL(BankSUK) ^ ISNULL(EntitySUK) ^ ISNULL(PeriodSUK)

or ,can you please help me in this too.

Thank you

|||Or is written by using two || symbols:

TEST1 || TEST2 || TEST3 ....

Conditional Split to send top 10, top20, and top 30 in three different flows ?

I have dataflow that i wish to split. The recordset that i uses is sorted by the Sort Task. After that i'll like to take the Top 10 records and send in one direction, then ill like to take the top 20 and send in another direction and finally Top 30 in a third direction.

How can i do that ?

You can use a script component to give each record a number using the technique described here: http://www.sqlis.com/default.aspx?37

Then you can filter on the values that are created using a conditional split transform component.

Make sense?

-Jamie

|||Yes but it seems to be a bit of a workaround. I was looking for an easier way like using SQL select top 10 * from....|||

cgpl wrote:

Yes but it seems to be a bit of a workaround. I was looking for an easier way like using SQL select top 10 * from....

Yeah I agree, that would be nice. Perhaps you could request it at betaplace or http://lab.msdn.microsoft.com/productfeedback/Default.aspx?

-Jamie|||You could do it all in one script component. Add some extra outputs and then direct the row to the correct output based on the counter value.

I'd demonstrate if I knew what the equivalent of buffer.DirectRow(<outputindex>) was in the script component, but that would be if the syntax for a full component.|||

DarrenSQLIS wrote:

You could do it all in one script component. Add some extra outputs and then direct the row to the correct output based on the counter value.

I'd demonstrate if I knew what the equivalent of buffer.DirectRow(<outputindex>) was in the script component, but that would be if the syntax for a full component.

Oh yeah, Darren's right. And the syntax he's on about is <buffername>.AddRow()

-Jamie|||The easiest way to deal with that, would be if you could disable the "Random" feature in the Row Sample Task. Then you could do a Multicast to 3 different Row Sample Tasks|||

yep, I have the same problem. I have a dataset containing a record for each bill in the past month and I want to flag the top 10 customers according to the aggregated amount pr customer.

Like this in plain SQL:

select top 10 CustName, sum(Amount) as sum_Amount into #ek_temp

from erhverv_kreditnota group by CustName order by sum_Amount

go

update erhverv_kreditnota

set top10 = 'Top 10'

where CustName in (select CustName from #ek_temp)

go

Any way to do this without scripting?

|||

cgpl wrote:

The easiest way to deal with that, would be if you could disable the "Random" feature in the Row Sample Task. Then you could do a Multicast to 3 different Row Sample Tasks

Yeah another excellent idea, you would assume that selecting the first N rows is easier than selecting a random number. Did you submit the idea to Microsoft?

-Jamie|||

jesal wrote:

yep, I have the same problem. I have a dataset containing a record for each bill in the past month and I want to flag the top 10 customers according to the aggregated amount pr customer.

Like this in plain SQL:

select top 10 CustName, sum(Amount) as sum_Amount into #ek_temp

from erhverv_kreditnota group by CustName order by sum_Amount

go

update erhverv_kreditnota

set top10 = 'Top 10'

where CustName in (select CustName from #ek_temp)

go

Any way to do this without scripting?

As explained, no, not really. Is there any reason why you don't want to do this in a script?

-Jamie|||I did!

http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=2bc4ee52-aec6-409d-b39c-6e2fb4945799|||Out of interest, would you want to send:
Rows 1-10 to output1
Rows 1-20 to output2
Rows 1-30 to output3

or

Rows 1-10 to output1
Rows 11-30 to output2
Rows 31-60 to output3

?

I might have a crack at this myself...it sounds quite interesting.

-Jamie|||

I would like the first scenario:

Rows 1-10 to output1
Rows 1-20 to output2
Rows 1-30 to output3

-

There are two reasons why I would like to do this without a script. First I don’t have that much experience with scripting. Second I think that a simple and common task like this should be available “off the shelves”.

I tried scripting based on Jamie’s answer to the thread “Script Transformation” (http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=60464).

1. My problem is that the number of output rows is different from the number of input rows – so I need to limit the script to run on the first 10-30 rows, not the entire recordset.

2. Jamie suggests the syntax <buffername>.AddRow() and Outputbuffer (in the “Script Transformation” thread). I used the default declaration (ByVal Row As Input0Buffer) – but as far as I can see this buffer contains both the input and output and I cannot find Row.AddRow?

Any suggestions to a script that redirects the first 10 rows from input to output?

- Jeppe

|||

jesal wrote:

I would like the first scenario:

Rows 1-10 to output1
Rows 1-20 to output2
Rows 1-30 to output3

-

There are two reasons why I would like to do this without a script. First I don’t have that much experience with scripting. Second I think that a simple and common task like this should be available “off the shelves”.

I tried scripting based on Jamie’s answer to the thread “Script Transformation” (http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=60464).

1. My problem is that the number of output rows is different from the number of input rows – so I need to limit the script to run on the first 10-30 rows, not the entire recordset.

2. Jamie suggests the syntax <buffername>.AddRow() and Outputbuffer (in the “Script Transformation” thread). I used the default declaration (ByVal Row As Input0Buffer) – but as far as I can see this buffer contains both the input and output and I cannot find Row.AddRow?

Any suggestions to a script that redirects the first 10 rows from input to output?

- Jeppe

Jeppe,
This should do it: http://blogs.conchango.com/jamiethomson/archive/2005/07/27/1877.aspx

-Jamie|||Thank you Jamie!

Your "SSIS Nugget: Select Top N in a data-flow" worked great. I have one question though - I couldnt get the "Output0Buffer" to work, but when I changed the SynchronousInputID for the Output to 0 it worked, what is the connection between the declaration of the Output0Buffer and the Synchronous setting?

Thansk again

- Jeppe

Conditional Split to FlatFile

Hi,

I have a Conditional Split to FlatFile Destination.

How can I put the result, that goes in the FlatFile Destination, in a variable also (like in Recordset Destination).

Do I have to runs this thing twise (and put the first time in FlatFile Destination and the second time in Recordset Destination)?

Thank you.

Why do you want to put the results in a variable?

A multicast transformation will let you split the data flow into as many branches as you want.|||

Take a look at the Multicast and conditional split component in the data flow. You could 'branch' the data pipeline and send rows to multiple destinations in one pass.

Conditional split questions

I have a zipcode column that contains xxxxx-xxxx, i want to use conditional split so that i can take the last 4 digits and put them into a different column, I tried to use the SUBSTRING ("ZIP", 6, 4) but it returns an error, any ideas on how i can split it?

Thanks.

Actually, I think you want the derived column transform, not the conditional split. Your SUBSTRING should work fine.|||

I think you want a Derived Column Transfomation too, but you have a couple of mistakes, assuming this is a SSIS expression-

You need to remember the SSIS expression syntax is C style and therefore zero based, so index 7 is the start of the last section, I assume you are skipping the hyphen.

You have used double quotes, this makes it a literal.

Try SUBSTRING(Zip, 7, 4)

Both points don't count if you are still trying to do this on SQL, but I assume you wanted a SSIS solution.

As a rule it helps a lot if you give the details of the error, things like error message, as save a lot of guess work when trying to help you.

Conditional Split Question

Hello,

I am have an ID column that sometimes contains all numeric characters and sometimes contains all digits. I would like to the records with all digits (0-9) to continue downstream in my Data Flow. I would like the records that contain characters other than digits to be logged to a table.

This sounds like a job for the Conditional Split transformation, but I don't see a way to easily test for a numeric value. For example, I would like to use something like ISNUMERIC([MyIDField]) for testing the values in my Conditional Split, but I don't see a way to do this.

Do I have to create a Derived Column transformation prior to my conditional split that populates a "numeric" ID column for each of my records then test this Derived Column in my Conditional Split? Seems like more work than I would to see for something as simple as testing for a numeric...

TIA...

Brian

"numeric characters and sometimes contains all digits"... digits are numeric? Anyway I'd use my Regular Expression Transform http://www.sqlis.com/default.aspx?91. It will handle the test and split, and regular expressions are great for validating things like this.

|||

Brian,

You are correct, there is no ISNUMERIC() function in the expression evaluator. However, in your case, there is a fairly decent workaround I think.

If you are SURE that the string is never a mix of numeric and character data, you could use the following expression to direct alpha strings (where Col is the name of your input column:

FINDSTRING("0123456789", SUBSTRING(Col, 1, 1), 1) == 0

This expresssion will be true if the first character of Col is an alpha character.

Hope this helps.

Mark

Conditional Split Question

I have a package which has a conditional task which directs rows to its respective OLEDB command. The records are sorted from the source system in chronological order. The problem I am experiencing is that some of the operations do not seem to be occurring in the same order. An example of this would be someone inserts a record, deletes the record and reinserts in the record in that order. When we run the package we can see the records are coming down in chronological order but the delete from the split seems to occur after the inserts. Has anyone else experienced this? Is there anything I might be missing to ensure things happen in the order they should? Any advice would be greatly appreciated. Thank you.Can you provide more details surrounding your data flow setup? Try to illustrate to us how you have the data flow built from source all of the way through the destinations (or OLE DB Commands).|||

Bagles1 wrote:

I have a package which has a conditional task which directs rows to its respective OLEDB command. The records are sorted from the source system in chronological order. The problem I am experiencing is that some of the operations do not seem to be occurring in the same order. An example of this would be someone inserts a record, deletes the record and reinserts in the record in that order. When we run the package we can see the records are coming down in chronological order but the delete from the split seems to occur after the inserts. Has anyone else experienced this? Is there anything I might be missing to ensure things happen in the order they should? Any advice would be greatly appreciated. Thank you.

If you have 2 OLE DB Command tasks in the same dataflow you should not rely on the rows being actioned in the order that they enter the pipeline. There is no sychronisation between the two paths. Once the rows are in different paths then they are two seperate streams of data and will be teated as such. If you want to ensure that the deleted happen after the insertions push the data for deletioninto a raw file and issue the deletes from another data-flow.

-Jamie

|||

Jamie Thomson wrote:

If you have 2 OLE DB Command tasks in the same dataflow you should not rely on the rows being actioned in the order that they enter the pipeline. There is no sychronisation between the two paths. Once the rows are in different paths then they are two seperate streams of data and will be teated as such. If you want to ensure that the deleted happen after the insertions push the data for deletioninto a raw file and issue the deletes from another data-flow.

-Jamie

Yep, or create separate data flows, with precedence enforced at the control flow level.|||I have a data reader that pulls data from a staging environment whereas the data is sorted in chronological order. It immediately goes down into a conditional split where a field holds 1 of 3 values; I, U, D (Insert, Update, Delete). From there each condition has a RowCount transformation to count the rows as they pass through and then onto an OLEDB command that performs the necessary operation. The stream stops at the OLEDB command and that is all there is.|||Ugggghhhhh! That hurts.|||

Bagles1 wrote:

I have a data reader that pulls data from a staging environment whereas the data is sorted in chronological order. It immediately goes down into a conditional split where a field holds 1 of 3 values; I, U, D (Insert, Update, Delete). From there each condition has a RowCount transformation to count the rows as they pass through and then onto an OLEDB command that performs the necessary operation. The stream stops at the OLEDB command and that is all there is.

As I said above, there is no guarantee of the order in which rows get processed, especially when you send them to different data paths.

There is also no guarantee that data will actually enter the pipeline from the staging environment in the order that you think it does. There is no concept of a set of data being ordered - there are lot of things that can influence the order that rows appear in the pipeline.

-Jamie

|||

Jamie Thomson wrote:

Bagles1 wrote:

I have a data reader that pulls data from a staging environment whereas the data is sorted in chronological order. It immediately goes down into a conditional split where a field holds 1 of 3 values; I, U, D (Insert, Update, Delete). From there each condition has a RowCount transformation to count the rows as they pass through and then onto an OLEDB command that performs the necessary operation. The stream stops at the OLEDB command and that is all there is.

As I said above, there is no guarantee of the order in which rows get processed, especially when you send them to different data paths.

There is also no guarantee that data will actually enter the pipeline from the staging environment in the order that you think it does. There is no concept of a set of data being ordered - there are lot of things that can influence the order that rows appear in the pipeline.

-Jamie

Jamie,

Are you saying that if I have an Order By statement in my data reader that there is no guarantee that it will actually be in that order or have I misunderstood your statement?

|||

Bagles1 wrote:

Jamie Thomson wrote:

Bagles1 wrote:

I have a data reader that pulls data from a staging environment whereas the data is sorted in chronological order. It immediately goes down into a conditional split where a field holds 1 of 3 values; I, U, D (Insert, Update, Delete). From there each condition has a RowCount transformation to count the rows as they pass through and then onto an OLEDB command that performs the necessary operation. The stream stops at the OLEDB command and that is all there is.

As I said above, there is no guarantee of the order in which rows get processed, especially when you send them to different data paths.

There is also no guarantee that data will actually enter the pipeline from the staging environment in the order that you think it does. There is no concept of a set of data being ordered - there are lot of things that can influence the order that rows appear in the pipeline.

-Jamie

Jamie,

Are you saying that if I have an Order By statement in my data reader that there is no guarantee that it will actually be in that order or have I misunderstood your statement?

Well, YES, the order will be retained until some other downstream data flow component rearranges the order. Surely you can't expect a union all to maintain order, for instance.|||

Bagles1 wrote:

Jamie,

Are you saying that if I have an Order By statement in my data reader that there is no guarantee that it will actually be in that order or have I misunderstood your statement?

In that case then yes, it will enter the pipeline in the order decreed by the ORDER BYstatement but thereafter you should not rely on the ordering within the pipeline. And you should DEFINATELY not rely on rows reaching a destination/OLE DB Command in some order when those rows are in different data paths.

-Jamie

|||Thank you for your explanation. A very painful lesson to learn this late in the project. Unfortunately in our case we have to process the rows in chronological order so it looks like it will be the script component once again.|||

Bagles1 wrote:

Thank you for your explanation. A very painful lesson to learn this late in the project. Unfortunately in our case we have to process the rows in chronological order so it looks like it will be the script component once again.

If this is true then it sounds as though you have some sort of procedural logic going on - that's not really possible with the standard components. Script component may help though.

I don't actually know your requirement but wouldn't it just make more sense to count the number of inserts and deletes per "thing". If there are more inserts than deletes then you insert the "thing", otherwise you don't.

Good luck with it anyway.

-Jamie

Conditional Split query

Hi,

I have the following table in MsAccess

EmployeesA

empId integer,

empName varchar(60),

empAge integer,

empStatus char(1) - can be N,D or S - New, Deleted or Shifted

and the following in Sql2005

EmployeesB

Id smallint,

Name varchar(60),

Age int,

Status char(1) - Bydefault 'N'

I have written a Foreach File package that populates the sql server tables (EmployeesB) from Access(EmployeesA). However i want to check for a condition now.

If empStatus = N in EmployeesA, then insert a new record in EmployeesB

If empStatus = D in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status field in EmployeesB as 'D'

If empStatus = S in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status as 'S' in EmployeesB and insert a new row.

How do I do it for each table each row in EmployeesA using a foreach file loop?

Thanks,

ron

If you are using a data flow inside your For Each, you can use the techniques shown in this thread (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1) to determine whether the row should be inserted or updated. The thread is dicussing specifically whether the row already exists or not, so you may need to add a conditional split to your data flow.

|||

Hi,

thanks for the reply. I had already seen that link. I cannot do a look up as Employees B will already contain millions of rows.

I just want to know this step by step if you could explain. I am so new to this SSIS.

How will I specify conditions :

If empStatus = N in EmployeesA, then insert a new record in EmployeesB

If empStatus = D in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status field in EmployeesB as 'D'

If empStatus = S in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status as 'S' in EmployeesB and insert a new row.

Which control to use. Where to specify etc.

thanks,

|||

Ok,

I have figured out most of it through a friend. Just tell me this:

What i am doing is :

For status D, I do a Lookup and if found, I have to use an OLE DB Command tranform to do the update.

What query do I fire in the look up over here. If that row exists, after that what to do in the OLEDB command. ?How to pass the current row?

thanks.

|||Do you mean how to update the row that was matched in the lookup? Why can you not use the same fields you used in the lookup for the match and put them into the WHERE clause of your update? Sometimes it is cleaner to return a key column or two from the lookup, and them as basis for the WHERE clause.|||

Can you state an example. What should be in the Lookup and what in the OledbCommand based on my table.

Thanks

|||

An example, do you mean for this problem-

If empStatus = D in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status field in EmployeesB as 'D'

I would be tempted to skip the lookup. Use a Conditional Split to get a feed of all EmployeesA rows where empStatus = "D", then use a command to do the update. Set your connection, and end the SQL statement -

UPDATE EmployeesB

SET empStatus = 'D'

WHERE empname = ?

AND age = ?

Map the two input columns empname and age to the two parameters, to complete the OLD-DB Command setup.

This avoids the costs of a lookup, which may be faster overall. If there is no match, then no update happens, which is the same overall outcome as if the lookup had failed to find anything and the command was not run.

It may be faster to use a Lookup to help filter out the non-matches, it really depends on row counts and ratios of lookup hits to misses. Test both if you are worried about performance, but it is often faster to attempt and "fail" than to prevent the "fail" in the first place in SSIS.

|||

Darren,

you know what..that worked like a charm i removed the look up and did as you said..I will try the rest and if everything works, i will close this thread. thankuuuuuu.

If you could have a look at this thread too, I will be much obliged.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2178311&SiteID=1

thanks.

Conditional Split problem

Don't know if anybody else has had this -

In a dataflow task I added a conditional split that currently has two possible outcomes.

Initially this was a straight choice. Later I added an "or" clause on the second leg thus

expression1 || expression2

on which the second leg ceased working.

After some mental anguish I deleted the component and then reimplemented it using identical logic and magically (or so it appears) it all worked.

So, it appears the old tricks still apply...

I don’t suppose you have the package before and after so we can look at
the differences?|||Sorry, no - versions under version control don't have the relevant logic in them. I'll keep an eye out for it in future though...|||

I have had people report something similar, but every time it turned out to be a slight error in the expression that resulted in one output never receiving data. What would be compelling as a bug would be if the number of rows on all outputs (with all outputs connected) did not equal the incoming rows.

If you see it again, please do post more details.

Donald

|||

When I created the new component, I copied the old expressions to Notepad and then pasted them back in again, so your scenario is unlikely (though of course not impossible.)

|||

Another possibility is that, if you have multiple conditional split conditions, they are being applied in a different order from your first implementation.

Donald

conditional split problem

I have a dataflow where i transfer data from textfile to oledb destination

I have a conditional split in between and check if incoming fields are empty.

in the conditional split i have

ISNULL(column1)|| ISNULL(column2) || ISNULL(column3)|| ISNULL(column4) ||ISNULL(5) || column1 == " " || column2 == ""||column3 == " " || column4== " "||column5==" "

this is what i have in my conditional split to check if they are blank.

it dosent show them as blank at all..

what am i doing wrong?

Are you looking at the wrong output from your Conditional Split?

-Jamie

|||

What is the precise definition of blank for your columns? From the expression, it looks like the answer is NULL or empty string or possible one space?

If blank can also mean "just spaces", one thing i can suggest is instead of

columnX == " "

try something like the following, which works for any number of "just spaces":

LEN(TRIM(columnX) == 0)

Mark

conditional split problem

I have been transfering data from text file to sql databases.

I have a conditional split where i check to if the address has changed for a particular person.If yes i direct to update else i direct to default output which means no change.

when i connect error output of conditional split to a database or union all couple of rows are directed to error output.But i dont understand the reason.How would i be able to know why they r directed to error.

Please let me know.

When the rows are sent to the error output they should have two new columns (ErrorCode and ErrorColumn) added to them. To determine the cause of the error, I would start by looking at these values, and then researching the code(s) that they contain.|||

This is a way of doing what Matthew suggested:

By default every error output in your dataflow will add a couple of columns: ErrorNumber and error column. The you can use script task to get the description of the error. Jamie has an explanation of that on his blog:

http://blogs.conchango.com/jamiethomson/archive/2005/08/08/1969.aspx

Conditional Split problem

Don't know if anybody else has had this -

In a dataflow task I added a conditional split that currently has two possible outcomes.

Initially this was a straight choice. Later I added an "or" clause on the second leg thus

expression1 || expression2

on which the second leg ceased working.

After some mental anguish I deleted the component and then reimplemented it using identical logic and magically (or so it appears) it all worked.

So, it appears the old tricks still apply...

I don’t suppose you have the package before and after so we can look at
the differences?|||Sorry, no - versions under version control don't have the relevant logic in them. I'll keep an eye out for it in future though...|||

I have had people report something similar, but every time it turned out to be a slight error in the expression that resulted in one output never receiving data. What would be compelling as a bug would be if the number of rows on all outputs (with all outputs connected) did not equal the incoming rows.

If you see it again, please do post more details.

Donald

|||

When I created the new component, I copied the old expressions to Notepad and then pasted them back in again, so your scenario is unlikely (though of course not impossible.)

|||

Another possibility is that, if you have multiple conditional split conditions, they are being applied in a different order from your first implementation.

Donald

conditional split on field in csv file

I know this should be simple but I can't figure it out. I am reading in a csv file to a conditional split task, all I want to do is split the file based on a field. Some values in field will have a suffix say ABCD while others wont. So my conditional split says Right(FieldA,4)=="ABCD" which then splits file in two directions or at least it's meant to. Problem is that it does not work. I think it has something to do with the field type in the csv file although I have tried using a Data Conversion task but to no avail all the field values with ABCD suffix are ignored by my conditional split and head off the same way as other values. Funny thing is is that if I manually add a value to the file with a suffix of ABCD and run task again then the conditional split works on the manually added row and all rows with suffix of ABCD. It's like it does not recognise previous values as string until one is added manually.

Thanks

Might there be trailing spaces on the file?

Try:

Right(Trim([FieldA]),4)=="ABCD"

|||

Thanks fo reply, yes I tried that one but it doesn't have trailing spaces. I think it has something to do with fields in Input file.

When I look at the file in notepad values are

"123","somevalue"

"123ABCD","somevalue"

I have seen some posts regarding text qualifier as a problem, mine is set to " but conditional split still fails.

|||Is it throwing any type of error message?|||

No the file processes fine.

If I add another row to file and put in the suffix then the conditional split works but I think this is because the text qualifiers are removed once I had row to file eg

Original file

"123","somevalue"

"123ABCD","somevalue"

goes thru data flow task with no errors but conditional split doesn't work

Modified file will look like

123,somevalue

123ABCD,somevalue

456ABCD,somevalue

and will work for both rows with suffix.

|||

Could you put a data viewer before the conditional split and see what data is getting in?

Thanks.

|||

Have put data viewer in and first riow looks correct but rows following look like they have another delimiter shown below

123, somevalue

o “456”, “somevalue”

o “123ABCD”, “somevalue”

Row delimiter is set to {CR}. Column 1 delimiter is set to {CR} if I change this to {CR}{LF} then the process fails with error delimeter for column 1 not found.

Thanks

|||

Got it to work in the Columns tab

Row Delimeter is {LF}

Column delimiter is Comma {,}

Text qualifier is "

in advanced tab

Coliumn delimeter of 2nd column is {LF}

which ended up making data come in as

123, somevalue"

456, somvalue"

123ABCD, somevalue"

which the conditional split was happy with.

Thanks for your help

Conditional split on date ?

Hi,

I have a DT_DATE column. I'd like to achieve a conditional split to ignore all records for which the date is below a specific hardcoded date (eg: 2007-03-01).

I'm having a hard time trying to express this using the conditional split transform.

What is the correct syntax to express a DT_DATE literal ?

eg:
[date] < (DT_DATE) "2007-03-01"

regards

Thibaut

What you have should work fine. I built a little test package to verify, and each of these worked as expected:

Code Snippet

HireDate < (DT_DATE)"1998-01-30"

Code Snippet

[HireDate] < (DT_DATE)"1998-01-30"

Code Snippet

HireDate < (DT_DATE)"01/30/1998"

Code Snippet

[HireDate] < (DT_DATE)"01/30/1998"

What behavior are you experiencing that prompts you to ask the question?

|||Are you sure [date] is a DT_DATE column and not a DT_DBTIMESTAMP column? That is, does it contain a time component?

Just double checking.

conditional split for insert or update cause dead lock on database level

Hi

I am using conditional split Checking to see if a record exists and if so update else insert. But this cause database dead lock any one has suggestion?

Thanks

Don't try and insert and update a table from the same data flow.

-Jamie

|||

My read from db is very expensive. We can't afford to read same data twice. So I use another merge join to force waiting on updating records to finish before I insert. This solve my problem. Thanks anyway.

aproaching Before:

conditional Split on newRecords and changedRecords, OLE DB Command was used to update changedRecords, OLE DB Destination was used to insert newRecords

aproaching Now:

Conditional Split on newRecords and changedRecords, OLE DB Command was used to update changedRecords,

Merge Join is used to left outer join newRecords with output of OLE DB Command ( this leave only newRecords is availible in output, but still wait for db update command finish), then

OLE DB Destination was used to insert output from merge join.

|||

Jun Fan wrote:

My read from db is very expensive. We can't afford to read same data twice. So I use another merge join to force waiting on updating records to finish before I insert. This solve my problem. Thanks anyway.

Why do you need to read data twice? Just push one of the data paths into a raw file and then insert/update/whatever that data from another dataflow.

-Jamie

|||

Thanks for sugestion. Pushing data into temp location (file or temp table) has been too slow for large amount new records. Another merge join to force wait on update finishing seems work great at this point.

Thanks again!

Jun Fan

|||

Jun Fan wrote:

Thanks for sugestion. Pushing data into temp location (file or temp table) has been too slow for large amount new records. Another merge join to force wait on update finishing seems work great at this point.

Thanks again!

Jun Fan

Have you tried raw files? They're lightning fast.

By the way, merge join does not ensure anything. It slows up one datapath, sure, but that in no way guarantees that you will prevent your locking problem.

-Jamie

|||

If performance is a concern, I'm suprised that using the OLEDB Command is OK, as it tends to be pretty slow. I have had much better success using a Conditional Split to direct new rows (Inserts) to an OLEDB Destination that writes directly to the target table, and directs the update rows to a permanent temp table. Then I use an Execute SQL Task to issue a batch Update statement after the data flow. During performance testing in the environments I work in, this has proven to be the fastest approach.

This is a pattern that many of the regular posters on this forum use very successfully.

conditional split for insert or update cause dead lock on database level

Hi

I am using conditional split Checking to see if a record exists and if so update else insert. But this cause database dead lock any one has suggestion?

Thanks

Don't try and insert and update a table from the same data flow.

-Jamie

|||

My read from db is very expensive. We can't afford to read same data twice. So I use another merge join to force waiting on updating records to finish before I insert. This solve my problem. Thanks anyway.

aproaching Before:

conditional Split on newRecords and changedRecords, OLE DB Command was used to update changedRecords, OLE DB Destination was used to insert newRecords

aproaching Now:

Conditional Split on newRecords and changedRecords, OLE DB Command was used to update changedRecords,

Merge Join is used to left outer join newRecords with output of OLE DB Command ( this leave only newRecords is availible in output, but still wait for db update command finish), then

OLE DB Destination was used to insert output from merge join.

|||

Jun Fan wrote:

My read from db is very expensive. We can't afford to read same data twice. So I use another merge join to force waiting on updating records to finish before I insert. This solve my problem. Thanks anyway.

Why do you need to read data twice? Just push one of the data paths into a raw file and then insert/update/whatever that data from another dataflow.

-Jamie

|||

Thanks for sugestion. Pushing data into temp location (file or temp table) has been too slow for large amount new records. Another merge join to force wait on update finishing seems work great at this point.

Thanks again!

Jun Fan

|||

Jun Fan wrote:

Thanks for sugestion. Pushing data into temp location (file or temp table) has been too slow for large amount new records. Another merge join to force wait on update finishing seems work great at this point.

Thanks again!

Jun Fan

Have you tried raw files? They're lightning fast.

By the way, merge join does not ensure anything. It slows up one datapath, sure, but that in no way guarantees that you will prevent your locking problem.

-Jamie

|||

If performance is a concern, I'm suprised that using the OLEDB Command is OK, as it tends to be pretty slow. I have had much better success using a Conditional Split to direct new rows (Inserts) to an OLEDB Destination that writes directly to the target table, and directs the update rows to a permanent temp table. Then I use an Execute SQL Task to issue a batch Update statement after the data flow. During performance testing in the environments I work in, this has proven to be the fastest approach.

This is a pattern that many of the regular posters on this forum use very successfully.

Conditional split error message

Getting the below error msg on my conditional split. I changed the error output to ignore errors and that keeps the error msg from appearing (and everything seems to work normally), but why would it evaluate to NULL?

Thanks

[Conditional Split - Find rows with balances [3412]] Error: The expression "FINDSTRING(Column0,"OPENING",1) > 0 || FINDSTRING(Column0,"CLOSING",1) > 0" on "output "Balance Rows" (3415)" evaluated to NULL, but the "component "Conditional Split - Find rows with balances" (3412)" 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.

Can Column0 be NULL?|||

I honestly don't know how.

The data file has between 4 and 6 rows on any given day - 4 of those rows always have "CLOSING" or "OPENING" in them.

So the conditional split should ignore the other rows, right?

That's why I don't understand why it's finding a null?

|||Try this expression:

FINDSTRING((ISNULL(Column0) ? "" : Column0),"OPENING",1) > 0 || FINDSTRING((ISNULL(Column0) ? "" : Column0),"CLOSING",1) > 0|||The problem may be that when evaluating Column0, some of the columns are NULL, and hence when the conditional split tries to evaluate the statement I provided to you a few days ago, it may fail. Using the new statement I just posted, we "trap" the fact that if the column is NULL, we set its contents to "" and continue on with the FINDSTRING statement.|||

So it's throwing the "NULL" error if finds any null columns (regardless of whether the row has OPENING or CLOSING in it, because it has to evaluate ALL rows?)

Such as, for example:

1234, ,1234 intstead of 1234," ",1234 ?

Is the first case above considered a null?

(Although I looked through my file, and I do not see any null fields at all)

|||Well, yes, that'd be true. ALL rows pass through the conditional split. The output path that gets chosen depends on which condition evaluates to true.|||

So to answer the other question,

a blank between file delimiters is considered a null value to ssis?

Such as 1234, ,1234 ?

I want to clarify this because it's got me concerned, as this situation is also causing problems with another file where it can't convert a value "without loss of data", because a numeric field is blank.

I was told to convert the value to string first, then convert it back to a numeric. Is this considered a best practice for working with numeric values?

Thanks

|||

Yes, it will be trated as NULL if your Flat File Source is configured to parse it that way. There is the property on the source adapter to control that.

It is not best the practice to convert numeric data to strings and back, but you need to make sure your numeric data is really numeric. NULLs should be fine if you can handle them downstream. It looks like your conditional split was not prepared for them.

Thanks,

~Bob

Conditional Split Component - annotation issues

Greetings SSIS friends,

When I configured my conditional split component (directing the data flow in 2 directions) The annotation does not align properly with the lines. Is there anyway to shift the text as to make more presentable?

Thanks for your help in advance.

No, you can't.

You can turn off the annotation, and then add your own, though, by right-clicking on the background and selecting "Add Annotation."|||

Hi Phil,

Excuse the silly questions! So how do I turn off the annotations?! I think I'd much rather add my own as the automatic ones don't seem to align themselves with the data flow lines. Shame.

|||

dreameR.78 wrote:

Hi Phil,

Excuse the silly questions! So how do I turn off the annotations?! I think I'd much rather add my own as the automatic ones don't seem to align themselves with the data flow lines. Shame.

Double click on the flow line and then set the PathAnnotation (found in the General section under Design) to "Never."|||

dreameR.78 wrote:

Hi Phil,

Excuse the silly questions! So how do I turn off the annotations?! I think I'd much rather add my own as the automatic ones don't seem to align themselves with the data flow lines. Shame.

If you think there's an issue here then please raise it at Connect (http://connect.microsoft.com/sqlserver/feedback)

-Jamie

CONDITIONAL SPLIT Assistance

i need to use a conditional split transformation to find missing column and direct the output of conditional split to my destination.

I have the following columns PatientId, Allergycode, SeverityCode

My requirement is to check whether value of a particular column is null or not null.

Please help.

Ronald

What part is a problem? Have you looked at transform documentation?
http://msdn2.microsoft.com/en-us/library/ms137886.aspx
To check for null values use IsNull method :)
http://msdn2.microsoft.com/en-us/library/ms141184.aspx|||

Hi Entin,

I have an access source table and the destination SQL table. in between i have the Conditional Split in which i use !ISNULL() the each column i have in my prescription table to test for missing columns and the Data conversion for changing the data type for the date column. Data conversion tranformation follows after the Split Condition Trans.

When i execute the package, it executes successfully but it doesnot write any rows to the destination table in SQL SERVER database.

Help out me bro.

Ronald

SSIS package "Conditional.dtsx" starting.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.

Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.

Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.

Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.

Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "SQL Server Destination" (322)" wrote 0 rows.

SSIS package "Conditional.dtsx" finished: Success.

|||How many outputs does your Conditional Split have? Which outputs are connected to Data Conversion transform and SQL Destination? Have you monitored the data flow during execution - do any row flow out of the output you are using?|||

My conditional Split has 14 outputs.

The conditional Split default output is connected to the data conversion.

Yes, I have monitored the data flow during execution. No, it does not.

|||So it probably means that each row satisfies at least one of the conditions, and no row falls back to the default output?|||

So, how do i go about this to make sure that, it writes rows to the destination table in SQL SERVER

Regards,

Ronald

|||

When i use case12 (MedicineCode) as an output to the Data conversion.

When i execute the package, it writes 57 rows to the destination table instead of 58 rows.

Ronald

SSIS package "Conditional.dtsx" starting.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.

Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.

Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.

Error: 0xC0202009 at Data Flow Task, SQL Server Destination [322]: An OLE DB error has occurred. Error code: 0x80040E14.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 27, column 3. The destination column (VisitType) is defined as NOT NULL.".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 26, column 3. The destination column (VisitType) is defined as NOT NULL.".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 25, column 3. The destination column (VisitType) is defined as NOT NULL.".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 24, column 3. The destination column (VisitType) is defined as NOT NULL.".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 23, column 3. The destination column (VisitType) is defined as NOT NULL.".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 22, column 3. The destination column (VisitType) is defined as NOT NULL.".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 21, column 3. The destination column (VisitType) is defined as NOT NULL.".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 20, column 3. The destination column (VisitType) is defined as NOT NULL.".

Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.

Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "SQL Server Destination" (322)" wrote 57 rows.

Warning: 0x80019002 at Data Flow Task: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Task failed: Data Flow Task

Warning: 0x80019002 at Conditional: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Conditional.dtsx" finished: Failure.

|||

Ronaldlee Ejalu wrote:

So, how do i go about this to make sure that, it writes rows to the destination table in SQL SERVER

Each output of conditional split forms a separate data flow. If you want to insert this data, you need to either

1) have one SQL destination per output, or

2) connect the flows together with Union All transform, then have a single destination