We have a scheduled a nightly SAP job to create a file called SAP_Users.csv. The purpose of this file is to list all the active SAP accounts. This file is placed on shared folder.
We then take our SQL server and do a DTS of this file to load it to our SQL server.
This file called SAP_Users.csv contains two columns one of which is the User ID. The newly populated table uPerformSAPusers is then massaged with additional metadata about the users from our PIMS (Personal Management Information System) via a SQL cursor scheduled from our HR SQL server. The cursor also skips people that are not currently active such as machines and people that have been terminated.
The final table:
CTNHCORDB01.uPerform_sapusers.dbo.uPerformSAPusers appears as follows:
Please note that NULL columns on First Name, Last Name, Department etc.. may indicate that this is a machine or a recently terminated person. The XML file that is created for the uPerform’s import excludes records where both a first and last name is empty. Also note that updates occur from HR Self-Serv’s SQL database because a link was created from HR Self-Serv to CTNHCORDB01. The details of how the link was established are contained the the weekly cursor job stored on HR Self-Serv.
Finally a executable that creates the required XML formatted file named: uPerformSAPuserQuery.exe is placed onto the uPerform server at :
\\ctnhupwebp1\d$\Vignette\Collaboration\WWW\TKRuPerformSAPusers
The uPerformSAPuserQuery.exe is scheduled via windows scheduler and creates the output uPerformSAPuserQuery.xml . Obviously the executable contains a connection to the CTNHCORDB01 server and utilizes the uPerformSAPusers table for it’s results. The source file for the executable exist at the same location as the executable for any future revisions. The processing of the executable involves a SQL FOR XML statement. There are three main advantages for utilizing a executable. The first was to capture the output into a stream that was not delimited by control returns or line feeds (CR LF) this is know to occur on a SQL job output. The second reason was to easily allow the file to be created on the local server (where it is required) without the need for additional folder shares. Finally the use of a VB.net application allowed me to utilize Dot Net’s commands to indent the XML output and make it more easily readable.
The final output can be observed via the URL:
http://test.uperform.inside.tkr/TKRuPerformSAPusers/uPerformSAPuserQuery.xml
The executable is done as dos exec and outputs to standard out. A batch job simply redirects the output to to a file name something.XML. uPerform 4.0 has the ability to be look for this static named XML file and load it on it own schedule.
My source for uPerformSAPuserQuery.exe below:
Option Explicit OnOption Strict On Imports SystemImports System.DataImports System.Data.SqlClientImports Microsoft.VisualBasic‘ — xml stuffImports System.Collections.GenericImports System.TextImports System.IOImports System.Xml Module Module1 Sub Main() Dim connectionString As String = “Password=NotARealPassword;Persist Security Info=True;User ID=uPerformSAPQuery;Initial Catalog=uPerform_sapusers;Data Source= CTNHCORDB01″ ‘ Production Server Dim queryString As String = _“SELECT 1 AS Tag, “ + vbCrLf & _“NULL AS Parent,” + vbCrLf & _“NULL AS [user!2!guid],” + vbCrLf & _“NULL AS [user!2!firstName!element], “ + vbCrLf & _“NULL AS [user!2!lastName!element],” + vbCrLf & _“NULL AS [user!2!department!element],” + vbCrLf & _“NULL AS [accountInfo!3!userName!element],” + vbCrLf & _“NULL AS [accountInfo!3!qualifiedUserName!element],” + vbCrLf & _“NULL AS [accountInfo!3!emailAddress!element],” + vbCrLf & _“NULL AS [culture!4!id],” + vbCrLf & _“NULL AS [culture!4!cultureName!element],” + vbCrLf & _“0 AS [users!1!user!hide]“ + vbCrLf & _“UNION ALL” + vbCrLf & _“SELECT 2 AS Tag, “ + vbCrLf & _“1 AS Parent,” + vbCrLf & _“uperformSAPusers.guid AS [user!2!guid],” + vbCrLf & _“uperformSAPusers.firstName AS [user!2!firstName!element], “ + vbCrLf & _“uperformSAPusers.lastName AS [user!2!lastName!element],” + vbCrLf & _“uperformSAPusers.department AS [user!2!department!element],” + vbCrLf & _“uperformSAPusers.userName AS [accountInfo!3!userName!element],” + vbCrLf & _“uperformSAPusers.qualifiedUserName AS [accountInfo!3!qualifiedUserName!element],” + vbCrLf & _“uperformSAPusers.emailAddress AS [accountInfo!3!emailAddress!element],” + vbCrLf & _“uperformSAPusers.culture AS [culture!4!id],” + vbCrLf & _“uperformSAPusers.cultureName AS [culture!4!cultureName!element],” + vbCrLf & _“1 AS [users!1!user!hide]“ + vbCrLf & _“FROM uperformSAPusers WHERE qualifiedUserName IS NOT NULL “ + vbCrLf & _“UNION ALL” + vbCrLf & _“SELECT 3 AS Tag, “ + vbCrLf & _“2 AS Parent,” + vbCrLf & _“uperformSAPusers.guid AS [user!2!guid],” + vbCrLf & _“uperformSAPusers.firstName AS [user!2!firstName!element], “ + vbCrLf & _“uperformSAPusers.lastName AS [user!2!lastName!element],” + vbCrLf & _“uperformSAPusers.department AS [user!2!department!element],” + vbCrLf & _“uperformSAPusers.userName AS [accountInfo!3!userName!element],” + vbCrLf & _“uperformSAPusers.qualifiedUserName AS [accountInfo!3!qualifiedUserName!element],” + vbCrLf & _“uperformSAPusers.emailAddress AS [accountInfo!3!emailAddress!element],” + vbCrLf & _“uperformSAPusers.culture AS [culture!4!id],” + vbCrLf & _“uperformSAPusers.cultureName AS [culture!4!cultureName!element],” + vbCrLf & _“1 AS [users!1!user!hide]“ + vbCrLf & _“FROM uperformSAPusers WHERE qualifiedUserName IS NOT null “ + vbCrLf & _“UNION ALL” + vbCrLf & _“SELECT 4 AS Tag, “ + vbCrLf & _“3 AS Parent,” + vbCrLf & _“uperformSAPusers.guid AS [user!2!guid],” + vbCrLf & _“uperformSAPusers.firstName AS [user!2!firstName!element], “ + vbCrLf & _“uperformSAPusers.lastName AS [user!2!lastName!element],” + vbCrLf & _“uperformSAPusers.department AS [user!2!department!element],” + vbCrLf & _“uperformSAPusers.userName AS [accountInfo!3!userName!element],” + vbCrLf & _“uperformSAPusers.qualifiedUserName AS [accountInfo!3!qualifiedUserName!element],” + vbCrLf & _“uperformSAPusers.emailAddress AS [accountInfo!3!emailAddress!element],” + vbCrLf & _“uperformSAPusers.culture AS [culture!4!id],” + vbCrLf & _“uperformSAPusers.cultureName AS [culture!4!cultureName!element],” + vbCrLf & _“1 AS [users!1!user!hide]“ + vbCrLf & _“FROM uperformSAPusers WHERE qualifiedUserName IS NOT null “ + vbCrLf & _“ORDER BY [accountinfo!3!username!element],[Parent],[Tag]“ + vbCrLf & _“FOR XML EXPLICIT; “ + vbCrLf Dim unformattedXml As New System.Text.StringBuilder()unformattedXml.Append(“<?xml version=” & ControlChars.Quote & “1.0″ & ControlChars.Quote & ” encoding=” & ControlChars.Quote & “utf-8″ & ControlChars.Quote & ” ?>” & vbCrLf) Using connection As New SqlConnection(connectionString)Dim command As SqlCommand = connection.CreateCommand()command.CommandText = queryStringTryconnection.Open()Dim dataReader As SqlDataReader = _command.ExecuteReader()Do While dataReader.Read()unformattedXml.Append(dataReader(0))LoopdataReader.Close() Catch ex As ExceptionConsole.WriteLine(ex.Message)End TryEnd Using Dim MyformattedXML As StringMyformattedXML = IndentXMLString(unformattedXml.ToString())Console.Write(MyformattedXML)End Sub Private Function IndentXMLString(ByVal xml As String) As StringDim outXml As String = String.EmptyDim ms As MemoryStream = New MemoryStream()‘ Create a XMLTextWriter that will send its output to a memory stream (file)Dim xtw As XmlTextWriter = New XmlTextWriter(ms, Encoding.Unicode)Dim doc As XmlDocument = New XmlDocument() Try‘ Load the unformatted XML text string into an instance ‘ of the XML Document Object Model (DOM)doc.LoadXml(xml) ‘ Set the formatting property of the XML Text Writer to indented‘ the text writer is where the indenting will be performedxtw.Formatting = Formatting.Indented ‘ write dom xml to the xmltextwriterdoc.WriteContentTo(xtw)‘ Flush the contents of the text writer‘ to the memory stream, which is simply a memory filextw.Flush() ‘ set to start of the memory stream (file)ms.Seek(0, SeekOrigin.Begin)‘ create a reader to read the contents of ‘ the memory stream (file)Dim sr As StreamReader = New StreamReader(ms)‘ return the formatted string to callerReturn sr.ReadToEnd()Catch ex As ExceptionConsole.WriteLine(ex.ToString())Return String.EmptyEnd TryEnd Function End Module
Tags: uPerform, XML
This entry was posted
on Thursday, March 18th, 2010 at 10:46 pm and is filed under Uncategorized.
You can follow any responses to this entry through the RSS 2.0 feed.
You can leave a response, or trackback from your own site.
Hello. I was reading someone elses blog and saw you on their blogroll. Would you be interested in exchanging blog roll links? If so, feel free to email me.
Thanks.