The Remote Procedure Call Failed

I have a named instance of SQL Server where I had to do an edition upgrade from SQL Express to Standard edition. A while later I notice that the SQL Agent is not running. I go to SQL Server Configuration Manager to start the Agent. First I need to change the start mode to either manual or automatic. However, I get the error "The remote procedure call failed." I try changing the log on account to something else but I am still unable to start the Agent. I search and Google and finally find this little weird tip. Do this in the service mmc! Normally you do not want to make any changes to SQL Service related items there but for some strange reason that was the solution for me as well.

SSIS Logging with Custom Messages

SSIS Logging is a great thing. I use it in every single SSIS Package I create. I normally use the SSIS log provider for SQL Server. I am not going to explain here how to set up logging for SSIS. There are plenty of other blog posts or BOL articles about that. Here all I was going to discuss how to add your own custom messages to log.

Drag the Script task control flow item on the Control Flow area and connect it to the desired component or components. Name the script task component wisely. The name of the script task will show up in the log's source column.

Double-click the script task and specify the ReadOnlyVariables if you want to display their values as your custom message. In this example I use Microsoft Visual Basic 2008 as my script language as that is my preferred coding choice because I just can't stand the curly braces of C#. :)

Click Edit Script which will bring up the script editor. Now look at the big block of comments. The information I am providing in this log is right there!

' To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, True)

This is what I used in an existing SSIS package I created.



Public Sub Main()
        Dts.Events.FireInformation(1, "Filename", Dts.Variables("User::SchemaRawfileLocation").Value, "", 0, False)
        Dts.TaskResult = ScriptResults.Success
 End Sub

So you can see that I created an Information event and my message is the value of the SchemaRawfileLocation variable.

On the msdn site you can look at the parameters to pass into the method. Not 100% sure on all the choices but my above example has been working.

These are the parameters:

FireInformation(informationCode as Integer, subComponent as String, description as String, helpFile as String, helpContext as Integer, byRef fireAgain as Boolean)

In my example:

informationCode: 1

subComponent: "FileName" (this does not seem to display in the SQL log though)

description: Dts.Variables("User::SchemaRawfileLocation").Value (the value of the variable, it's displayed in the message column)

helpFile: ""

helpContext: 0

fireAgain: False (no I do not want to fire this event again)

And there it is. Happy coding!