Thursday, December 27, 2012
SELECT TOP (100) PERCENT InterchangeControlNo, ReceiverID, ReceiverQ, ApplicationReceiver, DATEADD(HOUR, - 5, TimeCreated) AS ProcessTime, TransactionSetId
FROM dbo.bam_TransactionSetActivity_AllInstances AS T
WHERE (SenderID LIKE ' [your org ID] %') AND (TransactionSetId <> '997') AND (NOT EXISTS
(SELECT RecordID, ActivityID, InterchangeActivityID, GroupControlNo, InterchangeControlNo, ReceiverID, SenderID, ReceiverQ, SenderQ, InterchangeDateTime,
Direction, AckProcessingStatus, AckStatusCode, DeliveredTSCount, AcceptedTSCount, AckIcn, AckIcnDate, AckIcnTime, ErrorCode1, ErrorCode2,
ErrorCode3, ErrorCode4, ErrorCode5, TimeCreated, RowFlags, LastModified
FROM dbo.bam_FunctionalAckActivity_AllInstances AS F
WHERE (GroupControlNo = T.GroupControlNo) AND (SenderID = T.ReceiverID)))
ORDER BY TimeCreated DESC
Thursday, December 06, 2012
- Reduce the size of VM image to small (smaller size and lesses processors)
- Ensure endpoint exists or create one.
- Delete the VM image and create a new one with a different geographical location. Try different locations within USA. (this did the trick for me)
Thursday, November 10, 2011
On executing the package, OLE Destination 1 shape would threw the following error:
“[OLE DB Destination 1 ] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "The statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'PK_tbl_so'. Cannot insert duplicate key in object 'dbo.tbl_so'.".”
I tried various online forums but the solutions didn’t work for me. Then I tried data encryption and it worked. Check the image below.
After I deployed the package to remote server, I tried executing it and received validation errror around login/username. I am using SQL authentication for the DB that will receive the data from excel sheet. Internally, SSIS encrypts the credentials information. If you open the package in a notepad, you can view the encrypted information. But, when the same package is run on a different machine the user context changes and decryption process throws the error. Setting the protection level as below can get you around that issue.
Wednesday, August 17, 2011
Microsoft is consolidating its offering around Desktop, Web, Cloud and Mobile with the latest release of Visual Studio 2011. These are very exciting times for Microsoft Developers, it seems that finally Microsoft is streamlining its technologies and removing the redundancies. Biztalk-Appfabric consolidation is happening along with Silverlight-HTML5 consolidation. Sharepoint story is unchanged and it is one of the few non-redundant technologies in the Microsoft stack.
So, where do we stand amidst all this? Consolidation of technologies will lead to more clarity for customers. One reason, Biztalk didn’t maintain the steep growth curve was because of the confusion created by conflicting technologies on the Microsoft Connected System stack. Customers like clarity, especially while evaluating different technologies to implement. They tend to select products with more clarity around their future roadmap. Microsoft shops in general are prime candidate for Biztalk, but they saw technologies like AppFabric and WCF close substitute for Biztalk. For most of them, sole reason for buying Biztalk was because they were an EDI shop. On the other hand, Biztalk was a tough sell in non-microsoft shops as it works with Microsoft framework. A java shop will most likely go for a GIS or webMethods implementation.
Real value of Biztalk lies in developing it as an integration hub for both EDI and Non-EDI integrations and then extending it to link with Sharepoint. Next step will be to combine on-premise and cloud integration on same platform. This is where Azure Appfabric is leading us to. Keep an eye on PDC (BUILD) space for more on this.
Wednesday, June 22, 2011
In my opinion, if one thinks of BizTalk as a brand then it could be said that BizTalk may die as a brand in near future. Even that is a speculation for sake of argument. But if one considers BizTalk as Integration enabler and a set of tools, then it can’t be dead. There may be re-organization around the tools but they will not die down. I agree that redundancies in form of Windows App Fabric and Windows Azure App Fabric do take something away from BizTalk. They also create confusion in the mind of new and existing clients. Especially, when the marketing pitch is not in tune with technical pitch. Few years ago, I would just blindly recommend BizTalk in scenarios involving hosted workflows and service compositions. But now it is not so easy. Microsoft has labeled BizTalk as Integration Server, Windows App Fabric is termed as Application Server and Windows Azure App Fabric is a cloud integration platform. It can get confusing between these 3 technologies. If I need to mathematically establish a relationship between these 3, it will look something like:
BizTalk >= Windows Server Fabric + Windows Azure App Fabric.
It can be argued that in near future Windows Azure App Fabric may expand to include more of the existing BizTalk tool set and that may make the relationship more like
BizTalk = Windows Server Fabric + Windows Azure App Fabric.
This is where people jump in and say that BizTalk is dead as it can be replaced by combination of the two technologies! In my opinion:
1. Azure App Fabric is not a mature product yet and it is too early to say anything. Remember Oslo?
2. It is quite possible that BizTalk tools will be re-organized around cloud platform using Azure App fabric as core. Depending on how one sees it, it could be branded as a new version of BizTalk with Azure App Fabric as the cloud enabler. This will not mean end of BizTalk.
3. Other possibility is that BizTalk and Azure App Fabric will continue their parallel paths for foreseeable future. This will not mean end of BizTalk.
It could be said that Azure App Fabric is a new technology that BizTalk developers will have to learn. Just like BizTalk 2004 release, made BizTalk developers learn .net. BizTalk 2006R2 made developers look into WCF.
Windows Server App Fabric is more of an Application Server than an Integration server. However, BizTalk Appfabric connect allows .net developers leverage LOB adapters and Mapper to create workflow solutions. This compliments BizTalk just like Azure App fabric does.
As a BizTalk developer, I have mixed feelings. On one side I feel unhappy that the BizTalk tool set is being broken and leveraged around different Microsoft stacks. Now everyone else will be able to enjoy the excellent tool sets of BizTalk that were once sole proprietorship of BizTalk developers. It kind of takes power away from us. But at the same time, I feel happy that as a BizTalk developer we are placed uniquely to leave our footprints across the different technology stacks that use BizTalk tools .
Following are list of tools that are still exclusive to BizTalk. These are key touch points while making a choice between BizTalk and other technologies.
• Accelerators (EDI, SWIFT, Rosettanet, RFID, EDIFACT, HL7)
• Flat File processing
• BRE (it is exposed as a REST API now but still under BizTalk licensing)
• BizTalk Mapper (BizTalk AppFabric connect enables you to use the mapper with Server App Fabric, still under BizTalk licensing)
• Non-WCF adapters like FileAct/Interact
• Host Integration Server
Tuesday, April 19, 2011
One possible solution is to extend the File Adapter that is part of SDK (Image 1.1). Add the 3 projects in a Visual Studio Solution as shown in Image 1.2
1. Build the solution, GAC Microsoft.Samples.BizTalk.Adapter.Common.dll.
2. Registry Key.
Use the StaticAdapterManagement.reg registry key file given in the sample and update the locations of the dlls mentioned. Microsoft recommends adding additional string for 64 bit machines. I didn’t do and it worked.
3. Go to Biztalk admin console and try to add the new EMPTY FILE adapter. It is possible that you may get an error similar too :
The system cannot find the file specified.
/C:\Program Files (x86)\Microsoft BizTalk Server 2009\SDK\Samples\CustomAdaptersDevelopment\File Adapter\Runtime\Microsoft.BizTalk.SDKSamples.Adapters.DotNetFile.Runtime.dll
This error threw me off as my registry key file did not contain this path at all and it left me wondering why is Biztalk even looking for this path. I did a search for this file in the entire registry and I found couple of references. I had no idea what were they used for but I knew that Biztalk was somehow reading them from registry key. I renamed the key for these entries without deleting . I was able to add the adapter to Biztalk successfully.
4. Once adapter was added, I assigned it to appropriate Biztalk host so I can use it in send/receive port. While setting the receive handler for this newly added adapter, I got the following error:
The Messaging Engine encountered an error when intializing the receive adapter "EmptyFile", HRESULT:"Property /Config/pollingInterval not found on adapter configuration XML.".
I decided to debug the code to find the point of error. I found the error in method : ExtractPollingInterval in file ConfigProperties.cs under project Microsoft.Samples.BizTalk.Adapter.Common.csproj
Update the following code
long pollingInterval = ExtractInt(document, "/Config/pollingInterval");
string pollingUnitOfMeasureStr = Extract(document,"/Config/pollingUnitOfMeasure", "Seconds");
long pollingInterval = Convert.ToInt32(IfExistsExtract(document, "/Config/pollingInterval","5"));
string pollingUnitOfMeasureStr = IfExistsExtract(document, "/Config/pollingUnitOfMeasure", "Seconds");
Or update the root name of ReceiveHandler schema to Config. I did the code change and not the schema change
I was in a position to configure this new custom FILE adapter and receive an empty file. In order to get a handle on the empty file, I need to go to method PickUpFilesAndSubmit which is part of DotNetFileReceiverEndpoint.cs under DotNetFile.csproj The line of code that got me the handle to the incoming file is if (item.Length == 0).
Once I got the handle, I was able to add my logic to deal with the incoming empty file. In my case, I decide to add a string “EmptyFile” if the incoming file was empty. I would then detect this file in the send adapter using the same custom adapter and assign 0 bytes to it. In between, I will have my orchestration reading the file name of this empty file (added string “EmptyFile”) and route it to appropriate directory folder.
This solution requires tampering with contents of an emptry file. It may be to acceptable in some cases. In my case, it was acceptable.
Send Side Changes:
To intercept the EmptyFile (file with “EmptyFile” string) on the send side, I used the method: ProcessMessage in file DotNetFileTransmitterEndpoint.cs under project DotNetFile.csproj.
I inserted the following snippet after cloning the stream. This writes an empty file.
StreamReader sr = new StreamReader(Stream);
string fileContents = sr.ReadLine();
Dynamic Send Port:
In order to get this adapter to work with Dynamic Send port following changes will be needed:
1. Use DotNetFILE:// (same as the entry in registry) instead of FILE:// in orchestration for setting dynamic address
2. In the adapter use the following code to extract destination string from the context properties:
filePath = message.Context.Read("OutboundTransportLocation", "http://schemas.microsoft.com/BizTalk/2003/system-properties").ToString();
Use this destination string to parse the final destination of the file and add code to write the file to that location.
Thursday, December 30, 2010
I tried hacking my way by physically creating BAM application on the IIS but did not work either. Since BAM is a pre-compiled version, I could not debug it as I would do in an asp.net web application. Then, I thought of creating a new website called "BAM" at the same level as the "Default Website" but on a different port (not 80). In my case, I used port 81. I ran the Biztalk Configuration wizard, pointed it to "BAM" site instead of "Default WebSite" and it worked!!
Later, I did some research and found out that this is a "bug" or a "known behavior" with Biztalk 2010 installation. It seems creating a new site for BAM is generally accepted solution.
Check out :http://social.msdn.microsoft.com/Forums/en/biztalkgeneral/thread/ef650460-c219-4b34-bc7a-41bb7e75ec81
I would like to hear from others who faced/or not faced same issue.
Monday, December 27, 2010
StreamInsight is part of SQL 2008R2. Do some reading on StreamInsight before checking out this paper.
This paper is very conceptual and does not provide any code snippets. If you are looking for some code samples, then use the samples that come with StreamInsight download and extend them.
Monday, December 20, 2010
Since this feature is part of Biztalk 2010 installation, Biztalk license is needed to use this feature. In future this can and will change. It will then be possible to create custom WF that can be persisted to a DB and exposed as WCF endpoints, while mapping .net types using Biztalk with same set of mapping functoids that are part of Biztalk world. And all of this will be free!
Thursday, December 16, 2010
Tuesday, December 14, 2010
So, I came up with a simple scenario.
1. Expose a CreditScore service using Biztalk within a firewall.
2. Publish this Biztalk Orchestration as WCF to Cloud Endpoint using WCF publishing
3. Use a simple windows client application to send a message and receive a reply from this Biztalk service exposed within firewall.
Create a two way Biztalk Orchestration that accepts an xml message(First Name, Last Name, SSN) and returns a message (Credit Score and Comments).
Publish this Orchestration as the cloud endpoint.
1. Biztalk 2010
2. Windows AppFabric SDK 1.0
3. Windows Azure AppFabric Account to create the URL of cloud Endpoint (Free, if you have MSDN subscription)
4. Biztalk 2010 adapter pack
For details checkout : support.microsoft.com/kb/2449478
Step 2. Create a windows client to test this Biztalk service using endpoint exposed in the cloud.
Pre-Requisite for the client machine:
1. Visual Studio(2008 sp1 or 2010)
2. Azure AppFabric SDK 1.0 (to get NetTcpRelayBinding and TransportClientEndpointBehavior).
a) Open a "Windows Form Application" Project
"C:\Program Files\Windows Azure AppFabric SDK\V1.0\Assemblies\NET3.5\Microsoft.ServiceBus.dll"
c)Add Service Reference point to the URL exposed in the cloud : Sample URL:
shashiraina-demo.servicebus.windows.net/Test.Biztalk.Cloud.Orch/. This namespace in URL is created using AppFabric Account. (Image below)
d) Use the proxy to generate client side objects. I used code instead of config file because, I was not able to get "TransportClientEndpointBehavior" and "NetTcpRelayBinding" to show up in Config file. This may be because I was just using a Windows Application as test client instead of "cloud" supported client. With code, I could easily use the ServiceBus namespace and get it working.
Sample client code is below:
GetCreditScore.GetCreditScoreRequest req = new GetCreditScoreRequest();
GetCreditScore.GetCreditScoreResponse res = new GetCreditScoreResponse();
NetTcpRelayBinding tcpRelayBinding = new NetTcpRelayBinding();
TransportClientEndpointBehavior clientEndBeh = new TransportClientEndpointBehavior();
clientEndBeh.CredentialType = TransportClientCredentialType.SharedSecret;
clientEndBeh.Credentials.SharedSecret.IssuerName = "owner";
clientEndBeh.Credentials.SharedSecret.IssuerSecret = "Extract this from Appfabric Portal";// (it will look something like the crossed out section in the Image 2" )
EndpointAddress endpoint = new EndpointAddress("sb://shashiraina-demo.servicebus.windows.net/Test.Biztalk.Cloud.Orch/WcfService_Test_Biztalk_Cloud_Orch.svc");
WcfService_Test_Biztalk_Cloud_OrchClient client = new WcfService_Test_Biztalk_Cloud_OrchClient(tcpRelayBinding, endpoint);
req.Score = new Score(); ;
req.Score.FirstName = "Shashi";
req.Score.LastName = "Raina";
req.Score.SSN = "123-234-2323";
res.Root = new Root();
res.Root = client.GetCreditScore(req.Score);
Response from the service is below: (Go Ahead and tried it out from your machine)!
So, without punching a hole in your DMZ, you could expose your Biztalk services to outside world. I have been involved in complex configurations in ISA reverse proxy to expose Biztalk services to outside world. But using "Appfabric Connect for Services" , all these complex configurations can be eliminated. Moreover, the ACS (Access Control Services) that is part of Azure Appfabric allows creating your own plug-in security framework.
Monday, November 29, 2010
Tuesday, September 28, 2010
In scenario when the incoming message is a Non-EDI type (i.e a Positional Flat file), we have two options to promote the context properties that are required for Batching orchestration to work correctly. First option is to use Orchestration and second is to use a custom pipeline. In this example, I will demonstrate how to use an orchestration:
1. Create an orchestration that takes the Flat File message as input message
2. Create a Outbound Msg (Msgout) representing EDI schema
3. Add a map that does FF-->EDI conversion.
4. Create a correlation set containing 3 EDI context properites. Set these 3 propeties inside an expression shape within the context of a constuct block.
EDI834_Out(EDI.BatchId)=1; //get this ID from "Parties"
EDI834_Out(EDI.EncodingType)=0;// 0 for X12; 1 FOR EDIFACT
5. Initialize the correlation set in send port. This is the key step to promote the 3 context properties else you may end up "writing" the properties and not promoting them.
6. Bind the Orchestration Send Port directly to MessageBox
7. Create a Send Port that Filters "EDI.ToBeBatched=False". You may further differentiate by adding filter for BatchID.
8.Deploy the orchestation and start the send port.
Saturday, September 04, 2010
1. Modify the relevant EDI schema and preferably modify the namespace to differentiate it from standard schema. Deploy it.
2. Go To-->Biztalk Admin Console-->Parties-->Select Partner--> Right Click-->EDI Properties-->Click on "Validation and ACK"-->Check "Extended Validation" (Image below)
Checking "Extended Validation" tells Biztalk Engine to validate against the custom schema validations. This is in addition to standard EDI schema validations that come out of box.
Friday, August 27, 2010
1. Add a new hard drive (.vhd file) to VPC of desired size but more than the existing hard drive (.vhd drive) that needs to be extended. Call this New partition D:
2. Use DriveImageXML a free software to create an image of existing hard drive (C:)
3. Restore the created image on to (D:)
4. shut down the VPC and remove the original drive C and keep only drive D:
5. Fire it up, you may get issues in booting up the new drive. Use http://www.runtime.org/driveimage_faq.htm. Go to section:
I restored my boot drive, why does it not boot?
and follow Fix Manually section.
6. This should boot up the machine with new hard drive D:. But you may end up having a blank desktop and no programs will work. This is because they all were installed in drive C and the new drive being used is D.
7. To fix this use: http://support.microsoft.com/kb/223188
Saturday, December 27, 2008
"Cannot access a disposed object. Object name: 'TransactionScope' "
On investigation, I found that this can happen when:
- Recieve port/location does not have a corresponding subscription (orchestration / send port) created.
- Send Port is created but not enlisted. This is same as no subscription found in database.
When enable transactions check box is clicked while configuring receive location as shown below:
Behind the scenes, a transaction scope object is created that will attach itself to incoming message sent from a client and form a one continious transaction involving MSDTC. This enables WCF client to control the whole transaction involving Biztalk. Client can use "commit" and "rollback" commands to commit and rollback message in Biztalk messagebox database. Very powerful!
At core, Biztalk works on pub-sub mechanism. In absence of any subscription, receive location can't really participate in transaction and create a transaction object. Hence the error is raised "cannot access a dispose object" (transaction object)
Solution is simple: Create a subscription for the receive port/location in form of a send port or an orchestration.
Sunday, October 05, 2008
Step 1: BIZTALK TEAM
Biztalk side needs to define a Program ID. SAP adapter then registers this Program ID to the SAP gateway defined in the URL. This Program ID then needs to be registered from SAP side. SAP admin should follow “Performing Tasks Using the SAP GUI for Specific SAP Adapter Scenarios” section of SAPAdapter.chm file.
Program ID will be unique per listener else SAP will send the document to random listeners. Each Receive location will be tied to a unique Program ID for an environment. Example: If same receive location is being used in 3 environments (DEV, QA and Prod) then 3 Program IDS are required to be set up for that single receive location. RFC checks the connection between SAP and biztalk using program id registered channel. SAP then sends IDOC to biztalk. Biztalk accepts this IDoc and processes it.
Configure recv location as shown:
Configure following URL at recv location
sap://CLIENT=[client ID] ;LANG=EN;@a/[servername]/[system ID]
?ListenerGwHost=[hostname]&ListenerGwServ=[gateway service name] &ListenerProgramId=[Program ID}
SAP team should supply with the above variables.
Do not forget to ask for user name and password to configure screen below:
Step 2: SAP TEAM
SAP team to supply:
1. client ID
3. system ID
5. gateway service name
6. User ID
Thursday, April 24, 2008
BAM and WSS adapter have conflicting requirements for 64 bit machine. BAM needs 32 bit IIS and WSS 3.0 needs 64 bit IIS. To make matters further complex, it is not possible to host 32 bit IIS and 64 bit IIS on same 64 bit machine. So how can Biztalk communicate with a MOSS server running on 64 bit machine? Following configuration makes this possible:
1) Enable 32 bit IIS on the 64 bit machine by running following command:
cscript %SystemDrive%\inetpub\AdminScripts\adsutil.vbs set w3svc/AppPools/Enable32bitAppOnWin64 1
(1 means “true” and 0 means “false”)
2) Now, IIS is 32 bit and follow standard BAM configuration steps.
3) Install WSS 2.0 SP2 on the Biztalk machine to enable configuring WSS adapter. Also, configure WSS adapter on MOSS machine that hosts WSS 3.0
4) To install WSS 2.0, register ASP.Net 2.0 (32 bit version) from “Windows\Framework” folder by running aspnet_regiis.exe –i
5) Download and install WSS 2.0 SP2
6) Install WSS 2.0 in server farm mode and then create and extend the website. This website will host the WSS adapter.
7) When installing WSS 2.0 on clustered machine, use option “Join existing …”
8) Run Biztalk Configuration wizard and configure WSS adapter on Biztalk machine.
9) Run Biztalk Configuration Wizard on MOSS machine and configure adapter there.
10) Make the AppPool Account that is running WSS 2.0 website a member of SharePoint Group and SharePoint Site Collection on MOSS machine hosting WSS 3.0.
When WSS is configured, it creates BTSharePointAdapterWS. WSS adapter uses this web service to communicate with MOSS form libraries, lists and document libraries. This service need not be running on Biztalk machine but it should be running on MOSS machine for proper communication back and forth.
Tuesday, January 08, 2008
1) Use "Update Task" shape for updating Task A and drop it in the "parallel" arm of Task B. The position of this task could be above the "Complete Task" or as required. But it should be under the "create task" shape for Task B.
2) Here is the imporant part. Set the correlation token as the correlation token used for Task A even though this task is under Task B related tasks. This correlation token will direct the update task to the correct task (Task A in our case)
3) Bind the TaskID to existing Task ID for Task A.
3) Use syntax "TaskProperties.ExtendedProperties["NameOfInfopathField_inTaskA_thatis_to_be_updated"]="desiredvalue"
Parallel workflows in WF are actually processed in a sequence. Left branch is processed first and then control moves towards the right . While cross updating tasks, correlation ID is the key that ties the different tasks together. For tasks on the extreme right arm, all the correlation IDs in the workflow are available. But this selection reduces are we move towards left. In order to update a task on the right side of a given task, the correlation ID will not be available from the work flow design view. But going to deign code file(*.designer.cs / *.designer.vb) gives the flexibility to link the correlation ID with the task that is to be updated.
this.UpdateFromTaskOnLeftSide =CorrelationToken (this will be token for the task on the right side that is being updated from left side).
Monday, January 07, 2008
This is what you do:
1) Create a table and name it "RulesGroup". Add two columns : UserLogIn and UserGroup
2) In the infopath form create two text box control: UserName and UserGroup
3) Create a secondary datasource and configure it to receive data from SQL database. Use the wizard to point to the correct table.
4) In the infopath form: Use infopath function username() to populate UserName text box.
Map the UserGroup text box with following Xpath:
xdXDocument:GetDOM("RolesGroup")/dfs:myFields/dfs:dataFields/d:RolesGroup[@UserLogIn = "shashi"]/@UserGroup
PS: XML generated by the wizard marks the 2 user defined columns as "attributes". So there is need to user @ in Xpath expression.
In our case if UserName is "shashi" the UserGroup will be "Operations".
New users and Groups can be added in DB as required and accordingly Infopath Views can be programmed based on Groups.
Remember: While using form services to host infopath form, additional requirement will be creating a new Univeral Data Connection(UDC) file in Data Connection Library under the site collection. This UDC file maps the connection details between infopath form and SQL service. SQL server can't really authenticate the user name being passed directly from the infopath form. The user name comes as "" (blank). One way of getting around is to use proxy user name and password in case the user name is blank. But this will compromise security in Big Way. Use of UDC file helps in this regard. Also, it allows you set up custom User Groups based on User Names. These user groups can be then used to limit access to certain views as explained before in this article.