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

flow layout in rs2005

I have a report that needs to show postal addresses. The address is broken down into several fields. The problem I have is some of the address parts are optional. If they are empty, I'm left with nasty gaps in the address. I'd really like next label to reclaim the space of any empty labels.

a quick example

A full address would look like this..

customer name
address line 1
address line 2
town
county
post code

if address line 2 isnt given, I get:

customer name
address line 1

town
county
post code

but I want:

customer name
address line 1
town
county
post code

Can anyone help?

Many thanks,

Paul

Anyone know? Even if the answer is "sorry, can't do that". Could really do with an answer, I think I've downloaded the entire internet over the last day trying to figure it out!

Cheers|||

Hey Paul,

Can you try this:

=Fields!Customer_Name.Value
& IIf(Len(Fields!Address_Line_1.Value) > 0, chr(10) & Fields!Address_Line_1.Value, "")
& IIf(Len(Fields!Address_Line_2.Value) > 0, chr(10) & Fields!Address_Line_2.Value, "")
& IIf(Len(Fields!Town.Value) > 0, chr(10) & Fields!Town.Value, "")
& IIf(Len(Fields!County.Value) > 0, chr(10) & Fields!County.Value, "")
& IIf(Len(Fields!Postal_Code.Value) > 0, chr(10) & Fields!Postal_Code.Value, "")

This assumes that the Customer_Name field is required, but none of the others are. If the other fields have any length, it will add the line feed, otherwise it will not. It will put the whole thing in one textbox, I'm not sure if that was a requirement.

Hope this helps.

Jarret

|||

Ahh, ok. That works!!! Completely a different answer to how I expected, I thought it would be some layout thing somewhere.

Anyway - exactly the result I was after. Thanks for taking the time to help.

Paul

flow functions ?

Hi

Does anyone know where can I find a list of flow functions valid in MDX? The list in

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/mdxref9/html/e363722a-3e5b-40a9-a0b5-399dd2d93f6d.htm ist not complete. For example: the case when else end function is not there. I really need a switch.

Thanks!

Have you looked at this article. http://msdn2.microsoft.com/en-us/library/ms144841.aspx

It's a bit short, but should give you some general idea.

Hope that helps.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Flow Control in stored procedures

A newbie question..

If within a stored procedure there are a list of Stored Procedure calls.

Create Procedure spTest AS

EXEC spMyActionQuery1
EXEC spMyActionQuery2
EXEC spMyActionQuery3
EXEC spMyActionQuery4

GO

Assuming each sub stored procedure executes an action query, does SQL Server ALWAYS wait for the current procedure to finish execution before proceding with the next stored procedure? I believe the answer is yes.The answer IS yes :D

Flow Control in SSIS

I am having a hard time with what appears to be something simple. I want to import an excel spreadsheet into a table on a daily basis from a command line. I created a package from the Import Wizzard in the SQL Management Studio and saved it. Since I want a clean table each day, my process needs to be create a temp table, import from the Excel file into the temp table. If that is successful, delete the original table and rename the temp table the original name. The point of this process is to provide for a fail-safe if there is some unforseen problem downloading the data on a particular day.

When I run the package, the first thing it does is delete the original table. I know this because the process shows the time that it finished is before anything else has started or finished. The time shown for the completion of the data flow task is about 2 minutes after that time.

This is maddening!!! The one thing I do not want to happen I can not seem to prevent. I have my control flow set on success. Why does it do this?

Are you using precedence constraints? You should have a flow like:

Create temp table(Execute SQL task) -- load temp table (Dataflow) --> delete orig. table & rename temp (Execute SQL Task)

The precedence constraint should be set upon success of the previous task

|||

Thanks for the reply. I was using contraints and what you descibe is how I put it in my original message. However, after a good night's sleep I see that in my package I the drop table task comes after the table is renamed, rather than before. It appears SSIS was trying its best to complete all those items the best it could.

After putting all in correct order, the package ran as it should.

Flow Control in SSIS

I am having a hard time with what appears to be something simple. I want to import an excel spreadsheet into a table on a daily basis from a command line. I created a package from the Import Wizzard in the SQL Management Studio and saved it. Since I want a clean table each day, my process needs to be create a temp table, import from the Excel file into the temp table. If that is successful, delete the original table and rename the temp table the original name. The point of this process is to provide for a fail-safe if there is some unforseen problem downloading the data on a particular day.

When I run the package, the first thing it does is delete the original table. I know this because the process shows the time that it finished is before anything else has started or finished. The time shown for the completion of the data flow task is about 2 minutes after that time.

