flow of events while sending message from one serivce two another

Hello,

I want to know the exact flow of events when I use this statement:

BEGIN DIALOG CONVERSATION @.dialog_handle
FROM SERVICE [SERVICE1]
TO SERVICE 'SERVICE2'
ON CONTRACT [MainContract]

Assuming that I have defined SERVICE1 for Queue1 in the initiator ,

and I have defined SERVICE2 for queue2 on the target.

Is this the flow:

1.Message first goes to the queue1
or
it directly goes to the SERVICE2 on target end point which in turn puts this message in Queue2 on the target?

2. target queue then activates the stored procedure which is connected to queue2 (let's say the procedure name is 'Processqueue2')

3.I noticed that even when none of the item comes in the queue, still when u use "ALTER QUEUE queue2 WITH STATUS = ON", that time also the 'processqueue2' is called.

4.'processqueue2' then fetch message from queue2 and process this. optionally it can send the acknowledgement message to the initiator or just send the end dialog message.

5.if i dont want 'processqueue2' to send the acknowledgement then
can I can directly send the End dialog from the inititor it self i.e. the end dialog just after sending the message.

BEGIN DIALOG CONVERSATION @.dialog_handle
FROM SERVICE [SERVICE1]
TO SERVICE 'SERVICE2'
ON CONTRACT [MainContract]

SEND ON CONVERSATION @.dialog_handle
MESSAGE TYPE SendMessageType ('hello from intiator')

END CONVERSATION @.dialog

In this case why do i need queue1 at all?

my assumption here is that my communication is one way and i don't need the ACK from the target.

Thanks,

BEGIN DIALOG alone does not actualy send any message. It just create the initiator endpoint in sys.conversation_endpoints.

When you SEND a message, the message goes at first into sys.transmission_queue. After the SEND is commited, the message is picked up from sys.transmission_queue and delivered to he machines where SERVICE2 is hosted and is enqueued into Queue2. After the enqueue into Queue2 is commited, an ACK is automatically sent back to the host of SERVICE1 and this allows the message to be deleted from sys.transmission_queue.
In the case when SERVICE1 and SERVICE2 are within the same SQL Server instance, we might try to optimize the SEND by directly enqueueing the message into Queue2 (skip the intermediate step of sys.transmission_queue). If this optimization attempt fails for whatever reason (e.g. Queue2 is diasbled), then the normal path of sys.transmission_queue is used even within the same SQL instance (in fcat, even within the same database).

You application cannot send ACK replies, it can only send real message replies. These are ordinary messages sent from target to initiator, and they would follow the exact sequence as above.

Procedure activation (Processqueue2) happens whenever there are available (i.e. unlocked) messages in the queue. It is not a trigger, the procedure does not get activated once for each message. The algorithm that determines when to activate a new instance of the procedure (up to the max of MAX_QUEUE_READERS setting) monitors the activity of the procedure (RECEIVE statements) vs. the incomming rate of messages and determines when the procedure cannot keep up and launches a new instance of it. When you enable a queue, if there are messages in the queue, it will activate the procedure. Same goes for server start-up, database going online etc (if there are messages in the queue, it will activate the procedure).
It is not guaranteed that the activated procedure will actually find messages in the queue. The code of the procedure should always be prepared with being activated but finding the queue empty (altough we do try hard not to activate in such situations).

A one way message flow that does BEGIN DIALOG/SEND/END is at risk of running into problems if the target service suddenly starts erroring dialogs (e.g. permissions change, or service contract changes etc). Because the initiator has already ended the conversation, when the error comes back from the target it will be droped. The initiator has no way of evem knowing the error occured. A much better message exchange patttern is to BEGIN DIALOG/SEND from the initiator, RECEIVE/END from the target, then RECEIVE/END from the initiator (i.e. the target ENDs first). The initiator does not have to sit there waiting for the target to reply, the EndDialog message sent tby the target can be processed by a procedure attached to queue1. This issue is also discussed in this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=273523&SiteID=1.

HTH,
~ Remus

sql

Flat file format

I have a table that I want to export to a flat file. The problem I am running into is, the person I am sending it to needs it in a specific format. It is a comma serperated file and I need quotes on some of the data but not all.

For example : "1234",abc,"id"

I know how to make it all or none but not conditionally. Also I have some fields that the total in them is 0.00 and when this gets exported to the file the format become .00, is there a way to make it 0 with out changing the ones that have totals. Thanks.

95five0 wrote:

I have a table that I want to export to a flat file. The problem I am running into is, the person I am sending it to needs it in a specific format. It is a comma serperated file and I need quotes on some of the data but not all.

For example : "1234",abc,"id"

I know how to make it all or none but not conditionally. Also I have some fields that the total in them is 0.00 and when this gets exported to the file the format become .00, is there a way to make it 0 with out changing the ones that have totals. Thanks.

Well, you may just have to assemble the quotes yourself using a derived column transformation just upstream from the flat file destination. Basically, for those fields, concatenate the double quotes to your data.

For your other question, I think you'll need to ensure that the output datatype for 0.00 is of string, so that it doesn't remove the leading zero.|||The only way to do what you describe, would be to create varchars for each field and convert them to the exact format you want, then export the varchars.

The default for comma delimited with string quotes, is "strings" get quoted, but other fields do not. So in your case the ,abc, would result in ,"abc",.

You might see if they really need it in that exact format. Most import programs will work with slightly different input, like the quotes or .00 as a number.|||Thanks for the advise. Basically what I did was create a temp table and essemble each line into one varchar field. Then I just pushed the data from the temp table to the file.|||

95five0 wrote:

Thanks for the advise. Basically what I did was create a temp table and essemble each line into one varchar field. Then I just pushed the data from the temp table to the file.

Yet another way to make it work. Good deal.

flat file destination -data all on one line

I have a flat file destination that Im sending data to, from an OLE DB data source. There are two records, but for some reason they are both going on the same line in the output. This is after setting the output to fixed width, from comma delimited.

Help ?

If you are viewing the flat file from a text editor, recheck the option on your editor to verify word wrap is not occurring. The file may be perfectly fine, it's the representation in the editor.|||

If you are using the pure FixedWidth format the row delimiter is not going to be inserted. It assumes all your columns are fixed width and it does not put any delimiters.

If you want to break your rows in new lines, choose the Fixed Width with Row Delimiters option at the time you create the flat file connection from the Flat File Destination. This will configure the connection to use the RaggedRight format and add a dummy column with the new line delimiter at the end. You should be able to do this manually as well, if you are changing an existing connection.

HTH.

Flat File Destination

Hi,

How can I check if my Flat File Destinatino is empty or not?

I′m sending it with FTP, but I don′t wanna send an empty file.

Any ideas?

Thank you.

I reckon you coudl use a WMI query to get this, but to be honest I don't know how.

I would use the Script Task, just write a quick line of VB to grab the file size and stuff it in a variable. If being good you could read the filename straight off the file connection as well, e.g.

Code Snippet

Dim fileInfo As System.IO.FileInfo = New System.IO.FileInfo(Dts.Connections("Flat File Conn").ConnectionString)

Dts.Variables("FileSize").Value = fileInfo.Length

Note I created a variable called FileSize of Int64, and added it to ReadWriteVariables list of my Script Task.

Next, I would use an expression on the constraint leading from the script task to the ftp task. @.[User::FileSize] > 0, such that the expression must be satisfied to run the FTP task.

Fixed width output problem

I'm sending the results of an SSIS data flow to an fixed-width flat file output, but instead of getting separate rows of data, like so:

row1data...
row2data...
row3data...
etc...

I get:

row1data...row2data...row3data...etc...

Is there some setting I'm missing in either the flat file output or the file connection to turn this on?

The fixed width format does not include the row delimiters.

You can use the Ragged right format if your last column always has a fixed format or you do not care for it to be fixed.

If you do have a requirement for the fixed length of the last column (like you always want integer values to take 10 characters in the file), you can create your Flat File Connection manager by clicking "New..." on the Flat File Destination UI and choose the "Fixed width with row delimiters" option. That will actually create the ragged right file with a dummy row delimiter column.

HTH.

|||Thank you. That's what I needed.|||Thanks so much for this. I wanted SSIS to mimic the fixed width behavior of DTS or SQL Server 2000 export wizard. This was exactly what I was looking for.