Could not establish trust relationship for the SSL/TLS ...

If you are consuming a web service in Biztalk and calling it through ports, etc you may come across a certificate for a wsdl that's out of date or wrong in some other way. in this case you get the following error;

"Could not establish trust relationship for the SSL/TLS ... "

In code its easy enough to bypass this by trusting all certs.

      //Trust all certificates      System.Net.ServicePointManager.ServerCertificateValidationCallback =

     ((sender, certificate, chain, sslPolicyErrors) => true);

However in Biztalk its much harder; I tried adding this code in an expression shape but had no luck. After searching around a bunch I found the following server wide solution. Add the following to your BtsNtSvc.exe.config (see bold):

<?xml version="1.0" ?>
<configuration>
<system.net>
<settings>
<servicePointManager checkCertificateName="false" checkCertificateRevocationList="false" />
</settings>
</system.net>
...

Obviously this is really risky security wise; only use it for development.

UTF and encodings

So there are a number of ways(ha!) to go from bits/bytes of data to human readable glyphs(like 'a' or 'A' or the chinese characters, etc). Each is called an 'encoding'. One way to go from bytes to (english) characters is ASCII encoding. This uses 8 bits, or actually it uses 7 bits and the extra bit is something that doesnt matter too terribly much to me. This means we can encode 2^7=128 different glyphs(or characters, I'm not sure of the technical distinction. Actually we can also include 'beep', space, escape, etc. For my purposes here i will call all the things we represent 'characters')using ASCII encoding. Naturally the ones ASCII includes for encoding are all the english letters(41 to 7A, in hex) some non readable characters(0 through 20 in hex) and numbers (30 through 39 in hex). You can look up the rest. 

UTF encoding is a little different, it starts by taking all the writing systems(in the world) and assigning them to code points those code points are then assigned to a hex number which is stored in memory(in binary).

Every platonic letter(but also space, beep, escape, etc) in every alphabet is assigned a magic number by the Unicode consortium which is written like this: U+0639.  This magic number is called a code point. The U+ means "Unicode" and the numbers are hexadecimal. U+0639 is the Arabic letter Ain. The English letter A would be U+0041.

So with UTF we go from characters to code points and from code points to bits/bytes in hex (or decimal, but hex is more common). Now how do code points map to actual numbers represented by bytes, equivalently how do we go from a couple of bytes(how many bites do we get? ASCII took 7.) to a code point. Well UTF-8, UTF-16, etc are all different ways of going from a code point to an actual character(or non readable character).

UTF-8 makes some sense to me. as does UTF-16, though it takes up more space. UTF-8 uses between 1 and 6 bytes to store a code point, whereas UTF-16 uses either 2 bytes or 4 bytes to store a code point.  

In UTF-8 U+0041 maps to just 41(in hex) in memory which is the character 'A'. Characters above ASCII, meaning above '127' in decimal or above 007F in hex, are stored in 16 bits(2 bytes). So UTF-8 doesnt use 2 bytes until it 'needs' to(the extra bit on the end of that first byte in UTF-8 is just set to 0 I think). 

UTF-16 uses at least 2 bytes so U+0041 goes to 0041(in hex) in memory which is the character 'A'. For english characters this is a bit too much wasted memory('Hello' would look like 00 48 00 65 00 6C 00 6C 00 6F vs in UTF-8 it would be just 48 65 6C 6C 6F). So UTF-16 is a little memory heavy because every other byte is often 00, which in UTF-8, 00, would go to just null, since thats what 00 is in ASCII. UTF-8 and ASCII are the same up to 7F(in hex) or 127(in decimal).

There are other UTF encoding but 8 and 16 seem like the big ones to me. Of course the big thing to remember is that you have to have an encoding before you can read anything on a screen. This is why its so important.

Biztalk App As HTTP Post Receiver

Suppose you want to set up a Biztalk app that handles incoming HTTP recieve Posts. There are a few parts to setting up an HTTP Receeive location and Biztalk App. I outline them in order below:

1)Set up handler:
*Start up IIS and go to the server tab on the left(main root of the tree). 
*In the Features View, in the IIS section in the middle, click on Handler Mappings. 
*In the upper left click on Add Script Map; fill in as follows
*RequestPath:
BTSHTTPReceive.dll
*Executable:
C:\Program Files\Microsoft BizTalk Server 2010\HttpReceive\BTSHTTPReceive.dll
*Name:
BTSHTTPReceive

