Between Assignments

I am currently between assignments right now.  I just rolled off the client that I had been consulting at for nearly the past four years.  We accomplished a great deal in the areas of Business Intelligence and Corporate Performance Management.  We implemented PerformancePoint Planning into production prior to the software being released to the public.  If you have any BI / CPM needs in the St. Louis, Missouri area, shoot me an email at dstre at hotmail dot com, and we can discuss how I, along with my employer, might be able to assist you.  Looking forward to hearing from you!

Remote PPSCmd Call

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
        End If
     End Sub

End Class
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.


SSIS Script Task bug

I was running Microsoft SQL Server Integration Services Designer Version 9.00.2047.00 and encountered the following error when using the Script Task component in SSIS 2005.

The task is configured to pre-compile the script, but the binary code is not found.  Please visit the IDE in the Script Task Editor by clicking Design Script button to cause the binary code to be generated.

In your script window there is a setting called PrecompileScriptIntoBinaryCode.  If you set this to False, the error will disappear, but you won’t be able to run the package any longer through the SQL Agent. 

This error is a bug and MSFT has come up with a fix.  The problem is caused by a recent change to the CLR (Common Language Runtime) workstation runtime for builds of the Microsoft .NET Framework 2.0.  See Microsoft kb article:  KB932556.  I installed SSIS SP2, and it corrected the issue.      
After the upgrade I am now running Version 9.00.3042.00.  However, once I opened my SSIS package and set the PercompileScriptIntoBinaryCode to True, and I still got the same error.  I had to open the script and hit the space bar a few times, resave the script, and the error disappears.



Posted in SSIS. 5 Comments »

Hello World!

Cheers to my first blog entry.  This blog will be focused on BI solutions using the Microsoft BI tool suite with a emphasis on PerformancePoint Server 2007.  We currently have PPS CTP4 Planning running in a production environment.  I’ve been focusing on the data integration aspects of PPS and I will use this blog to share my experiences and provide tips that will hopefully help others.  I’ve been using SQL Server 2005 Integration Services (SSIS) to perform this task, so you will likely find many entries on SSIS also.  Thanks for dropping by and I hope to have many more entries published in the near future.