TASK_RTF_NOTES in MS Project or RTF in MS SQL. How to win it and prepare cubes in SSAS

There is a problem that is quite painful for programmers of organizations of MS Project organizations - getting notes from responsible persons. Notes have significant value (with the correct formulation of the control problem), because without the initial information, the problems are not classified and the correct decision is not taken. They, notes, of course, need to be displayed in reports.

From the user's point of view, everything seems to be simple - the report is the report, but from a technical point of view, there are a lot of nuances and questions. In this article, I present my solution, based on some pieces of code scattered here and there, over the network, and I hope that it will be useful to my colleagues.

I do not pretend to originality, but for some reason I did not find analogues of this solution, I had to collect it myself. In addition, I am not a very deep MS SQL specialist, so if I have any practical comments, please comment.
')

The notes are obviously stored in a field called TASK_RTF_NOTES. The field is filled by the user when sending reports on tasks for which he is responsible. There seems to be no special need for markup tools, moreover, they, these tools, are hidden from the user, but for some reason, the respected (though not understood by me) Indians save this information in RTF format. Everything would be fine, but it is necessary to prepare reports, including not on one project, but on a heap at a time. SSAS combined with, for example, MS Excel is a good reporting tool.

Yes, in MS Project Professional, of course, there is a “report builder”. But these are not cubes, this is a bunch of requests with output to the table. And to change the cuts on the go, even if you really want it, you will fail. And you will have to send this report to a heap of people instead of themselves taking it at any time.

Ie, presumably, the scheme is approximately as follows:
1) Extract field value
2) Pass value to cube dimension
3) Get a beautiful report in MS Excel (for example)

Those. something like this:


But it was not there.

The first surprise is that the field is of type image . What for? Unclear. Why I stopped at this little thing - it will be clear further. Transformed into varbinary (MAX) , drove on.
The second surprise is RTF itself. what to do with it? We climb into the network and find a pack of tips, which I broke into 2 categories:

1) Take advantage of the object RTFTextBox.
The tip is right on MSDN! This, apparently, the official position of MS on this issue! Even a vague doubt creeps in, and did not they put this feature on the MSP specifically, in order to be able to sell report builders?

2) Do not fool people with problems and use the TASK_NOTES field.
Great. Especially if you read further. And further it is written that only the TASK_RTF_NOTES fields are stored here.

And that's all. There are more tips to parse RTF using regular expressions. But regular expressions in MS SQL also seem to be rather not there than there is, for their implementation it is also necessary to add the "CLR function" ... What should I do?

I had to search for more. Now I already had more opportunities - because I obviously can’t do without addon, I thought and thought and decided to write addon specifically to parse RTF. Do you think I took advice number 1? Yes, that's right, I used it. But for some reason, SQL chewed my dll-ku and spat it out with the phrase “I don’t believe System.Drawing, it’s a curve, it’s badly written and there are a lot of vulnerabilities in it”. Funny, in MS, it turns out, they write curves dll-ki!

Then I finally realized that without parsing the RTF I could not do it, and finally I came across this place:
NRTFTRee by Oliver

Here, of course, not a word about TASK_RTF_NOTES. Yes, and the comments in Spanish. However, here I saw a real opportunity to collect the source code of the parser, which was done.

I’ll omit the details of the assembly without System.Drawing - just a little imagination and understanding that in fact all these classes are not really needed for this task, but there are no irreplaceable ones.

An example of a SQL script that now really works:

SELECT TOP 1000 [TASK_UID] ,[TASK_NAME] ,[PROJ_UID] ,convert(varchar(max),[dbo].ConvertFromRTF(convert(varbinary(max),[TASK_RTF_NOTES]))) ,[TASK_NOTES] FROM [ProjectServer2010_Published].[dbo].[MSP_TASKS] where not [TASK_RTF_NOTES] is null 

I will answer the question “Why covert so many times ???” - for some reason, the CLR functions do not support the transfer of image and varchar (at least in 2005 MS SQL).

Yes, there is still a caveat - for some reason, the encoding in our MSP is not 1251, but 1252 (but not always, since some of the projects have been moved), so the conversion function itself is written like this:

 [SqlFunction(DataAccess = DataAccessKind.Read)] public static SqlBytes ConvertFromRTF(SqlBytes bytes) { if (bytes == null) return null; if (bytes.IsNull) return null; if (bytes.Length == 0) return null; MemoryStream strm = new MemoryStream(bytes.Value); byte[] buff = new byte[4096]; int rd = strm.Read(buff, 0, buff.Length); RtfTree tree = new RtfTree(); string s1 = Encoding.Default.GetString(buff); tree.LoadRtfText(s1); string text = tree.Text; SqlBytes res; if (s1.IndexOf("ansicpg1252") > -1) { res = new SqlBytes(Encoding.GetEncoding("Windows-1252").GetBytes(tree.Text)); } else if (s1.IndexOf("ansicpg1251") > -1) { res = new SqlBytes(Encoding.GetEncoding("Windows-1251").GetBytes(tree.Text)); } else { res = new SqlBytes(Encoding.Default.GetBytes(tree.Text)); } return res; } 

References:
1. Original converter: NRTFTRee by Oliver
2. Archive with solution: LibRTFToVarchar