*Click on Request Restrictions button:
*In the Verbs tab, click All verbs. 
*In the Access tab click Script radio button.
*Click OK, and then click yes to add the ISAPI extension.

2)Set up IIS application:
*Add a new application, with Alias 'HttpReceive' and Physical Path 'C:\Program Files\Microsoft BizTalk Server 2010\HttpReceive' don't bother testing the connection. 
*Use an application pool with access to the Biztalk Message Box DB. I dont think the default app pool has sufficent perms for this. 
*In the IIS section of this application click on Authentication and disable everything but Anonymous.

3)Set up biz adapter:
*Create a Recieve Port and then a Location along with it, called whatever you like. 
*For Transport type click HTTP. I reccomend using pass through receive as the Receive Pipeline since these posts come through not as XML but as plain text. See http://enricoscipio.blogspot.com/2009/02/biztalk-http-adapters-and-get.html. You should not have this recieve location hand off to an orchestration since orchestrations will need xml.
*Click on Configure and fill out as you see:

I reccomend having a simple send adapter just pick anything up from this recieve location and dump it in a folder. 

4)Test with post.
*Open Fiddler, set it to POST;
*****
POST http://localhost/HttpReceive/BTSHTTPReceive.dll HTTP/1.1
Host: localhost
Connection: keep-alive
User-Agent: Fiddler
Accept: text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
Accept-Encoding: gzip,deflate,sdch
Accept-Language: en-US,en;q=0.8
Accept-Charset: ISO-8859-1,utf-8;q=0.7,*;q=0.3
Content-Length: 10

Name=Jason
*****
that last bit is in the body. 
*You can also try GET;

GET http://localhost/HttpReceive/BTSHTTPReceive.dll?Name=Jason HTTP/1.1
Host: localhost
Connection: keep-alive
User-Agent: Fiddler
Accept: text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
Accept-Encoding: gzip,deflate,sdch
Accept-Language: en-US,en;q=0.8
Accept-Charset: ISO-8859-1,utf-8;q=0.7,*;q=0.3
Content-Length: 0

A good reference for most everything said here is:
http://allcomputers.us/windows_server/biztalk-2010-recipes---adapters---configuring-http-receives.aspx

SSIS or scripts that take a runtime parameters at the command line

If you want to run an SSIS package from the command line you use dtexec. If you have a sql script that you want to run from the command line you use sqlcmd. Both of these utilities will allow you to pass through a variable at runtime to the script/package you created. Of course there are other ways to get them to run but the command line is the lowest common denominator for really anything that runs on a Windows computer(and not just Windows, but I digress). 

SQL Script called prune.sql:
<SQL CODE HERE>
delete from Table where Column=$(Param)
<SQL CODE HERE>

and the scripting variable Param you would set like so;

C:\>sqlcmd -S "My Server" -v Param=15 -i prune.sql


SSIS package called Package.dtsx:
As an example say you have only one Control Flow object, no Data Flow objects, and the Control flow is a SQL task with variables as you see:

pruneSP is a stored proceedure that takes a parameter called @testval and when you click on Parameter Mapping you see the map from User::days_back to @testval:

Another gotcha, at least it seemed to me, was that your result set menu requires special configuration. Result Name should be set to 0 and then you will need some variable that you have created to to store the result set in. I named mine outputRS and it should be of type Object. For more on variables in SSIS see 
http://www.simple-talk.com/sql/ssis/working-with-variables-in-sql-server-integration-services/
and for a walkthrough of how to use them that is very simple try this:
http://sqlfool.com/2009/08/getting-started-with-variables-in-ssis/

Anyway once you are finished with the dtsx file, you can run it like this(from the dir that the dtsx file is in):

C:\>dtexec /FILE Package.dtsx /SET \Package.Variables[User::days_back].Properties[Value];15

From orchestration to calling it as a WCF Service