This is maddening!!! The one thing I do not want to happen I can not seem to prevent. I have my control flow set on success. Why does it do this?

Are you using precedence constraints? You should have a flow like:

Create temp table(Execute SQL task) -- load temp table (Dataflow) --> delete orig. table & rename temp (Execute SQL Task)

The precedence constraint should be set upon success of the previous task

|||

Thanks for the reply. I was using contraints and what you descibe is how I put it in my original message. However, after a good night's sleep I see that in my package I the drop table task comes after the table is renamed, rather than before. It appears SSIS was trying its best to complete all those items the best it could.

After putting all in correct order, the package ran as it should.

sql

Flat File to SQL table

I am looking to evaluate a text field in the row and change it to an integer in the sql table based on the text.

What is the best data flow tranformation for me to use inbetween the flat file source and the ole db destination?

it depends on what logic you are using for your evaluation but Derived Column will probably do it. If not, the script component.

-Jamie

|||Can you help with an example If then expression?|||

With the information you have provided, no. What evaluation do you want to do?

-Jamie

|||

Something like:

If [Treatment] = "No Deposit Required" then 1 else 0

I'm not sure how to write this in an expression.

|||

OK

[Treatment] == "No Deposit Required" ? (DT_I4)1 : (DT_I4)0

-Jamie

|||Thanks. your great..

Flat File to SQL Destination - Unused Columns Warning

I have a flat file data source and SQL Server destination data flow. Only a subset of columns from the source are mapped to the destination. During execution SSIS returns DTS pipline warnings for every unmapped source column. Is some kind of transformation the only way to get rid of these warnings?

