Metadata on data flow path not updating

I have a data flow task with a single source and destination task. I'm having the source task creating a table from a variable expression and the destination table also created from a variable expression. I'm running this under 3 scenarios in which each scenario has a different source and destination table. They are different in name but close in table structure with the exception of one column being different. The Metadata for the source flow path seems to be "sticky" in that it is not modifying the source table structure in the flow to account for this different column. I'm not sure how to adjust this. Any ideas? I've modified several properties in the task and data flow but nothing seems to make this adjustment in run-time.Metadata cannot and does not change dynamically nor at runtime.

This is by design and there's very little you can do to get around that, short of coding your own application using the SSIS API.

metadata driven SSIS package--table variable

Is there anyone know if a simple SSIS package (moving data from source table to target table) or task can be called repeatedly using a variable that obtains value once a time from a metadata table containing only table names. Basically, I would like to pass in a table variable to the SSIS package or task to start the ETL for different tables. Thanks a lot!

Oliver

Hmm,

I suppose you could do it that way. I am currently generating complete SSIS packages from metadata. No variables. Explicit references. You would still need to handle the objects of the package that change, like columns datatypes, etc...|||You cannot have 1 data-flow that is used for different tables (unless the structure of each table is exactly the same - which I doubt).

You will need multiple data-flows, one for each table.

Once you have your multiple data-flows, decide which one of them to execute using workflow expressions. Allan and Darren have a good example of this here: http://www.sqlis.com/default.aspx?306

-Jamie|||Patrick,

Do you think you could post a simple example of what you are doing? I think there are a lot of people in the SSIS community who are realizing they need to head toward dynamic package creation in order to achieve the maintainability that they were able to with DTS and ActiveX scripts.

Thanks,

Jason.|||Basicall, we have 150 tables for ETL process. The transformation of each table is more or less the same. We would like to creat a SSIS package for one table,and all the other tables to be constructed based on the table name retrieved from a metadata table that contains all the table names. Thanks!

Oliver|||

Oliver77 wrote:

Basicall, we have 150 tables for ETL process. The transformation of each table is more or less the same. We would like to creat a SSIS package for one table,and all the other tables to be constructed based on the table name retrieved from a metadata table that contains all the table names. Thanks!

Oliver

"More or less the same" isn't enough. They have to be exactly the same for them to be able to use the same data-flow.

-Jamie|||

Can we create a SSIS package using VB .NET? If we can, we can call metadata driven stored procedure to build the transformation task specifically for each table.

|||

Could you give more detail as how you generate the metadata driven SSIS packages? THanks

Oliver

|||

Oliver77 wrote:

Could you give more detail as how you generate the metadata driven SSIS packages? THanks

Oliver

One of the samples provided with the product shows how to build a package progamatically (although I've just looked at my Junne CTP installation and can't find it - perhaps its been removed?). Once you have mastered how to do that you could work on building them based on metadata. I haven't seen any examples of doing that - perhaps you'd be the first person to do it?

-Jamie|||Patrick,

DO you have sample code? Thanks!|||Patrick,

Can you give some rough ideas as what you are using in terms of programming languages, scripts or SSIS tasks to create metadata driven SSIS packages.

Many thanks!

Oliver

Message Queue Task 64 bit Cluster issue

Hello,

I'm using the Message Queuing task to create a local private queue message. Everything works great on a 32 bit machine. When I try this on a 64 bit Itanium Cluster I keep getting the message "Message queue service is not available" in my SSIS log. I've using this string as my path "ClusterName\private$\QueueName". Does anyone know of any issues with the Message Queue task on 64 bit or a cluster? The Message Queue service is up and running, it doesn't make sense.

Thanks,

Andy

I found out that you must install MSMS on each node of the cluster separately. Then set up your Message Queue using the Cluster Administrator. See this document for help.

http://download.microsoft.com/download/4/f/5/4f518f76-c1ce-431b-b79f-71caf9e27578/MSMQ3incluster.doc

Also, it's easier if you use the utility mmcv.exe to set up the cluster:

