Showing posts with label dataflow. Show all posts
Showing posts with label dataflow. Show all posts

Monday, March 19, 2012

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

Sunday, March 11, 2012

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

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

Saturday, February 25, 2012

Conditional Execution in the Control Flow via Script Task

Greetings.

I'm trying to conditionally execute a dataflow based on the presence of a data file. If the data file isn't present, I'd like to execute gracefully without error.

Logic is as follows:

If FileExists Then
execute dataflow
Else
exit w/o error
End If

I've got the code ready to go, but I'm not sure how to do this conditional branch logic. Right now, the code calls the Dts.Results.Success / Failure. The problem, however, is Failure is exactly that... which doesn't result in the graceful exit I'm looking for.

Anyone have any ideas?

Thanks in advance.

Here is how I would do that:

Create a script task in your control flow to check if the file exists and write that result into a SSIS variable, let's say FileExists=1 -->exists; FileExtis=0 -->Does not exist. Then create a precedence constraint from the script task to the data flow. Then Edit the precedence constraint to use evaluation operation 'Expression and constraint'; Value 'success' and write the expression like @.[User::FileExists]==1.

This way the dataflow will be executed only if the sript task succed and the value of the variable FileExists is equal to one.

Rafael Salas

|||This sounds like a fantastic suggestion for the File System Task. I would suggest you submit it at the Microsoft Connect site.|||

Phil Brammer wrote:

This sounds like a fantastic suggestion for the File System Task. I would suggest you submit it at the Microsoft Connect site.

You mean to have a 'Check if file exists' operation in the file system task?

Rafael Salas

|||

Rafael Salas wrote:

Phil Brammer wrote:

This sounds like a fantastic suggestion for the File System Task. I would suggest you submit it at the Microsoft Connect site.

You mean to have a 'Check if file exists' operation in the file system task?

Rafael Salas

Indeed.|||

Good Idea. I followed your suggestion; for those interested in voting on that suggestion:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=231838

Rafael Salas

|||For those curious.. I documented the steps for future peoples that landed on this thread.

dichotic.wordpress.com