Microsoft SQL Server 2000 Developer Edition

Hi,
Can anyone confirm that the Microsoft SQL Server 2000 Developer Edition
includes a fully functional version of Enterprise Manager.
I want to run a database at work using MSDE (it's a small but complex db).
Also, would the single user license be appropriate for work use (I will be
the only administrator/developer).
Regards,
Ron.Hi
Yes it does.
According to the EULA, developer edition may only be used for development
and not for production use.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:93C38BBF-48A6-4D08-B85B-701B3319CA36@.microsoft.com...
> Hi,
> Can anyone confirm that the Microsoft SQL Server 2000 Developer Edition
> includes a fully functional version of Enterprise Manager.
> I want to run a database at work using MSDE (it's a small but complex db).
> Also, would the single user license be appropriate for work use (I will be
> the only administrator/developer).
> Regards,
> Ron.|||To add to Mike's response, Developer Edition contains all the features of
the Enterprise Edition, including GUI client tools. The only difference is
licensing.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:93C38BBF-48A6-4D08-B85B-701B3319CA36@.microsoft.com...
> Hi,
> Can anyone confirm that the Microsoft SQL Server 2000 Developer Edition
> includes a fully functional version of Enterprise Manager.
> I want to run a database at work using MSDE (it's a small but complex db).
> Also, would the single user license be appropriate for work use (I will be
> the only administrator/developer).
> Regards,
> Ron.|||Thanks guys,
I think I'll have to stay with my current limitations then! (my company
won't spend much for smallish projects)
I can probably do what I need using Access 2000 as a front-end to MSDE. I
was hoping to make use of the DTS (and other) features of Enterprise Manager.
Alternatively, does anyone know of a good third-party MSSQL manager?
Regards,
Ron.

Microsoft SQL Server 2000 Developer Edition

Hi,
Can anyone confirm that the Microsoft SQL Server 2000 Developer Edition
includes a fully functional version of Enterprise Manager.
I want to run a database at work using MSDE (it's a small but complex db).
Also, would the single user license be appropriate for work use (I will be
the only administrator/developer).
Regards,
Ron.Hi
Yes it does.
According to the EULA, developer edition may only be used for development
and not for production use.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:93C38BBF-48A6-4D08-B85B-701B3319CA36@.microsoft.com...
> Hi,
> Can anyone confirm that the Microsoft SQL Server 2000 Developer Edition
> includes a fully functional version of Enterprise Manager.
> I want to run a database at work using MSDE (it's a small but complex db).
> Also, would the single user license be appropriate for work use (I will be
> the only administrator/developer).
> Regards,
> Ron.|||To add to Mike's response, Developer Edition contains all the features of
the Enterprise Edition, including GUI client tools. The only difference is
licensing.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:93C38BBF-48A6-4D08-B85B-701B3319CA36@.microsoft.com...
> Hi,
> Can anyone confirm that the Microsoft SQL Server 2000 Developer Edition
> includes a fully functional version of Enterprise Manager.
> I want to run a database at work using MSDE (it's a small but complex db).
> Also, would the single user license be appropriate for work use (I will be
> the only administrator/developer).
> Regards,
> Ron.|||Thanks guys,
I think I'll have to stay with my current limitations then! (my company
won't spend much for smallish projects)
I can probably do what I need using Access 2000 as a front-end to MSDE. I
was hoping to make use of the DTS (and other) features of Enterprise Manager
.
Alternatively, does anyone know of a good third-party MSSQL manager?
Regards,
Ron.

Microsoft SQL Server 2000 Developer Edition

Hi,
Can anyone confirm that the Microsoft SQL Server 2000 Developer Edition
includes a fully functional version of Enterprise Manager.
I want to run a database at work using MSDE (it's a small but complex db).
Also, would the single user license be appropriate for work use (I will be
the only administrator/developer).
Regards,
Ron.
Hi
Yes it does.
According to the EULA, developer edition may only be used for development
and not for production use.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:93C38BBF-48A6-4D08-B85B-701B3319CA36@.microsoft.com...
> Hi,
> Can anyone confirm that the Microsoft SQL Server 2000 Developer Edition
> includes a fully functional version of Enterprise Manager.
> I want to run a database at work using MSDE (it's a small but complex db).
> Also, would the single user license be appropriate for work use (I will be
> the only administrator/developer).
> Regards,
> Ron.
|||To add to Mike's response, Developer Edition contains all the features of
the Enterprise Edition, including GUI client tools. The only difference is
licensing.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:93C38BBF-48A6-4D08-B85B-701B3319CA36@.microsoft.com...
> Hi,
> Can anyone confirm that the Microsoft SQL Server 2000 Developer Edition
> includes a fully functional version of Enterprise Manager.
> I want to run a database at work using MSDE (it's a small but complex db).
> Also, would the single user license be appropriate for work use (I will be
> the only administrator/developer).
> Regards,
> Ron.
|||Thanks guys,
I think I'll have to stay with my current limitations then! (my company
won't spend much for smallish projects)
I can probably do what I need using Access 2000 as a front-end to MSDE. I
was hoping to make use of the DTS (and other) features of Enterprise Manager.
Alternatively, does anyone know of a good third-party MSSQL manager?
Regards,
Ron.

Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) Release A

My objective is to use Enterprise Manager to move (copy) my SQL db from the server to my windows desktop computer.

I downloaded MSDE and am having trouble installing it, no doubt because I do not understand the documentaion (ReadMeMSDE2000A.htm).

When I try to run setup, I get that message that says:

"A strong SA password is required for security reasons. Please use SAPWD switch to supply the same."

Considering my purpose, do I need a "strong" SA password? If not, how do I get around it? If yes, how do I set it up?

I am a Mac user so I have poor windows skills, please make it as painless as possible for me, thanks!

RonCreate 2 file in the same director with setup.exe

1. Setup.bat
setup.exe /settings Settings.ini /L*v C:/MSDELog.log

2. Settings.ini
[Options]
SAPWD=YourPassword
DISABLENETWORKPROTOCOLS=0
SECURITYMODE=SQL
DATADIR="C:\MSDE"
TARGETDIR="C:\MSDE"

Run the setup.bat|||I have tried your code and other code like that and in each case it fails. I keep getting this error message that says:

"The instance name specified is not valid."

Note that your code didn't even use the instance name.

In your code you use:

DATADIR="C:\MSDE"
TARGETDIR="C:\MSDE"

Actually on my C drive the folder is called MSDERela. I tried changing your code to indicate that and it still doesn't work. Same error message.

Any other suggestions? Thanks!|||I added the INSTANCENAME and added quote marks to some of the code and it seems to have worked. It produced a folder on my C drive wtih a number of files in it.

Now can someone tell me how to launch MSDE?

Now you know why I call myself SQL_Dummy!sql

microsoft reporting services

Hi,
In Microsoft Abalysis Manager one can browse the cube data by right
clicking the cube and drag and drop columns to view data. Can the
same be achieved with reporting services.
Thanks in advance
BajiraoI'm not sure if I understand your question perfectly. There is a preview
function that you can use in the Report Designer that allows you to see what
a report would look like, plus you can preview the data set. If you're
asking whether or not you can query OLAP cubes from Reporting Services, the
answer is that you can, but you need to know MDX in order to do so.
We are working on a whitepaper that addresses this topic. It assumes you
already have good knowledge of MDX. Given the number
of inquiries, I've gone ahead and posted it over on the OLAP MSN group. You
can find it in the general discussion are of.
http://groups.msn.com/OLAPServicesDevelopersForum/
Sean
Sean
Sean Boon
SQL Server BI Product Unit
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
"Bajirao.B.T" <btbajirao@.yahoo.com> wrote in message
news:736f0ba3.0403180730.4e63cfd5@.posting.google.com...
> Hi,
> In Microsoft Abalysis Manager one can browse the cube data by right
> clicking the cube and drag and drop columns to view data. Can the
> same be achieved with reporting services.
> Thanks in advance
> Bajirao|||Thanks for the reply. I my be not clear in expressing what I want.
In microsoft analysis manager on right click, browse cube data > one
form pops up. There we can drag and drop dimensions from the top to work
area. So that the cube takes different shapes.
The same functionality I want to achieve in the web browser useng
Reporting services is that possible or not is my question.
Regards,
Bajirao
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

Microsoft OLE DB Provider for ODBC Drivers error 80004005

Hi everybody
My problem with sql server is the following one:
I've got my backoffice manager application done with asp technology
that works on sql server installed on Win2000 and running on IIS5.
what I've done is simply doing a backup of my db and moving the asp
application on another server: Windows 2003 with IIS6 and sql server
vers.8.
Then I tried to set up everything as in the beginning on the previous
machine(Win2K), but I'm getting this error:

Microsoft OLE DB Provider for SQL Server error '80004005'
[DBNETLIB][ConnectionOpen (Connect()).]Server SQL inesistente o accesso
negato.
(translated would be "SQL Server doesn't exist or access denied")
/sitowebsite/bom/login.asp, line 13

...where in the line 13 is set the connection to the database:
Conn.Open Application("DBsource")

which should be driven by what is set inside the global.asa file:
Application("DBsource")="Provider=SQLOLEDB;server=127.0.0.1;database=db_nam e;uid=db_uid;pwd=db_pwd;"
Application("WS_DB_TYPE") = ( "SQLSERVER" )

I tried to restore the database just not losing information and nothing
happens.
I tried also to create a new user and modify the properties as it
should be from the previous settings in the db(db_owner,...etc), still
nothing.

any clue is appriciated.
many thanks in advance.ziottt (t.giommi@.gmail.com) writes:

Quote:

Originally Posted by

My problem with sql server is the following one:
I've got my backoffice manager application done with asp technology
that works on sql server installed on Win2000 and running on IIS5.
what I've done is simply doing a backup of my db and moving the asp
application on another server: Windows 2003 with IIS6 and sql server
vers.8.
Then I tried to set up everything as in the beginning on the previous
machine(Win2K), but I'm getting this error:
>
Microsoft OLE DB Provider for SQL Server error '80004005'
[DBNETLIB][ConnectionOpen (Connect()).]Server SQL inesistente o accesso
negato.
(translated would be "SQL Server doesn't exist or access denied")
/sitowebsite/bom/login.asp, line 13

This error message that the application cannot reach the SQL Server
you are trying to connect to. Thus, users or backups have no importance,
because you don't reach that far.

Quote:

Originally Posted by

...where in the line 13 is set the connection to the database:
Conn.Open Application("DBsource")
>
which should be driven by what is set inside the global.asa file:
>
>Application("DBsource")="Provider=SQLOLEDB;server=127.0.0.1;database=db_nam
>e;uid=db_uid;pwd=db_pwd;"

And SQL Server is running on the same server as the web server? Have you
verified that SQL Server is actually running?

Diclaimer: I don't know ASP, so if there are any problems with that
global.asa, I can't tell.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Microsoft Enterprise Manager cannot "see" an instance of Microsoft MSDE-SQL - Server

Hello,
i have following Problem:
i have created a new instance of Microsoft SQL - Server with MSDE and now i
want to connect to a database on this instance through a network.
But i do not "see" the instance ...
I have tested it with
- Enterprise Manager
- an Microsoft Access .adp - Project
- MSDE+ SQL-Server Management Tool
Also: The server is started, this was my first look up .....
Who can help me'
Thanks in the beginning ...
PeterHi
Have a look at
http://support.microsoft.com/default.aspx?scid=kb;en-us;814130
and
http://support.microsoft.com/default.aspx?scid=kb;en-us;827204
Regards
Mike
"stundner@.oca.at" wrote:
> Hello,
> i have following Problem:
> i have created a new instance of Microsoft SQL - Server with MSDE and now i
> want to connect to a database on this instance through a network.
> But i do not "see" the instance ...
> I have tested it with
> - Enterprise Manager
> - an Microsoft Access .adp - Project
> - MSDE+ SQL-Server Management Tool
> Also: The server is started, this was my first look up .....
>
> Who can help me'
>
> Thanks in the beginning ...
> Peter
>
>

mgmt studio-View user permissions?

Using SQL server 2000 Enterprise Manager, one can bring up the properties of
a database user, click the Permissions button, and see all the permissions
that have been set for Stored Procs, tables etc... One can modify these as
needed.
How do I use Management Studio to do this?
I tried selecting the user (under security for that database) and was
expecting to see it under the Securables page under the Explicit permissions
- but I don't see it there - the user has been granted execute permissions to
some of the stored procedures and I can verify that by selecting the stored
proc and selecting the Permissions page under it's properties...
I am running SQL server 2005 Standard on a WinXP SP2.
Hi,
In user properties window and securables, try adding all stored procedures
with Add button.
Then you select all objects of type and select stored procedures.
Then just click on stored procedure and permissions should be written in
bottom of the window.
Danijel Novak
"Bob" <Bob@.discussions.microsoft.com> wrote in message
news:43F72B0A-113D-4A7E-A97D-0D63C77EF41B@.microsoft.com...
> Using SQL server 2000 Enterprise Manager, one can bring up the properties
> of
> a database user, click the Permissions button, and see all the permissions
> that have been set for Stored Procs, tables etc... One can modify these
> as
> needed.
> How do I use Management Studio to do this?
> I tried selecting the user (under security for that database) and was
> expecting to see it under the Securables page under the Explicit
> permissions
> - but I don't see it there - the user has been granted execute permissions
> to
> some of the stored procedures and I can verify that by selecting the
> stored
> proc and selecting the Permissions page under it's properties...
> I am running SQL server 2005 Standard on a WinXP SP2.
|||Thanks for the quick response Danijel. I guess I was looking for something
similar to what Enterprise manager offered "List only objects with
permissions for this user" option when we select the permissions for a user
in the database context.
Is there a way to see these?
Thanks again!
"Danijel Novak" wrote:

> Hi,
> In user properties window and securables, try adding all stored procedures
> with Add button.
> Then you select all objects of type and select stored procedures.
> Then just click on stored procedure and permissions should be written in
> bottom of the window.
> --
> Danijel Novak
>
> "Bob" <Bob@.discussions.microsoft.com> wrote in message
> news:43F72B0A-113D-4A7E-A97D-0D63C77EF41B@.microsoft.com...
>
>
|||Hi,
For that purpose I'm using following T-SQL...
SELECT OBJECT_NAME(major_id), permission_name, state_desc FROM
sys.database_permissions WHERE grantee_principal_id = USER_ID('db_user')
Hope this is what you're looking for...
Danijel Novak
"Bob" <Bob@.discussions.microsoft.com> wrote in message
news:8B465B77-A3D1-4B4C-80ED-A88DCD4B93D6@.microsoft.com...[vbcol=seagreen]
> Thanks for the quick response Danijel. I guess I was looking for something
> similar to what Enterprise manager offered "List only objects with
> permissions for this user" option when we select the permissions for a
> user
> in the database context.
> Is there a way to see these?
> Thanks again!
> "Danijel Novak" wrote:

Message Queue Task

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 ?

Message Queue Task

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 ?

Message Queue Task

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

PublicClass ScriptMain

Private shouldTerminateAsBoolean =False

Private sourceObjectAsObject

Private sourceDataSetAs DataSet

Private stringifiedDataSetAs StringBuilder =New StringBuilder()

PublicSub Main()

Dts.TaskResult = Dts.Results.Success

Try

sourceObject = Dts.Variables("DataSet").Value

sourceDataSet =DirectCast(sourceObject, DataSet)

Using swAs StringWriter =New StringWriter(stringifiedDataSet)

sourceDataSet.WriteXml(sw, XmlWriteMode.WriteSchema)

' stringify DataSet for transmission

EndUsing

Dts.Variables("SerializedDataSet").Value = _

stringifiedDataSet.ToString()

Catch exAs Exception

shouldTerminate = Dts.Events.FireError(1, _

ex.TargetSite.ToString(), ex.Message,String.Empty, 0)

Dts.TaskResult = Dts.Results.Failure

EndTry

EndSub

EndClass

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.IO

Imports System.Text

PublicClass ScriptMain

Private shouldTerminateAsBoolean =False

Private sourceObjectAsObject

Private stringifiedDataSetAsString

Private targetDataSetAs DataSet =New DataSet()

PublicSub Main()

Dts.TaskResult = Dts.Results.Success

Try

sourceObject = Dts.Variables("SerializedDataSet").Value

stringifiedDataSet =DirectCast(sourceObject,String)

Using srAs StringReader =New StringReader(stringifiedDataSet)

targetDataSet.ReadXml(sr, XmlReadMode.ReadSchema)

' reconstitute dataset

EndUsing

Dts.Variables("DataSet").Value = targetDataSet

Catch exAs Exception

shouldTerminate = Dts.Events.FireError(1, _

ex.TargetSite.ToString(), ex.Message,String.Empty, 0)

Dts.TaskResult = Dts.Results.Failure

EndTry

EndSub

EndClass

|||

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 ?