Ok, im making some progress. So what i have is a Message Queue Task which is bound to a message queue connection manager (which 'tests' ok). The Message Queue Task is set to recieve, variable from string message (declared a variable of type string) and to remove the message from the queue. The output of that task is piped into the data flow task.
The data flow task expands into a XML Source which is configured to get its input from the string i declared in the Message Queue Task and i point the schemas path to an appropriate schema. I then pipe the output of that into a SQL server destination which ive mapped all the columns from the XML message to a table (which the SQL server destination created for me).
It all looks good on paper, and builds properly with no errors etc. There is already a message in the appropriate private queue. When i go to debug it, it just sits on the Message Queue Task node (its yellow) and goes no further. No data is put into the DB. I have put a watcher on the link between the XML Source and the SQL server destination, and can see no data being piped through.
Even if i send another message, the execution of my package doesnt step passed the Message Queue Task. Its just sitting there waiting for something? what? I thought it would block until there was a message on that queue, and then process it if and when it arrives. But it doesnt seem to do that.
Any ideas?
I read on MSDN that you need integration services installed. I have checked and i do, and its running. Is theres something else i need to configure?
Help!
Hi
This works for me on either private or public Message Queue. How did you send the message to the private message queue?
This is what I'm doing
Create a MSMQ task (MSMQ Connection to machinename\private$\juantest1). Create a string variable in the package: sendVariable (value: ValueSend). In The MSMQ Task use the above connection, select send variable and use the create variable. Execute the package
Create another package MSMQReceive (same connection manager). Create a string variable recVariable (value: Default value). In the MSMQ Task select the correct MSMQ connection and for message select Receive Message, message type= variable message; variable = user::recVariable. Put a break point after execution. Execute the package. The value of variable recVariable = ValueSend
Juan Acosta (Microsoft SSIS Team)
This posting is provided "AS IS" with no warranties, and confers no rights
|||Hi Juan, thanks for the reply. Its interesting you ask 'how am i sending it?' I wouldnt have thought it would matter. An MSMQ message should be independant of how it was sent, shouldnt it? Im actaully sending it from a small console .NET 1.1 C# app using the standard .NET MQ library (system.messaging?) Its just a simple XML message. I can post the console app code if you think that would help?
I could also build the sender app in SSIS and see if that works for me. If it does we know its something with the way im sending the message - but that seems a bit counter-intuitve to me.
Thanks again, will let you know how i get on.
|||Hi Juan. I have just made another SSIS package which is a simple MSMQ task which is set to send to the private queue i have set up withe the Message Queue Connection Manager (which tested ok). That package runs and says it completed fine, and it posts a message to the private queue (.\private$\testq). I can see the message from the computer managment applet etc.
I run the reciever app and it just sits there doing nothing. Waiting on the MSMQ task.
The way i have it set up is on controlflow tab i have an MSMQ task which pipes into a Dataflow task. i have a break point set on the dataflow task node on the controlflow tab. It never hits that breakpoint, no matter how i sent the message (from my console app or from another SSIS package), whether theres a message there or not when i start the reciever app.
Another thing which MAY be of interest is that im running all this on a virtual PC (WMWare 5.0) running W2K SP4. I have not applied Yukon SP1 yet either.
Still nothing seems to work
|||have applied SQLSvr2005 SP 1 now, and it has made no difference :(
|||We dont need to install MSMQ triggers do we?
|||
Taurineman,
I was just having the same problem assisting a Microsoft Partner. But I found the solution. The message label is what the Message Queue task looks at for the type of message.
For a String Message set the label to "String Message" for a data file set the message label to "Data File Message"
This isn't very straight forward. The way I stumbled onto this is by setting up two Message Queue tasks in my SSIS package. The first one sends the message and the second one recieves the message. This runs just fine. I then I took a look at the messages to determine what I was missing from the messages I was sending from a C# project. It turns out the Label string needs to be exact.
Code To Send Message Correctly with C# project for Message Queue Task to process:
System.Messaging.Message recoverableMessage = new System.Messaging.Message()
recoverableMessage.Body = "Test";
recoverableMessage.Label = "String Message";
recoverableMessage.Recoverable = true;
MessageQueue msgQ = new MessageQueue(@."machinename\private$\810");
msgQ.Send(recoverableMessage);
|||
Unfortunately this forum does not have much discussion on using Message Queue Task.
I tried the above post by Oliver and it worked fine. I have a complex problem in using MSMQ task.
i have a sender package that has a MSMQ task and SQL Exec task. The SQL Exec task get data from SQL 2005 (like "select name from person"). And I used a variable (of type Object) "srcVariable" to store the full result set. I then use MSMQ to send this object to a local private queue. It worked fine. Note that since i used ADO.NET provider, so the actual data type for this object is "System.Data.DataSet". OLEDB provider does NOT work for MSMQ because the object is of type __ComObject that can NOT be serializable.
Next I have another receiver package that has a MSMQ task. The MSMQ task was able to read object from the queue. I use a package-level variable to store it. However, the returning type for this object is "String" but the value is "System.Data.DataSet".
Can any expert please suggest how to convert this object from String type to DataSet type so that it can be used downstream?
|||
So if I understand the objective, it is to store a dataset in MSMQ, and then to retrieve the same dataset in a separate IS package for downstream use.
SSIS will not serialize the the Object ("srcVariable") which contains the DataSet for you. Because of that, when your receiver task picks up the message, all you have is what amounts to the ToString() call on a DataSet, which returns "System.Data.DataSet".
For example, it would be the same in powershell to do the following:
(new-object System.Data.DataSet).ToString()
"System.Data.DataSet"
Instead, you can serialize the DataSet object instance to an SSIS String variable in the sender, and then reconstitute it back from a string to a DataSet in the receiver. For example, if you have in sending package two SSIS variables (an Object named "DataSet" which contains a DataSet and a String named "SerializedDataSet"), and in the receiving package the same two variables, you could use the following script tasks to handle the serialization/de-serialization of the dataset. The DataSet could then be used downstream, perhaps in foreach loop container or a dataflow task.
The first task is used in the sender to serialize the dataset to a string prior to that string's transmission via a MSMQ task. The second is for use in the receiver package after the MSMQ message receipt to de-serialize the stringified dataset for downstream use.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Imports System.Text
Public Class ScriptMain
Private shouldTerminate As Boolean = False
Private sourceObject As Object
Private sourceDataSet As DataSet
Private stringifiedDataSet As StringBuilder = New StringBuilder()
Public Sub Main()
Dts.TaskResult = Dts.Results.Success
Try
sourceObject = Dts.Variables("DataSet").Value
sourceDataSet = DirectCast(sourceObject, DataSet)
Using sw As StringWriter = New StringWriter(stringifiedDataSet)
sourceDataSet.WriteXml(sw, XmlWriteMode.WriteSchema)
' stringify DataSet for transmission
End Using
Dts.Variables("SerializedDataSet").Value = _
stringifiedDataSet.ToString()
Catch ex As Exception
shouldTerminate = Dts.Events.FireError(1, _
ex.TargetSite.ToString(), ex.Message, String.Empty, 0)
Dts.TaskResult = Dts.Results.Failure
End Try
End Sub
End Class
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Imports System.Text
Public Class ScriptMain
Private shouldTerminate As Boolean = False
Private sourceObject As Object
Private stringifiedDataSet As String
Private targetDataSet As DataSet = New DataSet()
Public Sub Main()
Dts.TaskResult = Dts.Results.Success
Try
sourceObject = Dts.Variables("SerializedDataSet").Value
stringifiedDataSet = DirectCast(sourceObject, String)
Using sr As StringReader = New StringReader(stringifiedDataSet)
targetDataSet.ReadXml(sr, XmlReadMode.ReadSchema)
' reconstitute dataset
End Using
Dts.Variables("DataSet").Value = targetDataSet
Catch ex As Exception
shouldTerminate = Dts.Events.FireError(1, _
ex.TargetSite.ToString(), ex.Message, String.Empty, 0)
Dts.TaskResult = Dts.Results.Failure
End Try
End Sub
End Class
|||
This sounds a very plausible solution. I will try it out and post my finding back.
Just out of my curiosity, how do you know that SSIS does not serialize DataSet object while it is sent to Q ? Any documentation info ?
In the MSMQ receiving task, there is a type of "String To Variable"... then what does this really mean ? Does this reconstruct object from a serialized XML string?
|||SSIS does serialize the DataSet stored in the IS Object variable, along with other IS variable types (using a SOAP formatter as you noted in another thread), when using the message type of "Variable Message". The MSMQ task in receive mode just doesn't understand its own sent messages, excepting strings. That is, the MSMQ task is incompatible with itself, when used to send and then receive a non-string variable message type.
Hence the need, at least right now, to use IS String variables (or IS Object variables which can wrap strings), when using the MSMQ task to receive a variable message.
To my knowledge, the "inability to deserialize" non-strings is not documented, as the receive side of the MSMQ task doesn't limit you to variables of type String or Object, when in receive variables mode.
|||
This is really good stuff...
Unfortunately i cannot make it work by using the script above.. Did I miss anything ? Here is what I did.
1. Inside sender package, I used your sending script above, and was able to send to Q using "String message" as Message Type. Inside StringMessage column, I keyed in "User::SerializedDataSet". This works fine and the message got sent into the Q.
2. Inside receiver package, I have a receiving MSMQ task which is followed by a script task (that is copied and pasted your above receiving script). For MSMQ task, in the "Receive" property page, i configured MessageType to be "String message to variable" and variable to be "User::SerializedDataSet". And this MSMQ task works fine. BUT, the script task blows up at:
targetDataSet.ReadXml(sr, XmlReadMode.ReadSchema)
Anything is wrong here? Please help.
BTW - I also tried String Message (as opposed to "String message to variable") for MessageType inside receiver package and did not get it work either.
|||
 Steve Wang 2006 wrote: |
|
This is really good stuff... Unfortunately i cannot make it work by using the script above.. Did I miss anything ? Here is what I did. 1. Inside sender package, I used your sending script above, and was able to send to Q using "String message" as Message Type. Inside StringMessage column, I keyed in "User::SerializedDataSet". This works fine and the message got sent into the Q. Set the expression on the Message String property of the sending MSMQ task to the IS string variable User::SerializedDataSet. That is, the expressions node for the task should look like:
MessageString => @.[User::SerializedDataSet]
This as opposed to the literal value of "User::SerializedDataSet" in the StringMessage property. 2. Inside receiver package, I have a receiving MSMQ task which is followed by a script task (that is copied and pasted your above receiving script). For MSMQ task, in the "Receive" property page, i configured MessageType to be "String message to variable" and variable to be "User::SerializedDataSet". And this MSMQ task works fine. BUT, the script task blows up at: targetDataSet.ReadXml(sr, XmlReadMode.ReadSchema) Anything is wrong here? Please help. The "blow up" on the .ReadXml line is a consequence of the message not containly xml (yet). As long as the message does not contain xml, this DataSet.ReadXml() method will throw an exception. So fixing the sending package to use an expression containing xml for the task's StringMessage property will "fix" the receiver task too. BTW - I also tried String Message (as opposed to "String message to variable") for MessageType inside receiver package and did not get it work either. The MessageType property inside the receive task should be set to "String Message to Variable", loading the message contents into the receiver package's IS string variable User::SerializedDataSet.
|
|||
Thank you much... Both Sender package and receiver packages worked great.
I also tried to send a DataSet object from C# code to the same Q, it worked as well. Here is what I did...for reference purpose.
Inside C# code, get data from database and create a DataSet object. Then use the serilization above to create an XML string which is sent to the Q next. Some tricky stuff here is: Be sure to use the identical lable as noted above AND approrpiate formatter which is ActiveXMessageFormatter for the message. I spent quite a bit time to figure this out. I am assuming that SSIS uses this formatter but I did not find any documentation on this. Otherwise SSIS's MSMQ Receiving task won't work.
Of course once SSIS MSMQ Receiving task reads the string, it needs to convert to DataSet as jaegd pointed out above.
|||
As an extra mile to go....
i tried inside C# code to receive the message from Q that is sent from SSIS MSMQ sender package... and I was not able to get the message by using "ActiveXMessageFormatter" which was applied to the Q instance. It blows up at Receive() method call by throwing the following error:
"cannot deserialize the message passed as an argument. Cannot recognize the serialization format"
Any suggenstion ?
