Thursday, August 23, 2012

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:
delete from Table where Column=$(Param)

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
and for a walkthrough of how to use them that is very simple try this:

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

Tuesday, August 21, 2012

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


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


For example:

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

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

Tuesday, August 14, 2012

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">

  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 

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:

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:

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;

Friday, August 3, 2012

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())";
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;

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);