Experience on SSIS and SAP, inspired after viewing some presentation

Experience on SSIS and SAP, inspired after viewing some presentation.

I inspired for the article from one article posted on MSDN blog about SAP and SSIS. It's really cool stuff from Matt Masson in PowerPoint presentation.

Please have a look into the below presentation by Matt Masson. it's really great to know about SAP and SSIS!

BizTalk Adapter Pack (SAP) installation

BizTalk Adapter Pack (SAP) SSIS integration

BizTalk Adapter Pack (SAP) SSIS integration (Advanced)


Below I am just sharing my experience with SSIS and SAP BW/APO.

I have worked in one of project doing integration between SAP BW/APO modules to other ERPs (like JDE, PeopleSoft etc). For that we used to workout everything by building IDocs inside a SSIS Script Component. It's very time consuming but right now I don't think rather then BizTalk there is any simple option available to build and submit IDocs. I Used to build all require segments like Control, Header and Data inside script task and then composing message through Flat File Connection Manager to build IDoc.

As of my best knowledge currently no tool available in market which can give full functionality to play around IDocs inside SSIS (may be I am wrong, you need to search extensive what such features you are looking for and what are available). But the best option I have implemented almost one year ago in my first SSIS implementation was creating IDocs programmatically inside SSIS. Obviously, I need to implement my logic inside script activity and flat flie connection manager, it make system bit slow but still configurable and acceptable. It is time consuming for initial period to build logic but I think once we build it, it's best option to stick on your requirement with few changes in your code to build new structured IDoc.

Really hoping from Microsoft to have SAP Connection Manager if anything near to automation/parser and connection manager it will be adding great value to product and will be great help for such people don't want to connect to SAP DB directly and want to push data by safer IDoc option.

At end really want to appreciate the efforts from Matt for such presentation.

Thanks for reading my views and please contact me for your feedback or suggestion on nilayparikh@gmail.com.

Looking beyond to ETL from SSIS, next door can be EII - Enterprise Information Integration.

Below paragraph is pre-face of the article, and it's mentioned about inspiration of the article, if you wish to skip it please go to next paragraph. :-).. But I would recommend reading it.

