Dataset Distinct Query

I am trying to build a report with a prompt to select building. It all works fine except I in the prompt building is list as may times as there is data for it. I want it to only display once, but not effect the other data is there a way I can query this?

I have my 2 datasets and my parameter set up to display building to select from. My issue is I get multiple values in the Select Value Prompt. It will pull the correct data I just want to clean up the Select Value Prompt.

Example: of prompt list

Building 1

Building 1

Building 1

Building 2

Building 2

Dataset 1:

Select Building, First, Last

From Table

Dataset 2:

SELECT Building, First, Last

From Table

WHERE (Table.Building = @.Building)

Hi Ray,

You can use the DISTINCT clause:

Select DISTINCT Building, First, Last

From Table

Cheers,

Rob

|||That doesn't work|||What had to do is make a 3rd dataset in the report, w/ just the select distinct statement in it and then, change the available values dataset to the one that is just the select distinct.sql

Datareader Destination as source for other datareader source ?

HI!

as far as I know from docs and forum datareader is for .NET data in memory. So if a use a complex dataflow to build up some data and want to use this in other dataflow componens - could i use data datareader source in the fist dataflow and then use a datareader souce in the second dataflow do read the inmemoty data from fist transform to do fursther cals ?

how to pass in memory data from one dataflow to the next one (i do not want to rebuild the logic in each dataflow to build up data data ?

Is there a way to do this ? and is the datareader the proper component ? (because its the one and only inmemory i guess, utherwise i need to write to temp table and read from temp table in next step) (I have only found examples fro .NET VB or C# programms to read a datareader, but how to do this in SSIS directly in the next dataflow?

THANKS, HANNES

Hannes,

You would need to build an ADO.Net provider for SSIS packages in order to do this. We have built one at Conchango but I think my boss is reluctant for us to just give it away.

-Jamie

|||

Hi Jamie!

I could understand your boss..

I would expect there is some integrated way because it is essential to get data from one dataflow to an other dataflow.

Do you know any othere method (without temp tables) to move data from one dataflow to to next?

I do not understand the difference between datareader and oledb source/dest? is there reader only for .NET Programming, oder is there a special purpose for this objects i do not realize (it seems to be the same escape for integration with other .NET data consumer which could read the Datareader...)

THANKS, HANNES

|||

hannes,

I have just realised that I completely misunderstood your first post. I apologise for that.

The answer I gave previously applies if you want to pass data from one PACKAGE to another. I realise now that what you want to do is pass data from DATA-FLOW to another. You have 2 options for doing that. Raw files or ADO recordsets.

I compare the two here: http://blogs.conchango.com/jamiethomson/archive/2006/06/28/4159.aspx and its pretty conclusive about which you should use.

Again, my apologies for misreading your initial post!

-Jamie

P.S. Hannes, are you still at MISAG?

|||

That is it, but what I cannot understand why there is no in memory way.

THANKS

PS: No! - AVENESS - see signature for further details (for the moment only in german)

|||

There is - the ADO recordset destination provides this functionality. Admittedly you have to write code to access it again in a script source component but this is because the metadata of the recordset is not (and cannot be) persisted in the Object variable at design-time.

-Jamie

DataMining problems

Hello Experts,

I’ve got a logical problem. I’m just a newbie in this theme.

I build a cube with Analysis Services. It works and I can brows the cube.

For Example this could be a possible result:

Color

RedBlueBlackYellow

Auto brand

Opel12

VW214

Mercedes1122

The count shows us how much cars were stolen.

Now I try to build a Data Mining structure that shows me the behavior between the auto brand and the color. (To build the report I used Excel 2007 and the Data Mining AddIn)

After build the Mining Structure the Colum name are 1, 2 or 4 (just the counts).

To search for a better solution I tried to build the report manual. (Now it looked like that)

Auto BrandCollorstolen

OpelRedno

OpelBlueyes

OpelBlackyes

OpelYellowno

VWRedyes

VWBlueyes

VWBlackno

VWYellowyes

MercedesRedyes

MercedesBlueyes

MercedesBlackyes

MercedesYellowyes

Now the Column names are correct and the Data Mining structure is more understandable.

But the second report you can’t build with a cube, because the Measures were just numeric.

My Questions:

- Where is my error?

- How do you build a Data Mining?

- Is there somewhere a tutorial could help me (I just founded tutorials that explains the different works of the different Data Mining Models)

- How would you build this problem in a Mining Structure?

Hope I don’t forget some information and hear you soon

Yours sincerely,

Alex

Please visit our portal site ssdm.com. You can find tutorials, tips and tricks as well as samples there.

As to your problem, it depends on what information you wish to analyze. For example, you can build a data mining model using some classification algorithms (such as decision trees and neural network). If you mark stolen as predictable, you can use your model to predict the probability that some specific type of car got stolen. You can also train a clustering model to assist in exploring your data.

Thanks,