I found an interesting blog entry recently that discusses calling the PerformancePoint Planning PPSCmd.exe remotely from an SSIS Control Flow task. I couldn’t find a place to add a comment on the site, so I decided to create a separate entry on my blog.
Here’s the link to Norm’s blog:
In our scenario, the SSIS package resides on a database server, which is separate from the PPS Planning server. Initially, I thought we couldn’t install the PPS Planning client on the database server (due to licensing issues), so we wrote a VB.Net application that remotely called PPSCmd on the PPS Planning server. After I used this for a few months, I realized we could just as easily install the PPS Planning client on the database server, so the remote call was no longer needed. In Norm’s blog, he suggests downloading an application from Microsoft called PSEXEC to assist in this remote call if you can’t have the ppscmd.exe file on your SSIS server. If you choose to write a custom app to perform this function, you can borrow some of the code below. SSIS calls the VB dll from a Script Task. You’ll need to add the assembly as a Reference in your script task.
You can download the VB code here.
RemoteProcessCaller: A VB dll that runs and manages a remote process
The component has a class named “RemoteProcess” and its constructor takes five arguments:
– CommandLine (string -the path and file to execute)
– Machine Name (string -the server on which to execute the process)
– UserName (string -the login account to the remote server)
– Password (string -the pw for the login account)
– Timeout (int – the time in milliseconds to run the process.)
The “RemoteProcess” class contains a string function called “RunProcess” which returns a “0” for success and the error message if applicable.
Here’s the SSIS Script Task code:
Public Sub Main()
Dim t As New RemoteProcess(“c:\Program Files (x86)\Microsoft Office PerformancePoint Server\3.0\BizModeler\PPSCmd.exe StagingDB /Operation LoadDataFromStaging /Server http://localhost:46787 /Path “”<path>.model:<modelsite name>”” /NoValidation”, “<servername>”, “<userid>”, “<password>”, 800000)
If Not t.RunProcess() = “0” Then
Throw New Exception(t.RunProcess())
Dts.TaskResult = Dts.Results.Failure
Dts.TaskResult = Dts.Results.Success
Hope you find this helpful. I found it much easier to simply install the PPS Planning ppscmd.exe on the same server as the SSIS package, so we decided to scrap this work. Having the PPSCmd.exe local just requires changing the server argument to use the server name rather than localhost. But if you’re in a situation where you must perform a remote process call, you can try adapting this code, or merely download the PSEXEC application from the link on Norm’s blog. I’ve never tried the PSEXEC application so I cannot attest to it one way or the other. Let me know how it works out for you.