SSIS Package Error

Biztalk is not always the right tool for Bulk data inserts. SQL Integration Services provide powerful tool set for inserting large bytes of data from source ( flat files / excel files/DB) into DataTables. In my scenario, I created SSIS package using SQL 2008R2 Business Intelligence Development Studio. It was a simple Data flow, read the excel file from a network location and insert to a remote DataTable. Image below:


On executing the package, OLE Destination 1 shape would threw the following error:
“[OLE DB Destination 1 [421]] 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.







Update

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.

Microsoft BUILD

Starting this year, Microsoft rebranded its Professional Developer Conference (PDC) as BUILD. I have no idea what message they are trying to convey with this name change. For some strange reasons, this change was not broadcasted strongly by the numerous Microsoft blogs and sites. The registration page was so hidden that even search engines could not find it using standard words like “PDC Microsoft conference” etc. Well, for people who still don’t know, BUILD is being held in Anaheim, California in middle of September this year. At the time of writing this article, no agenda have been published by Microsoft for the conference. This is rather strange for an elite conference like this. My guess is that there will be breaking news around direction of Biztalk Cloud technologies. Update to Windows mobile platform (“Mango” code name) will get good coverage. Microsoft is really pushing hard to break into the Smartphone market. Even though Mango is looking like a robust platform, it is hard to penetrate the barrier created by Android and iPhone. Recent buy out of MMI by Google, bodes well for Microsoft. It leaves windows mobile as the only true Open Source mobile platform in the market. It is rather ironic that a company traditionally linked with developing proprietary technologies is taking this mantle from a company like Google that is traditionally linked with developing and encouraging Open Source systems. This demonstrates the fast changing technology landscape and underlines the difficulties faced by CIOs in planning strategic IT initiatives.

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.

Death of Biztalk -- Are you kidding me?

Honestly, I am tired of statements like “BizTalk is Dead” or “BizTalk is Obsolete”. With every release of new technology around Connected System stack, the noise around speculated BizTalk demise gets louder. Right from the time Oslo was released (which never saw light of the day) to recently released Azure App Fabric –June CTP, blogosphere gets noisy with shouts of BizTalk is Dead.

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)
• Pipelines
• Flat File processing
• BAM
• 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

Processing Empty Files in Biztalk

In my current integration project, there is a need to process empty files (0 byte) in Biztalk. Default behavior of FILE adapter is to delete an empty file and raise an event. This empty file is then deleted even before it reaches pipeline on way to Biztalk MsgBox. So there is no way to do a routing based on filename either in a pure message scenario or using an orchestration.

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

Image1.1

Image 1.2

Key steps:

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

To

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();
if (fileContents.Contains("EmptyFile"))
{
fileStream.Write(new byte[0],0,0);
return null;
}

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.