In this post I will cover the process of creating an orchestration and consuming it as a WCF service. It is a popular process but it's one that has a number of steps I think should be shown together. 

1)Your orchestration, ideally, should have just a single request/response port for the WCF wizard to consume. Other ports don’t need to be public but the one that is going to be consumed needs to be;

Or you can create a single receive port(here the other ports shown are just to files for testing);

You can just have a single port with only a request on it but then your service gets called and the calling program blindly continues after that. In other words it starts an instance of biz when the service is called but it doesn’t wait till that instance successfully completes. 

At this point, before you go into the WCF wizard be sure to build and deploy your app(give it a name and sign it as well, obviously). But don’t try to finish the binding for the public port that you want the WCF wizard to create.

2)Next, to set up the WCF service go to Tools in Visual Studio or you can go to Biztalk and look for ‘Biztalk WCF Service Publishing Wizard’. Once you are in the wizard click through to get here:

Metadata is things like schema info for the wsdl so be sure to click that. You want it to create the biztalk receive location too so you can bind your applications receive location to it, so be sure to check that as well. The Biztalk App name is the name for the app you deployed containing your orchestration.

The next few screens are for publishing an orchestration or schema(you want orchestration), selecting the dll for the orchestration, selecting the port you want binding info created for, giving the schemas a unique namespace, and finally, picking a location for your service (http://localhost/<nameofapp> is a good one if you’re hosting locally).

Anonymous access is not something you want for production but for testing it may be easiest. I’d leave it unchecked at first and if you can’t get things working because of perms, go back and check it.

Click through to create the service, and finish.

3)Next, you can import the BindingInfo.xml file (in the inetpub/wwwroot/<WebSiteName> folder) in your biztalk app. Then you can start it. Once it’s started you should be able to browse there from IIS(assuming your perms are okay; you will need perms to get to Biztalk and the SQL DB as well I believe. So make sure to put this site under the correct AppPool, for example DefaultAppPool didn’t work for me). By ‘there’ I mean something like

Http://localhost/<nameofbizapphere>/<nameofbizapphere>_<nameoforchestrationhere>_<nameofrecieveporthere>.svc

This site will give you a good tip on how to fire off your service once you’ve referenced it. I don’t bother generating code the way that site recommends but it can be done. Below is an example of a consumed biz app called PublishAsWCF_OnePort;

4) When you want to add this WCF service to your c# project go to ‘add service reference’ and then just click on the address and type in the address of your wsdl, which is

http://localhost/<nameofbizapphere>/<nameofbizapphere>_<nameoforchestrationhere>_<nameofrecieveporthere>.svc?wsdl

For example:

http://localhost/PublishAsWCF_OnePort/PublishAsWCF_OnePort_OnePortOrch_Recv.svc?wsdl

Click Go. From here you basically give the service a namespace, whatever you like, add it to the ‘usings’ in your project and go ahead and build your project. Below is an example of two apps I consumed, and loaded into a project. The input message is called Birth and the response (which exists only for ServiceReference1) is called Lifetime. They both work when the Biztalk app is running. Notice the default operation to start this is ‘Operation_1’.

An excellent walk through of step two, actually most of the stuff I covered here, for Biz 2009 is at

http://mscerts.programming4.us/application_server/biztalk%20server%202009%20%20%20exposing%20wcf%20services%20from%20orchestrations.aspx

If you have any questions beyond what is there or here try this link:

http://msdn.microsoft.com/en-us/library/bb226564.aspx

Polling using WCF-SQL Part 2

Debatching multi-record messages can happen at 3 levels from here, as I see it. By 'multi-record message' I mean a message in the Biztalk sense that contains data which in SQL would be a table with multiple rows in it. For example a Message might look like this inside:

<ns0:Top xmlns:ns0="http://BlogStoredProc.SmallMsg">

<Record><FirstName>Jason</FirstName><LastName>Slemons</LastName></Record>

<Record><FirstName>Zeta</FirstName><LastName>Function</LastName></Record>

<Record><FirstName>Galois</FirstName><LastName>Group</LastName></Record>

