Exotic Unpivot Transformation

I totally give up.

I need to import an csv file with system monitor performance counters into a sql server 2005 database

example csv format:

"(PDH-CSV 4.0) (W. Europe Daylight Time)(-120)","Server1\Memory\% Commited Bytes In Use", )","Server2\Memory\%Commited Bytes In Use"
"07/18/2006 10:08:57.295","24.701095057336488","30.701095077776488"

and I want it to transform it into:

Time ServerName Memory\% Commited Bytes In Use
07/18/2006 10:08:57.295 Server1 24.701095057336488
07/18/2006 10:08:57.295 Server2 30.701095077776488

I have two problems:

I need to trim the servername from the column values

I need to insert an extra column ServerName and put those server names in it

I tried Unpivot and Fuzzy grouping in SSIS but it's al too fuzzy for me...

Any pointers someone?
Many thanks!

Regards,

Mop

I presume that the number of servers that can be listed is unknown at design-time, is that correct?

If so then you will need to do this with an asynchronous script component.For each incoming row create a row in the output for each server in that row. You should be able to parse out the server names fairly easily using .Net code.

-Jamie

|||

Hello Jamie

You're right, I don't know the number of servers at design-time. I will look what's possible with the asyncronous script component. In the meantime someone also told me it could be accouplished using XML.

Thank you!

Regards,

Mop

|||

mop wrote:

Hello Jamie

You're right, I don't know the number of servers at design-time. I will look what's possible with the asyncronous script component. In the meantime someone also told me it could be accouplished using XML.

does this other person mean to store the source differently?

Well yes, of course if you restructure the source file then you'll be able to do it without your "exotic unpivot" within script. The question of whether you store that restructured source file as XML, CSV, fixed-length or something else entirely is completely and utterly irrelevant.

-Jamie