OBIEE & SSIS :
This post is in continuation to my previous post(http://obieevbdotnet.blogspot.jp/) where we have learn how to retrive xml data for a OBIEE query using web services.
PROBLEM STATEMENT:
It is simple to extract xml output for a single OBIEE query however if we need output for multiple queries and want to schedule a query, we need a scheduler to control and extract the output.
SOLUTION STATEMENT:
I have used SQL server integration services(SSIS) to schedule and extract OBIEE output to a shared drive. This implementation consists of 2 main steps:
- Create a local web services using the class defined in my previous post.
- Call the local web services from SSIS package.
- Build and schedule a job to call the package in SQL server.
STEP 1: Create a local web services using the class defined in my previous post.
- Create a new asp.net web service project.
- Add a web service template or rename the existing web service template as "webservice.asmx".
- Paste below code in "webservice.vb"
Dim obj As New obiee ' Refer the post http://obieevbdotnet.blogspot.jp/ for obiee class
<WebMethod()> _
Public Function RunReportPathSiebel(ByVal REPORTID As String, ByVal REPORTPATH As String) As String
Dim temp As String = ""
Try
obj.repId = REPORTID
obj.ExecuteQueryPath(REPORTPATH) ' executequery method will extract the xml data file in C:\temp folder
Catch ex As Exception
temp = ex.Message
End Try
Return temp
End Function
End Class
- Debug and Test the webservice.
- Execute "RunReportPathSiebel" by supplying "reportid" and "reportPath" as below
STEP 2: Call the local web services from SSIS package.
Any web services can be call directly from SSIS using "WEB Service Task" and use the method available, however there is one limitation of using it. Web service task need "HTTP connection Manager" setting to connect the WSDL(web service description language) specification of the web service. I am assuming that our OBIEE query is a long running query and hence the "Time Out" setting in connection manager is limited i.e 300 sec. which is very less.
I am going to show you an alternative method i.e using "SCRIPT Task" in SSIS.
- Open BID studio for SQL server 2005/2008 or higher.
- Create a new Integration Services Project and open package.dtsx
- Add a Script Task by dragging from the toolbox.
- Set the properties of Script Task as below
- Click "Design Script" to open visual studio editor.
- Paste the below class outside the main class i.e "ScriptMain". WS class will connect your local web services and execute a specific method.
ByVal serviceName As String, ByVal methodName As String, _
ByVal args() As Object) As Object
Try
Dim client As System.Net.WebClient = New System.Net.WebClient()
'-Connect To the web service
Dim stream As System.IO.Stream = _
client.OpenRead(webServiceAsmxUrl + "?wsdl")
'Read the WSDL file describing a service.
Dim description As ServiceDescription = ServiceDescription.Read(stream)
'LOAD THE DOM'''''''''''''''''''''''''''
'--Initialize a service description importer.
Dim importer As ServiceDescriptionImporter = New ServiceDescriptionImporter()
importer.ProtocolName = "Soap12" ' Use SOAP 1.2.
importer.AddServiceDescription(description, Nothing, Nothing)
'--Generate a proxy client.
importer.Style = ServiceDescriptionImportStyle.Client
'--Generate properties to represent primitive values.
importer.CodeGenerationOptions = System.Xml.Serialization.CodeGenerationOptions.GenerateProperties
'Initialize a Code-DOM tree into which we will import the service.
Dim nmspace As CodeNamespace = New CodeNamespace()
Dim unit1 As CodeCompileUnit = New CodeCompileUnit()
unit1.Namespaces.Add(nmspace)
'Import the service into the Code-DOM tree.
'This creates proxy code that uses the service.
Dim warning As ServiceDescriptionImportWarnings = _
importer.Import(nmspace, unit1)
If warning = 0 Then
'--Generate the proxy code
Dim provider1 As CodeDomProvider = _
CodeDomProvider.CreateProvider("VB")
'--Compile the assembly proxy with the // appropriate references
Dim assemblyReferences() As String
assemblyReferences = New String() {"System.dll", _
"System.Web.Services.dll", "System.Web.dll", _
"System.Xml.dll", "System.Data.dll"}
Dim parms As CompilerParameters = New CompilerParameters(assemblyReferences)
parms.GenerateInMemory = True
Dim results As CompilerResults = provider1.CompileAssemblyFromDom(parms, unit1)
'-Check For Errors
If results.Errors.Count > 0 Then
Dim oops As CompilerError
For Each oops In results.Errors
System.Diagnostics.Debug.WriteLine("========Compiler error============")
System.Diagnostics.Debug.WriteLine(oops.ErrorText)
Next
Throw New System.Exception("Compile Error Occurred calling webservice.")
End If
'--Finally, Invoke the web service method
Dim wsvcClass As Object = results.CompiledAssembly.CreateInstance(serviceName)
Dim mi As MethodInfo = wsvcClass.GetType().GetMethod(methodName)
Return mi.Invoke(wsvcClass, args)
Else
Return Nothing
End If
Catch ex As Exception
Throw ex
End Try
End Function
End
Class
- Copy and paste the code for the main class.
Public Sub Main()
'Local web services URL
Dim WebserviceUrl As String = "http://localhost/OBIDEX_SERVICE/OBIEE_Service.asmx"
'specify service name
Dim serviceName As String = "OBIEE_Service"
'specify method name to be called
Dim methodName As String = "RunReportPathSiebel"
'Arguments passed to the method
Dim arArguments(1) As String
'Our web service has two parameters ReportID and ReportPath in OBIEE
arArguments(0) = "reportname"
arArguments(1) = "/users/userid/foldername/reportname"
Dim objCallWS As New WS
Dim sSessionId As String
sSessionId = CStr(objCallWS.CallWebService(WebserviceUrl, serviceName, methodName, arArguments))
'MsgBox("new SessionID: " & sSessionId)
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
- Debug and run the script task.
Good Luck !!
RSingh