http://support.microsoft.com/?kbid=898701&SD=tech

Andy

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 ?

Message Queue Task

Simple Question.

I have a requirement to read XML Messages from a Remote private MSMQ.

These messages are essentially Database records that will need cleaning up and insterting into a Local Database table.

Is this possible with SSIS?

Would Biztalk be a more suitable tool for this type of process?

If its possible, are there any resources taht can point me in the right direction?

Thanks for your help!

J.

That is what the Message Queue task is for, so it seems possible, though I don't have personal experience with using it to read a Remote private queue.

message output from XSLT XML Task

Where does output from <xsl:message> stylesheet elements go? It's not in the Progress or Output window, and there doesn't seem to be a property that controls the destination for messages.

Kevin Rodgers wrote:

Where does output from <xsl:message> stylesheet elements go? It's not in the Progress or Output window, and there doesn't seem to be a property that controls the destination for messages.

I have the same question.

The ability to see the output from <xsl:message> would partially make up for the inability to set breakpoints in XSLT.

|||

Hi Kevin,

Interestingly enough, they go to standard output, which is really only visible if you're running dtexec directly. This is the default of the underlying System.Xml.Xsl engine, which the XML Task's XSLT operation is based on.

-David

|||

David, as I'm sure you know, it's possible for code to capture the output ot <xsl:message/>, and to send the output whereever it likes.

Good tip on standard output, though.

message output from XSLT XML Task

Where does output from <xsl:message> stylesheet elements go? It's not in the Progress or Output window, and there doesn't seem to be a property that controls the destination for messages.

Kevin Rodgers wrote:

Where does output from <xsl:message> stylesheet elements go? It's not in the Progress or Output window, and there doesn't seem to be a property that controls the destination for messages.

I have the same question.

The ability to see the output from <xsl:message> would partially make up for the inability to set breakpoints in XSLT.

|||

Hi Kevin,

Interestingly enough, they go to standard output, which is really only visible if you're running dtexec directly. This is the default of the underlying System.Xml.Xsl engine, which the XML Task's XSLT operation is based on.

-David

|||

David, as I'm sure you know, it's possible for code to capture the output ot <xsl:message/>, and to send the output whereever it likes.

Good tip on standard output, though.

MERGING Variable in FOR LOOP COntainer

Hi All,

Seems like a simple task, but been a struggle.

Simply trying to move a group of files from one folder to another folder and renaming the files with the monthyear in the middle of the filename..

I'm using a FOR Loop container and works find. The added complexity is I'm trying to rename the files the same tine and putting the Month Year into the file name.

I guess the struggle is how to get the file name out so I can manipulate it.

I tried creating variable(V_SOURCE) which stores the file path and create another variable(V_FILENAME) to hold the filename. I believe on the expression page of the FOR LOOP editor if I select the the file name and extension radio button, it should put the file name in the V_FILENAME variable.

In my file task trying to join the two variables together in another variable, keep saying my path is wrong with the file task kicks off.

here's the syntaxt

@.[User::V_SourcePath] + @.[User::V_FILE_NAME]

JUst to follow up if someone can help me with syntaxt for the merge and if they have a better approach

to moving and renaming the files

|||

I haven't checked myself, but do you need to have a \ between the folder and file, or is it included?

You can check the value of your variables by setting a breakpoint and typing the variable into the watch window.

|||THanks is helping out getting a picture of what's going on|||

Dan Cleary wrote:

JUst to follow up if someone can help me with syntaxt for the merge and if they have a better approach

to moving and renaming the files

I have done that using the File System task. I just posted an example in my blog:

http://rafael-salas.blogspot.com/2007/03/ssis-file-system-task-move-and-rename.html

I hope you find it helpful.

|||

Rafeal, looks like just what the docotor ordered, does the scope of the variable make a difference?

Having an issue when joining the SourcePath with the file name. The scope of those variables are at the package level not at For Loop.

I do appreciate your response

|||

Variable scope is not an issue here; if you need it, just define all the variables at the package level.

regards