</ns0:Top>

  The 'PollingStatement' level is the first, where you can 'select TOP(1) ...', grabbing only one record(in the binding tab of the receive location in Biz). The second level is where you use debatching at the schema level by selecting an envelope and then the receive port will split messages up inside for it. This is explained very well at 

http://seroter.wordpress.com/2010/04/08/debatching-inbound-messages-from-biztalk-wcf-sql-adapter/.

Lastly, it can happen inside an orchestration where you split up your record elements by assigning each one to a different variable with xpath. One thing I will say about this last method is that all the gorey detail of looping through each record is then exposed. In general I think debatching is a good idea since often your records are going to want to succeed or fail on their own and they don't depend on other records in the Poll, typically.  

Still, suppose you don't want to debatch at a higher level, but instead want the whole multi-record message coming into the generated orchestration. Then suppose we only want some of the elements in in those records, for example first and last name.  

1)Go into your generated Orchestration, which I have renamed 'GenOrch', create a Message named 'GenMsg', and another Message named 'SmallGenMsg'. 'GenMsg' should be based on the TypedPolling Root Node of the generated Schema. Next add a receive, send and transform shape to GenOrch as shown below:

The 'TypedPolling' node is the root node for the whole message coming from the 'PollingStatement' output. Notice that 'TypedPollingResultSet0', its child node, has a min/max occurs of 0/1 and its child node, 'TypedPollingResultSet0', has a min/max occurs of 0/unbounded. This is because it must accommodate mutiple records. With GenMsg set correctly we can now do whatever we like with it inside the orchestration. 

2)Add a schema, called SmallMsg, to hold just the first and last names of the records returned from our 'PollingStatement'. Be sure it has a middle child node with a min/max occurs of 0/unbounded (see above). Associate the SmallMsg schema with the Message SmallGenMsg.

3)Add two ports, a send port and a receive port. The first should be 'we receive messages on this port' and the second one, below that, should be 'we send messages on this port' specify both later. If you like you can use the predefined port for the receive port. The top receive port and the receive shape next to it should use GenMsg and the schema associated to it. The send port and the send shape next to it should use the SmallGenMsg and its schema. 

Connect up the two ports and Transform from 'GenMsg', TypedPolling, to 'SmallGenMsg', SmallMsg, in the obvious way;

At this point we simply have a shape that yields the message to a receive port, that message is then transformed into another message that is slightly simpler, and the simpler message is then spit out to a send port. 

4)Now you can deploy your project(be sure to mark the top receive shape's activate property to true, sign it, and give the project an application name). When you configure the ports, make the send port just out to a file, pass through receive. The receive location should be set up the same way as the binding was in Part 1 of this post. I will assume the reader is largely familiar with how to deploy a project, if not entirely sure how to configure the receive port location in Biz with the WCF-SQL Adapter.  

5)Create a new receive port, with a name of 'SQLPollReceive', and a location for it with the name 'SQLPollReceiveLoc'. The Receive pipeline should be set to XMLReceive and the Type should be WCF-SQL. Click Configure:

Here the Address(URI) should be the same as before. You can use the configure button to remind you how to generate it. Be sure to use the same InboundId as before(I use ID2). Under the Binding tab be sure to change InboundOperation Type to TypedPolling, and set the PolledDataAvailable to something like 'select COUNT(*) from Test' and PollingStatement to something like 'select * from Test'. Most of the fields here are explained in documentation like that found here:

http://social.technet.microsoft.com/wiki/contents/articles/3470.typed-polling-with-wcf-sql-adapter-best-practices-and-troubleshooting-tips.aspx

6)Start your application. In the folder that you configured the send port to dump to you should see messages piled up something like this:

Notice there are a number of files in my send folder, that's because the Polling interval i picked was every thirty seconds and my polling statement didn't bother deleting or modifying any records so it just continues to poll the same 3 records every 30 seconds and send them out!

Polling using WCF-SQL Part 1

1)Add a Table to a database on your local SQL Server installation. I call the table 'Test' and the database, 'CPNI'.

2)Create a new project in VS 2010, an empty biztalk project is best. I called mine 'BlogStoredProc'. 