Recently many things cooking around Microsoft Data Services, I hope it wills surely include more services which can be much helpful to implement difficult EII scenarios. New term Database as a Service (DaaS, name cloning from SaaS). Frankly speaking these days war between terminologies is far higher then technologies, aaahh forget about it, it's very confusing which do what!!!! nowadays, terminologies are becoming face of product and patterns are became abstract :-). Anyways let's come to core part of article, actually I was working on very nice requirement where system need to provide a merged data view from multiple system (heterogeneous sources and also multi-cultured also :(..). Previously we used to do complete data integration (As EAI Implementation) with moderate latency across whole system into one web application which provides grid view and reports; it was very frequently used by users and very important too. But this integration consumes 200GB of database for just viewing and reporting purpose (we keep only 15 days data and for that we have to invest minimum of 250 GB data storage on SAN and other resources for implementation of EAI like BizTalk server, message box database, traffic, bandwidth etc...). It was clearly an requirement of EII implementation, but previously it hadn't been identified as EII requirement and individual implementation, too. But after massive discussion (fights) we were implement that system as EII with use of SSIS (SQL Server Integration Services 2005) only. From that time I had the inspiration of this article. Thanks for reading it.

Back to Business:

Microsoft.SQLServer.Dts.DtsClient name space, I am SSIS developer too, but was not much familiar about its functionalities earlier. As a problem I described in above paragraph we design few complex SSIS packages with heterogeneous data sources and data views, merge joins, lots of shorting, querying, cleansing, etc. Here for demonstration I am unable to saw original design but will follow one sample design I have developed specially for article.

In below design, I am trying to querying to two different data sources which have entirely different databases and connected through one common key and I am trying to generate single dataset for viewing and reporting purposes. You can see below data table snapshots where first figure shows first names of people and second table shows last name of people. Both tables are in different database. Here data sources could be any kind of supportive sources inside SSIS for the concept.


Fig 1. Database Tree (two different databases - test_1, test_2).



Fig 2. Table 1 (Db1- table contain first name information).



Fig 3. Table 2 (Db2 - table contain last name information).



Now have a look to sample SSIS package, just I am doing merge operation and storing into data reader as a destination.


Fig 4. Sample SSIS package.



I have also developed one console .Net application for testing and demonstration purpose to call SSIS package as single point data connection which can provide me processed and integrated data within one single record set. Below is sample snapshot of coding, in real-time solution we used WCF services to provide external domain and other application as an exposer of the EII implementation. But here I am trying to demonstrate through C#.Net Console Application.


Fig 5. Console Application Code (C# Console Application).



Fig 6. Receiving data into one data reader.


Fig 7. Output.



You can see above snapshot of output that I received integrated query's result in single set.

Isn't it good? There are many benefit of such implementation I can say. The major benefit is shrink EAI landscape which will decrease support team size (Cost... Managers are very happy to listen, additional benefit) and we can free resources to better performance of it. Another important benefit is, we can avoid non-require expose of database and our precious data to other system which only need for viewing purpose, we can archive it by providing and implementing parametrise/on demand EII Data Services (SaaD :-D). I don't want to go inside other benefits we can archive from such implementation but defiantly it could turn into beneficial implementation for every organization which has such requirement.

Please share your views and comments as well as if you have any suggesting please free to suggest me on nilayparikh@gmail.com

Thanks for visiting my blog.

MOSS 2007 - Workflows and it exposes to Enterprise Information Integration and Enterprise Application Integration

I am personally very excited about MOSS 2007 and it's brand new key futures, from last few weeks I am really involving my self to see MOSS's new key futures and it's innovative implementation(s) for those areas intersecting with Integration, rather it's Enterprise Information Integration (EII), Enterprise Application Integration (EAI) or Data Integration (ETL). I have worked in my past with SharePoint Portal Server 2003 and it was really complicated to integrate different user activities/events with across the enterprise through BizTalk and Web services. Always it dragged me to develop more custom components/web parts/web controls and calling web services, transferring information and events through BizTalk server. Looks really complicated but believe me it was much tougher then it looks...

Now at new mission I am trying to find out more flexible integration/Work flow base implementations, here I have developed one sample solution to support my view on the Information Integration within MOSS 2007 Tasks and announcement. I am primarily trying to develop some workflow which can pick announcement which is direct live integration with sample Loan Application Management (any custom application generate application details) through WF (which hosts on Windows Services and connected through WCF for my sample, it's open space for any integration tool provide direct integration with MOSS site like BizTalk ;-) etc...). Let me comeback to sample, now I have developed one pilot Workflow which pick each announcement and generate tasks on SharePoint as well as through WCF/Web services it integration with external applications too. Another Workflow application get invoked on completion event of those Task and automatically generate following to do tasks and assign automatically.

Now these workflow also provide me standards functionalities of Windows Workflow Foundation, like listen, event base routing (you can check my previous articles to know more about event base routing), it's really empower developer to go beyond the classic and traditional boundaries of SharePoint sites to next generation workflow and information oriented collaboration development.

Let's back to sample, workflow1 picks announcement and process it and generate proper Task and other artifices, too. In the process of generating automated task and artefacts it's utilize many EII services to authenticate credit and other historical information of an applicant.

You can see below snap, first activity shape will invoke the workflow on any new announcement arrival or creation. Then the workflow is trying to received data and contract through Get activity and parallel processing other code too. At end of successful processing of whole parallel shape it will create all tasks needed for the announcement and at end it will log whole history. Please have a look at figure-1 and figure-2.





After deployment on to your MOSS Site you can check the workflow status by going to Site Settings->Galleries->Workflows. You can see figure-3 and figure-4 for site navigation and Workflow Collection. Site Collection Workflow will show you the association of the workflow to different activities and In-progress workflows too.





Let's come to another sample workflow of the pilot solution, which watch on every complete event of Task and according to the event it flow the application further for consideration and human approval. Where it also involve it self into request response with Web services and EII Service contract through workflow service. And similarly create further to do events in MOSS site.
Have look to MOSS Site Snaps.

1. Creating announcement for sample Loan Application.


2. You can see below figure announcement has been added to list. It shows 11111 id for loan application.


3. After sometime it was picked by workflow and processed, in solution I have appended string called " Processed By...", you can see here it has updated title of announcement and also added new relevant task for the loan application. During the creating of new task it went though to WCF application and receive back some valuable credit information and rating.


4. Task's detail page, you can see here generated description and information.


5. Once Task has been completed another workflow2 processed it and generated siblings task and to-do also it updated the title of the task (You can track related artifacts by "11111" id).


Such futures of MOSS 2007 make life easy for Integration developer and we can also develop such infrastructure suits MOSS 2007 features which help more easy and quick development for complex definitions.

Previously I was using BizTalk adapter or custom .net code but it always want huge resource to implement such workflow and integration for SharePoint site also it increase high volume of data and security threats while such implementation like workflow really help to develop much abstracted information and service access layers and less resource consuming infrastructure artifacts. I analyze such implementing with workflow pattern and pilot application performance results, it has ability to shirk our transaction volume across BizTalk by 40% compare to previous and 50% better performance with easy to maintain requirement. These are not derived figures but these are my assumption on the standard scenario which I analyzed.

Hope you liked this article and please send your valuable feedback and ideas to nilayparikh@gmail.com

Orchestrated windows communication foundation service (WCF + WF = BizTalk Content Routing)

These days... I start looking back to interesting .net 3.5 stuff, really impressive. Especially I can say with .net 3.5 sequential workflow and its two data shapes (receive and send) and their promotion to WCF services. Infect this is very good functionality toward integrating more complex collaborated scenarios with different domain application. Previously, it was really hard and time consuming to collaborate using open standards like web services, EAI (BizTalk), MSMQ etc, and it was bit effort consuming as well as contain higher risk on success of the design. Here I feel very nice and good solution towards such complex collaboration scenarios' integration to cross domain.

WCF itself is well functionality talk as open with open standards like WSE 2.0+, Queuing (MSMQ/JMS), .net Remoting, TCP/IP and web HTTP/HTTPs, etc. I must say this deadly combination will transform much complex scenarios in very simple WF/WCF solutions.

I tried to build one very simple collaborative solution. This can receive message through WCF Service which is simple passthrough service and routing incoming messages to WF. I found WF is very much flexible towards handling external events, tracking, hosting (with multi-threading).

I tried to draw one quick diagram which might help to understand the concept.


I also tried and simulate some work load on the sample application with the concept and I got surprised with really great performance. I pumped around 200 messages per second with approx size of 50kb and really it went smooth with some complex resource consuming XSLT transformation and file delivery. Where while building the application I programmed to raise max 5 threads for the transformations and delivering WF services. My correlation and Initializing WFs was working on single instance thread. But I was calling other WFs from main WF to performs transformation and delivery services.

Just thought this would be great to share with MS Developer community. I hope Microsoft is planning to put WF and WCF into future version of BizTalk on the same basis. Now, after success of the POC sample, I am analysing the replacement of content routing from BizTalk Server to WF + WCF and planning shift much load from BizTalk to standalone WF Hosting servers.

Let's see how it may go! I am very hopeful for this implementation and success of it as cost wise and performance wise too.

If anyone is interested in POC code most welcome to request on nilayparikh@gmail.com

Thanks.

Consuming WCF Services in BizTalk Server 2006 R2 - Add generated items (WCF Services)

I was thinking a long back to come up with this article, and now just found time to write about WCF services and it's implementation with BizTalk Server 2006 R2. I have designed few small solutions in past few months to deal with scenarios like Information Integration and SOA with BizTalk Server R2, where I found the great chance to work upon the WCF adapter series as well as Web Service Factory : Modeling Edition. Both made me big fan of them, it's great to work on both, I am not focusing on WebService Factory modeling in this article but will discuss about WCF services consumption inside BizTalk and few videos might help people to work on them.

I would like to share few images below, by reading this images you can understand the service I have coded for sample, if you are interested in sample code please mail me, and I promise you will find that sample someday in you mail box... when that I don't know. :-) But will try send you soon as possible for me.

The best way I feel to consume WCF services inside BizTalk solution is by using WCF Services Consume Wizard which you can find in Visual Studio Template, this wizard will power you by generating schema and port binding configuration files for your solution based on service descriptions. Not going into deep how this work internally here but focusing on the practice I gone through.

Below image shows Generated Items and Visual Studio Template.

Please have a look to the below Video Presentation where you can find what steps I followed to generate my sample project. Hope it will surely help you in any manner while working with WCF and BizTalk together.

As result of Wizard you will get schema and binding files inside your solution, that schema will be generated on basis of the metadata of the consumed services and it could be used as multipart messages in case of request-response services. Below snaps will show you the BizTalk schema presentation against the .net message contracts and data contracts.

DataContract Source Code:


MessageContract Source Code:


Service Implementation Code:


BizTalk Schema:



BizTalk Test Message I used for my sample:

You can analyze your self the presentation of Inbound message and the Contract representation inside WCF Implementation upon the above snaps. You can consume the generated schema in routine BizTalk solutions and patterns, while deploying such solution you can use generated binding information for such implementation with Endpoint transportation and Behavior information regarding WCF hosting and services. It is very good feature provided by wizard which make our life very easy :-). Let's have a view to below video presentation on the WCF Http and Custom Adapter configuration inside through BizTalk administrator console.

Hope you liked this article and it will give you fair idea about the BizTalk Server 2006 R2 and WCF coupling in Solution, as a system/solution designer point of view I really enjoyed to work with this two made for each other technologies and it provide such ease to workout most complex scenarios regarding service orientation and integration. As well as I realize the solution's capability to work over Internet Integration where security is the major concern and we generally prefers to go with ws-HTTP or HTTP channels for integration, on such scale it can help us to give such secured integration system.

Please provide your feedback about the article at nilayparikh@gmail.com.

Web Service Software Factory - Walk through Video (WCF Implementation)

Recently, I got very good opportunity to work on WCF Services through WebService Software Factory, it's a really nice tool, I have seen so far to build complex WCF services in very easy manner, no really I means. Yes, there are some limitation of the tool and it's not supporting all features of WCF implementation but you can say.. it's very nice kick off point for your project and code, later you can defiantly customize your code by modifying the auto-generated code itself.

While working on factory, I grabbed opportunity to record my activities through one screen recording software and I would like to share that video among IT Professional community. Please have a look and share you view on such beautiful tool.

Service Modeling & Message Modeling walk through:


Code Generation walk through:


You can also find some more videos on below link.
http://www.codeplex.com/servicefactory

Please send me your feedback and suggestions about the above walk-through on nilayparikh@gmail.com

BizTalk 2006 R2 with PeopleSoft Integration & MicroSoft BizTalk PeopleSoft Adapter Overview

Recently, I come accords requirement to integrate PeopleSoft ERP implementation within existing ESB. I have worked on many PeopleSoft integration through BizTalk, so just taking opportunity to write something exciting about knowledge I have gathered.

MicroSoft is very much aware about importance of PeopleSoft ERP and it's huge existence in Industry, so BizTalk R2 LOB adapter set has include PeopleSoft adapter which is very useful artifact during our PeopleSoft integration, it facilitate developer in many manners like developing schemas, messages. There are few limitation also we are facing with the adapter but those limitation could be overseen with different kind of implementation patterns and of course we can archive most of requirement what we want to with the adapter. There are also many third party adapters are available for PeopleSoft integration but I have explored more, the only one third party adapter I have worked with is iWay last year, yes it is very impressive but while comparing overall prospective I would defiantly go with R2 LOB. But it purely depend on the requirement and what extend we are looking for integrate the systems.

Microsoft Line of Business PeopleSoft Adapter's Architecture.

LOB PeopleSoft adapter's Prerequisites:
# The Java 2 Platform must be installed on the BizTalk Server that the BizTalk Adapter for PeopleSoft Enterprise is running on.
# The PeopleSoft Java Object Adapter JAR file, psjoa.jar should be copied to a folder that is accessible to the BizTalk Server that the BizTalk Adapter for PeopleSoft Enterprise is running on.

Implementation & Design Patterns:
BizTalk PeopleSoft adapter facilitate your requirement to query and receive data from PeopleSoft ERP also you can do basic data operation like Insert, Update and Delete. But here one thing very clear, but it will not allow live integration from outbound direction (from PeopleSoft). To archive such requirement you can implement services inside PeopleSoft which can push live data feed into any queue or physical location where BizTalk or any external service can monitor feeds with very less latency, so how you can get such live implementation.

I would like to share the architecture, I designed for such live implementation landscape recently. You can view the picturing below.

The above architecture, I designed for the two-way integration with PeopleSoft for our client. Low latency outbound messaging integration can be archived by MSMQ or any other queuing implementation between BizTalk and PeopleSoft. Also many time in different scenario requirement we need information integration between different systems that could be archive through cutting edge technology like WCF / WSE 2.0 where BizTalk R2 provide functionality integrate such services, you can also give term like 'EII' to such implementation. More over by implementing such landscape we are moving towards service orientation and service bus. BizTalk provides a ease to integrate many systems within such implementation, also it leverage extensibility of ERP implementation.

You can also go through below links for further references at MSDN.

1. Tutorial: Using the BizTalk Adapter for PeopleSoft Enterprise to Retrieve Data from PeopleSoft Enterprise
2. Tutorial: Using the BizTalk Adapter for PeopleSoft Enterprise to Write Data to PeopleSoft Enterprise

Please provide your valuable feedback on nilayparikh@gmail.com.