Retrieve XML From sysdtspackages90

Is there a way to translate the packagedata field data to XML on the sysdtspackages90 table on msdb?

I want to be able to programmatically analyze and potentially modify the XML of Integration Services packages that reside on the server.

Thanks for your help.

Modifying the xml directly is not supported. However, you can get the package from the packages table by using the LoadPackageFromSQLServer method on the application object. You can save it to a file and then read the package file however you like to access the xml (xmlreader, dom, etc).

Matt

retrieve the value that occurs the most

Hi,

I am trying to retrieve the value that occurs the most at a dimension level. For example, I have to analyze the product type mostly used in a specific target segment by number of products.

The mathematical function normally used is MODE. However, in AS2005 there is not such function. I tried to use TopCount but it is not as easy as I thought.

Any suggestion?

Thanks!

As far as I know, TopCount should work.

This is a sample from Adventure Works:

with

set [TopCustomers] as

TOPCOUNT(

[Dim Customer].[Dim Customer].[Dim Customer].members,

1,

[Measures].[Sales Amount]

)

select {[Measures].[Sales Amount]} on 0,

[TopCustomers] on 1

from [Adventure Works DW]

Hope this helps,

Santi

|||I think TopCount would only get you a mode if you were to use it against a count measure. I am guessing that you want to find out which member from the product dimension has the most transactions against it for a given set of criteria. Depending on the granularity of your dimensions you may have difficulty doing this without having some sort of count measure.