Also this data flow subsequently returns an error: [SQL Server Destination [1293]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security."

I'm researching this error, but if anyone is familiar with it your advice would be appreciated. Thanks.

You can either ignore the unused column warnings, or you can deselect them at the source connector so that they are not used. The preferred way is to not bring over additional columns from the source unless you need them for something.|||For the error message make sure that the file exists and that the user running the package has access to it.|||Amazing what you can miss the first time you work with a component. Deselecting the unwanted columns did the trick. Thanks Phil.|||

Rafael, it turns out I need to use an OLE DB destination instead of SQL Server destination because the database is on a remote server. See: http://msdn2.microsoft.com/en-us/library/ms141095.aspx

Specifically: "You cannot use the SQL Server destination in packages that access a SQL Server database on a remote server. Instead, the packages should use the OLE DB destination."

|||which it makes sense. I totally missread your post. Sorry about that.|||

M.Glenn wrote:

Rafael, it turns out I need to use an OLE DB destination instead of SQL Server destination because the database is on a remote server. See: http://msdn2.microsoft.com/en-us/library/ms141095.aspx

Specifically: "You cannot use the SQL Server destination in packages that access a SQL Server database on a remote server. Instead, the packages should use the OLE DB destination."

If you want to know more about why, read this:

Destination Adapter Comparison
(http://blogs.conchango.com/jamiethomson/archive/2006/08/14/SSIS_3A00_-Destination-Adapter-Comparison.aspx)

-Jamie

|||Thanks guys. The comparison article is also helpful.

Flat File Source Queston

I want to read the following file using the Flat File Source flow:

10000 Router
20000 Hub
10000 Switch
30000 Server
40000 Harddisk

Spaces are used between the numbers (e.g. 10000) and the following text (e.g. Router). Each line is temrinated by a {CR}{LF} pair. I would normally think of this as a "fixed width" file.

But in the Flat File connection manager, if I use format "Fixed width", the preview shows a row width of 1 character wide (the first row contains the number "1"; the next row contains "0", and so on for the remaining 3 rows.in the first line) Not what I had in mind. How do I setup SSIS to handle this file in intended maner?

TIA,

Barker

P.S. I never had any trouble with this type of file under Sql 2000 DTS. Also, have you noticed the crappy-looking "bitmap" displayed when one wants to click and define columns for a fixed-width file?

I might add that using "Fixed width" format I can successfully parse the columns: "Column 0" contains the digits; "Column 1" contains the spaces between the digits and the text; "Column 2" contains the text itself and "Column 3" contains the <CR><LF> line terminators. I'm sure I'm missing the point as this seems like a lot of work for something that should be very straightforward.

barker

|||

I'm beginning to think that "ragged right" is the preferred format. That format allows for fixed-width columns but assumes that last column has variable width (and is terminated). In other words, ragged right recognizes the line terminator as exactly that and doesn't think of it as another character(s) to be parsed.

Barker

|||

You've got it right. The RaggedRight seems as the most appropriate format for your file.

Thanks.

Flat file source not communicating with script component properly

Hi there,

I have a flat file source in my data flow that is suppose to feed through a couple of columns to a script component. All of the columns have data but when I debug in my script component (with the aid of messageboxes) I can see that one of then is empty.

This is wrong because when I preview that data in the flat file source, I can see that the columns are all populated.

Has anyone ever encountered that a flat file source doesn't communicate with a script component properly? I mean it does pass through the data of all other columns except for one.

I have replaced the components in case it had become corrupt and I even rebuild the package but the problem still persists.

Any advice would be welcome

Regards
Mike

Put data viewer between the Flat File Source and the Script component and check what data actually enters your script.

HTH,

Bob

|||Thanks for the reply,

I put a viewer on and saw that the script gets all the populated columns except for the one I mentioned earlier. Thus the problem seem to be with the flat file source not passing the data in that column through to the script.
But why would it do that even though it shows all of the columns populated when I click on the preview button on the flat file source?

Regards

**EDITED**

I have also noted that when I check the "retail null values from row as null values in data flow" checked box, then it does populate all columns correctly but I cant have that checked box checked since it does not go to the correct path on my workflow with the nulls. When I uncheck it, it misses the one column again but not the other ones. This is puzzling me very much
|||

What version of the SSIS do you have installed (RTM, SP1, SP2)? I think there was a flat file parsing bug fixed in one of SP releases that would potentialy cause this behavior.

Thanks,

Bob

|||Hi Bob,

I have recently (last week) upgraded to service pack 2 and also updated my ssis from Version 9.00.20(something) to Version 9.00.3042.00.

The package was built on an older version and pre service pack 2. I wonder if that is not the case here.

Thanks for the reply

Regards
Mike
|||

Would it be possible for you to isolate a few rows that fail, create another simple file with only those two rows, obfuscate your data and post your file here?

Just make sure the newly created file still fails the same way as the original one.

Thanks,

Bob

|||Hi Bob,

Just for some extra clarity, none of the rows are actually failing. They all pass through successfully except for one of the columns (SiteCode) in the file does not get read properly by the flat file source. It does not fail or warn me about anything, it just doesn't detect the data in this specific column. It passes an empty string through for all rows in that column.

Like I mentioned before, the funny thing is that when I preview the file in the flat file source in the data flow task, it does actually show that the column is populated. All of the SiteCodes are populated in the preview window but that preview data just doesn't get passed through to the other data flow component.

Would it still be usefull for me to post the file?

Regards
Mike
|||

Yes, by "failing" I meant not being parsed properly.

If you can provide the sample of data I can try to debug it here and eventually find out what is causing the behavior you are seeing.

Thanks,

Bob

|||Hi,

I hope this will be sufficient
The column with the 'BAL' data doesn't get read properly
Regards

FMC|FMCG|22110|FMCG Warh|BAL|Balance Sheet|BAL|Balance Sheet|22110|FMCG Warehouse Balance sheet|10030||Cost - Comp Equip 3yrs|2006|10|144.00|AA|General Ledger||
FMC|FMCG|22110|FMCG Warh|BAL|Balance Sheet|BAL|Balance Sheet|22110|FMCG Warehouse Balance sheet|10050||Cost - Comp Equip 10yrs|2006|10|233.00|AA|General Ledger||

|||

I tried to parse these two rows using Flat File Source and could not see any problem with it.

Could you tell me again what is not read properly? I have seen "BAL" values read in both; 5th and 7th columns.

|||Hi Bob,

I managed to find a workaround on this problem. When I click the "retain null values from source as null values in data flow" check box, the data does get passed on correctly but to the wrong exit point (error exit point).

I did further investigation as to why it would go to the error exit point and realized that it actually picks up on a null value and does not know how to handle it. I got the "object reference not set as an instance of a ...etc" error.

Adding code to check for these nulls and setting it to dbnull.value helped with my problem and things are working fine now. I also believe that my problem might also be because we did not use text qualifiers in this project but this is only speculation.

From now on I will know what to do with custom scripts reading and processing text files so that I don't leave the system open for these possibly unhandled situations.

Regards

Flat file source not communicating with script component properly

Hi there,

I have a flat file source in my data flow that is suppose to feed through a couple of columns to a script component. All of the columns have data but when I debug in my script component (with the aid of messageboxes) I can see that one of then is empty.

This is wrong because when I preview that data in the flat file source, I can see that the columns are all populated.

Has anyone ever encountered that a flat file source doesn't communicate with a script component properly? I mean it does pass through the data of all other columns except for one.

I have replaced the components in case it had become corrupt and I even rebuild the package but the problem still persists.

Any advice would be welcome

Regards
Mike

Put data viewer between the Flat File Source and the Script component and check what data actually enters your script.

HTH,

Bob

|||Thanks for the reply,

I put a viewer on and saw that the script gets all the populated columns except for the one I mentioned earlier. Thus the problem seem to be with the flat file source not passing the data in that column through to the script.
But why would it do that even though it shows all of the columns populated when I click on the preview button on the flat file source?

Regards

**EDITED**

I have also noted that when I check the "retail null values from row as null values in data flow" checked box, then it does populate all columns correctly but I cant have that checked box checked since it does not go to the correct path on my workflow with the nulls. When I uncheck it, it misses the one column again but not the other ones. This is puzzling me very much
|||

What version of the SSIS do you have installed (RTM, SP1, SP2)? I think there was a flat file parsing bug fixed in one of SP releases that would potentialy cause this behavior.

Thanks,

Bob

|||Hi Bob,

I have recently (last week) upgraded to service pack 2 and also updated my ssis from Version 9.00.20(something) to Version 9.00.3042.00.

The package was built on an older version and pre service pack 2. I wonder if that is not the case here.

Thanks for the reply

Regards
Mike
|||

Would it be possible for you to isolate a few rows that fail, create another simple file with only those two rows, obfuscate your data and post your file here?

Just make sure the newly created file still fails the same way as the original one.

Thanks,

Bob

|||Hi Bob,

Just for some extra clarity, none of the rows are actually failing. They all pass through successfully except for one of the columns (SiteCode) in the file does not get read properly by the flat file source. It does not fail or warn me about anything, it just doesn't detect the data in this specific column. It passes an empty string through for all rows in that column.

Like I mentioned before, the funny thing is that when I preview the file in the flat file source in the data flow task, it does actually show that the column is populated. All of the SiteCodes are populated in the preview window but that preview data just doesn't get passed through to the other data flow component.

Would it still be usefull for me to post the file?

Regards
Mike
|||

Yes, by "failing" I meant not being parsed properly.

If you can provide the sample of data I can try to debug it here and eventually find out what is causing the behavior you are seeing.

Thanks,

Bob

|||Hi,

I hope this will be sufficient
The column with the 'BAL' data doesn't get read properly
Regards

FMC|FMCG|22110|FMCG Warh|BAL|Balance Sheet|BAL|Balance Sheet|22110|FMCG Warehouse Balance sheet|10030||Cost - Comp Equip 3yrs|2006|10|144.00|AA|General Ledger||
FMC|FMCG|22110|FMCG Warh|BAL|Balance Sheet|BAL|Balance Sheet|22110|FMCG Warehouse Balance sheet|10050||Cost - Comp Equip 10yrs|2006|10|233.00|AA|General Ledger||

|||

I tried to parse these two rows using Flat File Source and could not see any problem with it.

Could you tell me again what is not read properly? I have seen "BAL" values read in both; 5th and 7th columns.

|||Hi Bob,

I managed to find a workaround on this problem. When I click the "retain null values from source as null values in data flow" check box, the data does get passed on correctly but to the wrong exit point (error exit point).

I did further investigation as to why it would go to the error exit point and realized that it actually picks up on a null value and does not know how to handle it. I got the "object reference not set as an instance of a ...etc" error.

Adding code to check for these nulls and setting it to dbnull.value helped with my problem and things are working fine now. I also believe that my problem might also be because we did not use text qualifiers in this project but this is only speculation.

From now on I will know what to do with custom scripts reading and processing text files so that I don't leave the system open for these possibly unhandled situations.

Regards

Flat File Data Flow

any suggestions on dealing with a flat file in the format below. I only want to process the data columns in the middle of the file and want to ignore all other rows. This was a very simple task in DTS with a small amount of VBScript in the transformation but it doesn't seem as straightforward in SSIS. thanks

......... file example ......

start-of-file

header1

header2

...

start-of-data

col0|col1|col2|col3|....

col0|col1|col2|col3|....

col0|col1|col2|col3|....

end-of-data

end-of-file

Two steps.

Read the file in first as one big text string (for each row) and pass them through a Conditional Split transformation to filter off each row that you don't want. Then hook it to a flat file destination.

Then use another flat file source against the file just created to do your column parsing. Work with it as needed from there.

Searching this forum will also yield other options (substrings, etc...) that you can try.|||thanks phil. thats helps

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.