3)Then go to 'Add Generated Items' and then 'Add Adapter Metadata'. I am using Biztalk 2010 and I choose to use the WCF-SQL adapter. If you are using an earlier Biztalk and see another option that sounds similar (something like 'Consume Adapter Service' instead of 'Add Adapter Metadata') then you may choose that and end up in the same place.  In the 'Consume Adapter Service' window I select sqlBinding. From there I see a button to 'Configure URI'. Click it.

4)Configure URI as such:

If your instance of SQL Server has the default name 'MSSQLSERVER' then you can leave 'InstanceName' blank as I have. This is common. The other fields have a good explanation of what their value should be so I wont detail them.

In the Binding Properties tab, you will need to change 3 things. The first is InboundOperation Type, which will need to be Typed Polling.'Typed Polling' means we have regular sql procedures that are expected to 'poll' data from a database and we need our procedure to have schemas that are known ahead of time(also known as a 'Strongly Typed' procedure). Regular polling is for when the schema comes along with the polling message. For more information on the binding properties see this article:
http://social.technet.microsoft.com/wiki/contents/articles/3470.typed-polling-with-wcf-sql-adapter-best-practices-and-troubleshooting-tips.aspx

The other two things you will need to fill in here are the 'Polled DataAvailable Statement' and 'Polling Statement' which sound very similar but from the above article you can tell they are not. One needs to return a nonegative integer only (the SQL code in the 'Polled Data Available Statement' field) and the other returns the data(the SQL code in the 'Polling Statement' field). When the SQL in 'Polled Data Available Statement' executes and returns a positive integer, then the 'Polling Statement' executes. If 'Polled Data Available Statement' returns 0 then the 'Polling Statement' doesn't execute. I have done these three things in the example window to the left. 

When you are done with these two tabs click OK. 


5) In the Consume Adapter Service click Connect and then select contract type for Inbound Operations(since polling is inbound). Under category I select Typed Polling, and then you should notice that Typed Polling shows up in the 'Added categories and operations' in the box below. Click OK

At this point an orchestration and a schema have been created for me. The Table 'Test' that I created has 4 fields. You can see them below since the select statement in the binding tab that I gave it was used to generate these fields;

Datetime in SQL/Biztalk and C#

I had a proc select a datetime field from a SQL database table and push it to a schema in biz. from there I grabbed it using xpath out of the schema as such:

tempstr = "string(/*[local-name()='Root']/*[local-name()='Record' ]/*[local-name()='TIME_STAMP']["+System.Convert.ToString(counter)+"]/text())";
then
time_stamp = xpath(Param_In,tempstr);


The above code is in an expression shape in an orchestration. Anyway, time_stamp , if you declare it as a C# DateTime gives you a time thats wrong. Actually its not far off, a few hours is all. I suspect 
time_stamp = xpath(Param_In,tempstr);
converts from UTC since the sql date time, on the right side of the equals, is of the format 2012-08-02T15:45:42.62Z. 
So I kept the time_stamp as a System.String and converted it using DateTime.TryParse. The 'T' indicates a placeholder that will not change, and the Z indicates UTC time. So the DateTimeStyle is RoundtripKind according to the surprisingly helpful page at msdn;


http://msdn.microsoft.com/en-us/library/az4se3k1.aspx#Roundtrip


The following code converts from the sql string to the C# System.DateTime:



string sometime = "2012-08-02T15:45:42.62Z";
DateTime newDate;
bool success = DateTime.TryParse(sometime, null, System.Globalization.DateTimeStyles.RoundtripKind, out newDate);

BizTalk: Errors exist for one or more children

When I was converting BizTalk 06 into BizTalk 2010 I ran into a number of strange errors that didnt make much sense. On the top of this list was the following:

#error: "Errors exist for one or more children." 


So helpful, right? When you look for the line in the orchestration that this error is coming from(and it always seems to be an orchestration) you are typically pointed to a snippet of code in a shape that seems totally innocuous. Here are the tricks I have used to get rid of the error:


1) First Delete the offending line of code in the shape and save. Then attempt to build the solution/project and let the build fail(this time because the shape is empty). Lastly, add back the line of code you deleted, save and build again. This sometimes works.