|||

Rafeal your Blog was great and very useful. I uess my struggle is doing the syntaxt on the file name in the expression builder.

I'm trying to take a group of file that are currently named RPT_BANK_NAME_@.MONTHYEAR_BANKNAME.XLS and

convert it to RPT_BANK_NAME_03_2007_BANKNAME.XLS. I have the report named storec in the variable just not sure on the syntaxt to strip out the @.MONTHYEAR and replace with the month and the year.

Any suggestions?

I tried to do a substring but not recognizing that function in the eexpresion builder

|||

Dan,

I guess I don't understand what is the format of the original name of the file. What I don;t get is the @.MONTHYEAR part.

is 03_2007 the month and year of the package execution date? or are they part of the original file name?

|||

THe @.monthyear is confusing, it's just hardcoded in the report name.

SO what I'm attempting to do in my expresion is take the Variable which is storing the report name

RPT_BANK_NAME_MONTHYEAR_BANKNAME.XLS and replace the monthyear with the curent Month and date.

I tried this but the expresion keeps failing the validation checks. Pretty much what you had in your blog

@.[User::V_DestinationFolder] + SUBSTRING( @.[User::V_Invoice_File] , 1 , FINDSTRING( @.[User::V_Invoice_File],".",1) 1 ) + "-" + (DT_STR, 2, 1252) Month( @.[System::StartTime] )+ (DT_STR, 4, 1252) Year( @.[System::StartTime] )+ SUBSTRING( @.[User::V_Invoice_File] , FINDSTRING( @.[User::V_Invoice_File],".",1) , LEN( @.[User::V_Invoice_File] ) )

|||

You've got an extra 1 in the expression (see red below) After correction, and assuming V_DestinationFolder is c:\temp\, it gives c:\temp\RPT_BANK_NAME_@.MONTHYEAR_BANKNAME.-32007.XLS, which I don't think is exactly what you want.

@.[User::V_DestinationFolder] + SUBSTRING( @.[User::V_Invoice_File] , 1 , FINDSTRING( @.[User::V_Invoice_File],".",1) 1 ) + "-" + (DT_STR, 2, 1252) Month( @.[System::StartTime] )+ (DT_STR, 4, 1252) Year( @.[System::StartTime] )+ SUBSTRING( @.[User::V_Invoice_File] , FINDSTRING( @.[User::V_Invoice_File],".",1) , LEN( @.[User::V_Invoice_File] ) )

The one below returns c:\temp\RPT_BANK_NAME_3-2007_BANKNAME.XLS and uses the Replace function. However, I have not tested it in the context of Rafael's example, so you might need to make some modifications to get it to work for you.

@.[User::V_DestinationFolder] + REPLACE( @.[User::V_Invoice_File] ,"@.MONTHYEAR", ((DT_STR, 2, 1252) MONTH( GETDATE() )) + "-" + ((DT_STR, 4, 1252) YEAR( GETDATE() )) )

|||

John is spot on.

In your case, REPLACE is a better option since '@.MONTHYEAR' is a literal that is always part of the file name. The expression in my example adds the month and year at the end of the file name and before the file extension (.txt); so I used FINDSTRING.

BTW, notice the expressions we are given use @.system::startTime; which gives you the month and year of the package execution date; so make sure that meets your requirements.

|||

Guys, you've been a huge help! I'm doing a watch and looking at the V_DESTINATION_PATH and it shows the

following in the watch window

+ User::V_DestinationPath {E:\\Client Billing 2\\FTP_Incoming\\INVOICE\\RPT-234-3-2007-BILL234.xls} String

but when running the task getting a path error

[File System Task] Error: An error occurred with the following error message: "Could not find a part of the path.". , any idea?

|||Looks like your variable V_Destination_Folder contains too many slashes, perhaps.|||

When you view it in the watch window, it shows the escape characters, thus the doubled slashes. You can validate the value by using a script task with a MsgBox.

Dan, you might want to double-check that the folder and filename you are referencing exist. Also, check permissions to ensure you can write to that location.