http://sandroaspbiztalkblog.wordpress.com/2012/01/30/biztalk-orchestration-error-errors-exist-for-one-or-more-children/


2) If you can find it, delete the generated odx.cs file. These tend not to exist as such in later versions of Biz but in 06 they do, and in 09 I believe its File01.cs. This is because the generated .cs files can be a source of problems if they aren't generated correctly. 


http://stackoverflow.com/questions/10986267/biztalk-2010-odx-cs-file


3) Look in the odx file as text and search for the error there("Errors exist for one or more children.") sometimes it will be near the bottom. Delete it and try to rerun. 


http://wilhelmsbiztalk.blogspot.com/2008/05/biztalk-orchestration-and-error-errors.html


4) Add some whitespace or a newline after the offending code, and try to recompile after that. Restarting visual studio is another option.


If you see other errors from Biz that you cant make sense of perhaps it is covered here:

http://social.technet.microsoft.com/wiki/contents/articles/7204.biztalk-list-of-errors-and-warnings-causes-and-solutions-en-us.aspx

msbuild, devenv, Powershell, and the man in the yellow hat.

Powershell has its pluses and minuses for sure. I couldn't seem to make much sense of how to use msbuild, or devenv for that matter, but I did piece together some things I found online and thought I'd share what I learned.


'msbuild.exe Solution.sln /t:build' 


can be done in powershell and you can replace msbuild.exe and Solution.sln with variables like so:



> $msbuild = "C:\Windows\Microsoft.NET\Framework\v4.0.30319\MSBuild.exe"
> $sln = "Solution.sln"
> $build = $msbuild + " ""$sln"" "
> Invoke-Expression $build


I'm unsure how evaluation is done and so I dont know why double quotes are needed around $sln, though I do suspect one set is for an escape of some kind. 

Change password in windows 7 Biztalk, SSO, etc.

If you change your windows password and use this account for SQL/Biz/Enterprise SSO then you'll need to change it in a few places. I learned this when I tried to delete an application in Biztalk and got this error; 


"could not contact the sso server biztalk" and "Check that SSO is configured and that SSO Service is running on that server".


Changing your password in Enterprise SSO involved opening Enterprise SSO Admin Config under All Programs and adding my local box as the Server, and then changing the password. I don't remember where exactly you change your password but i just clicked around some. Real scientific like. You should Enable / Disable the Enterprise SSODB in this snap in as well.


To change your password in Biztalk just double click on the in process host in Biztalk and click configure. 


I recommend restarting things at this point, and if you still don't solve whatever SSO/Biz problem you're having then restart your box.


Another thing that is helpful to try if your password hasn't been changed recently is to get a dos prompt with admin privs and CD to "C:\Program Files\Common Files\Enterprise Single Sign-On"  then 
Type: ssomanage -server [SSO Server Name Here]

Biztalk applications sharing a resource.

When using Biztalk 2010 and adding two or more applications at a time that use the same .dll, some care should be taken. The thing I have learned more than once is that when a .dll is added as a resource to one application, that application adds it to the GAC(you are given the option of specifying when this happens under 'add resource', but it does happen at least by the time the application is started). When the .dll is in the GAC there will be no need(nor will there be the option) to add this .dll resource to the other applications. So it seems to me you must choose which application adds it and the rest will just have to make do, assuming its there. I believe adding a reference from the application that doesn't have the .dll in its resources to the application that owns this .dll is probably the way you should actually do this. It can feel like overkill but actually this makes a good bit of sense to me the more I think about it. 

wait for msiexec to finish

If you are trying to use msiexec the windows installer, my experience has taught me that it immediately returns command to the user as soon as it starts executing. This is a problem in a script that depends on the install being done. If you want control to be returned only if the install is complete then try Start-Process. I didnt get all the way through the documentation for it but i did get as far as solving my problem;


msiexec /i "SSOMMCSnapInSetup.msi" /qr /log somelog.txt
returns right away but


Start-Process -FilePath msiexec -ArgumentList /i, "SSOMMCSnapInSetup.msi", /qr, /log, somelog.txt -Wait
only returns after the install is finished.