Sachin R. Chavan's - BizTalk Blog

Calling .Net assembly in SSIS - The Flexible n Reusable Way.

Sachin R. Chavan — Fri, 01 Jun 2007 05:14:49 GMT

Many a times in the SSIS Script Task one wants to call a .Net assembly to reuse/leverage the existing functionality.

Now, any .NET assembly you want to refer from an SSIS script task must be in GAC.

In order to access the assembly one has to add a reference to the assembly from SSIS Script Task IDE. But for your assembly to appear in the Add Reference Window it has to be copied under .Net Framework Folder i.e. 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727' Folder, it’s just not enough that the assembly is installed in GAC.

This particular limitation is because Script Task uses the Visual Studio for Applications (VSA) under the covers.

There is a peculiar limitation of VSA that requires you to place any DLLs that you want to reference into the Microsoft.Net\Framework folder of your machine, it's not enough to GAC it.

So what if I just wanted to GAC the assembly and still be able to access it from Script Task???

Certainly we have to use .Net Reflection but what if you are using 2 or more assemblies in SSIS Script task?? Or Are calling .Net assemblies in more than one script task??

You have to Write the .Net Reflection code to create instance that many number of times.

But wait there's one more way that’s easy and reusable.

What I do is, use a function that uses the Reflection to Load the desired assembly at runtime and create an instance of the required type or class.

What I do is copy paste the following function in my script task

' assembly2Load - i.e. Assembly Name: MyProj.Common

' class2AccessWithinAssembly - e.g: FTPDirectoryLister

' Function for Creating Assembly Instances

Private Function CreateObject(ByVal assembly2Load As String, ByVal class2AccessWithinAssembly As String) As Object

'Define variable of type [Assembly]

Dim targetAssembly As [Assembly] = Nothing

'Load your desired assembly from GAC into defined variable e.g. "MyProj.Common"

targetAssembly = [Assembly].LoadWithPartialName(assembly2Load)

'Define variable which used to hold “Type” object

Dim targetType As Type

'Get specific class from assembly e.g. “FTPDirectoryLister” Class

targetType = targetAssembly.GetType(assembly2Load + "." + class2AccessWithinAssembly)

'Define variable of type Object

Dim reqdObjectInstance As Object

'Get the instance of Class Specified e.g. “FTPDirectoryLister”

reqdObjectInstance = targetType.InvokeMember(class2AccessWithinAssembly, BindingFlags.CreateInstance, Nothing, Nothing, Nothing)

Return reqdObjectInstance

End Function

How do I call this Function? Simple:

Dim objCommonAssembly As Object

'Create Common Services Object to Validate the Generated XML

objCommonAssembly = CreateObject("MyProj.Common", "FTPDirectoryLister")

'Once the desired class instance is ready you can call methods in it

Dim listing As String

listing = objCommonAssembly.GetFTPListing(ftpURL)

Finally,

Since we are using reflection we have to add this line in Imports Section

Imports System.Reflection

Also, we have to set:

Option Strict Off

on the first line of the editor, this is required for our casting in the CreateObject function

That’s it we are done. Now one can use the same function in multiple script tasks just copy paste it.

I use this function a lot as it offers me flexibility an code reuse. Hope it will be useful to you too!!!

FOR XML PATH - A New Mode in FOR XML with SQL Server 2005.

Sachin R. Chavan — Mon, 30 Apr 2007 22:52:16 GMT

Last week I was searching for a solution to generate a complicated XML straight away out of the SQL Server. Actually, I had huge data scattered in few tables that I wanted to map to an XML with several attributes n elements with multiples levels of hierarchy, I was trying to generate this XML with the FOR XML Explicit mode. Doing things with this was really really complicated n terrible. So, I started to look around for new XML features introduced with SQL Server 2005 (as I was using SQL Server 2005 for this).

While browsing through MSDN pages I came around this new mode 'FOR XML PATH' introduced with SQL Server 2005. This is really a cool feature and gives you the ability to generate the complex XML with a greater ease, with this feature I was safe home and was able to generate the XML in desired format with a great ease. Thank God!!!

The new PATH mode allows you to use an XPath-like syntax as a column name which then is mapped into an attribute or element or sub element of the XML as per you specify and you can have multiple level of hierarchy and can mix elements n attributes. The problem with FOR XML AUTO n RAW modes with SQL Server 2000 was that you can have either the attributes or the elements but not both. If you want to have mixed content in XML you have to go for XML Explicit mode which was too complex n for generating hierarchy you have to Union lot of SQL Statements so, it sometimes, used to correctly called as the "query from hell".

With the Introduction of the FOR XML Path Mode things can be as easy as following:

SELECT CustomerID as "@CustomerID",
           CompanyName,
           Address as "address/street",
           City as "address/city",
           Region as "address/region",
           PostalCode as "address/zip",
           Country as "address/country",
           ContactName as "contact/name",
           ContactTitle as "contact/title",
           Phone as "contact/phone",
           Fax as "contact/fax"
FROM Customers
FOR XML PATH('Customer')

This will give you the following result:

<Customer CustomerID="ALFKI">
    <CompanyName>Alfreds Futterkiste</CompanyName>
    <address>
      <street>Obere Str. 57</street>
      <city>Berlin</city>
      <zip>12209</zip>
      <country>Germany</country>
    </address>
    <contact>
      <name>Maria Anders</name>
      <title>Sales Representative</title>
      <phone>030-0074321</phone>
      <fax>030-0076545</fax>
    </contact>
</Customer>
...

If you analyze down the results you can figure out that the column you specify as "@" are resulted in attributes e.g. customer ID, the column with no alias is resulted in element e.g. company name. Now the columns which you want to be embedded in some tag can be also generated using syntax 'element1/element2' e.g. for city column we have specified 'address/city'. It is this simple to generate the mixed kind of XML with this new mode.

There's a lot to it, lot many features and options available, for complete details visit the following links:

http://msdn2.microsoft.com/en-us/library/ms345137.aspx

http://sqljunkies.com/WebLog/mrys/archive/2004/02/19/1208.aspx

Hope you get benefited with this.

Upcoming Webinar on SOA

Sachin R. Chavan — Sun, 04 Mar 2007 13:33:00 GMT

Title: Recovering Lost Knowledge to Enable SOA

Date: Thursday, March 15, 2007

Time: 2:00 PM Eastern / 11 AM Pacific

Duration: 1 hour

Join Phil Murphy of Forrester Research and Rick Malek of Microsoft Consulting Services live as they share their insights and experience on what it takes to modernize legacy systems.

Attend this Webinar and find out how IT professionals can build a successful strategy to modernize legacy systems and implement SOA despite shrinking pools of support personnel, increasing maintenance costs, lower IT budgets, and the need to share applications across the enterprise.

About the speakers

Mr. Murphy, Principal Analyst at Forrester Research has more than 25 years of practical IT experience and currently advises CIO's and senior IT management on application rationalization, Application Portfolio Management, Legacy Modernization and Migration.

Mr. Malek of Microsoft Consulting Services has spent the majority of his career migrating Mainframe Development Environments to the Windows Server platform. At Microsoft he defines services and products to enable a smooth and efficient transition of the mainframe environments.

Click following link to register:

http://www.1105info.com/gpkppzq_nzruuuh.html

BizTalk 24x7

Sachin R. Chavan — Sun, 18 Feb 2007 20:54:00 GMT

Hey Guys ever image of one central place where you can find most of the information on BizTalk. Right from the White Papers, Code Samples, WebCasts, Technical Articles, Tools & lot more....

Here is the solution to all your worries, Saravana Kumar a Microsoft Biztalk Server enthusiast has floated a site totally dedicated to BizTalk which carries all above items. Your One Stop Shop for BizTalk related queries.

No Matter you are a Novice or a Pro I am damn sure you will definitely find here something useful for you.

Add the following URL to your Favorites Today:

http://www.biztalk247.com/v1/ContentTransformer.aspx?tag=EED032CC-B293-11DB-94BF-2CB755D89593

Visit The Site Today.

GR8 Job. Hats off to Saravana Kumar.

Vista Launch Coverage.

Sachin R. Chavan — Tue, 30 Jan 2007 22:33:00 GMT

Interested in Vista Launch Video's?

Visit the following link to watch the Vista Launch Video's:

http://geekswithblogs.net/scottkuhl/archive/2007/01/30/104931.aspx

Visual Studio Code Name "Orcas" - January 2007 Community Technology Preview (CTP)

Sachin R. Chavan — Tue, 30 Jan 2007 22:14:00 GMT

Microsoft Visual Studio Code Name "Orcas" is the next generation development tool for Windows Vista, the 2007 Office System, and the Web.

Visual Studio code name “Orcas” delivers on Microsoft’s vision of smart client applications by enabling developers to rapidly create connected applications that deliver the highest quality rich user experiences. This new version enables any size organization to rapidly create more secure, manageable, and more reliable applications that take advantage of Windows Vista and the 2007 Office System. By building these new types of applications, organizations will find it easier than ever before to capture and analyze information so that they can make effective business decisions.

This download is the January 2007 Community Technology Preview of Microsoft Visual Studio Code-Named “Orcas”. This CTP is available in English only.

Download it here:

http://www.microsoft.com/downloads/details.aspx?familyid=1ff0b35d-0c4a-40b4-915a-5331e11c39e6&displaylang=en

BizTalk Server 2006 R2 Key Features

Sachin R. Chavan — Fri, 19 Jan 2007 22:56:00 GMT

Interested in knowing up coming features in BizTalk Sever 2006 R2, you may find following information useful

Ø BizTalk Server 2006 R2 builds on BizTalk Server 2006’s business process management capabilities by extending a company’s core process management technologies outside of the corporate boundaries, and into supply-chain and trading partner scenarios.

Ø BizTalk Server 2006 R2 will include new capabilities that address core challenges found in key vertical industries such as manufacturing and retail. These capabilities include native support for Electronic Data Interchange (EDI) and AS2 as well as RFID.

· BizTalk RFID provides a new set of technologies for developing and managing radio frequency identification (RFID) solutions

· BizTalk RFID will reduce the complexity associated with the management of intelligent RFID device hardware by connecting real-time RFID events to relevant supply-chain processes and back-end business applications.

· BizTalk Server 2006 R2 will include native support for EDI and the AS2 protocol which will enable users to efficiently connect their supply chains to key suppliers and trading partners.

Ø BizTalk Server 2006 R2 will feature close alignment with the upcoming releases of 2007 Microsoft Office system and Windows Vista, including key WinFX technologies such as Windows Workflow Foundation and Windows Communication Foundation. This alignment will help customers readily access LOB data, as well as system-to-system or supply-chain processes from within their Office System applications (like SharePoint Server, InfoPath, etc).

· Platform alignment improvements in BizTalk Server 2006 R2 will enable .NET developers using the WinFX frameworks to build next-generation applications

· BizTalk Server 2006 R2 leverages customers’ existing investments in LOB applications and legacy systems and exposes them as WCF web services

BizTalk Server 2006 R2 will include an Adapter Framework to enable developers to write custom adapters for any line of business application and expose the underlying information and data in other Microsoft applications

BizTalk Server 2006 BAM Functionality

Top Level Talking Points

· Microsoft’s BizTalk Server Business Activity Monitoring (BAM) provides users with an end-to-end view of business processes across applications and trading partners. Through this visibility, organizations receive a unified view of the health of business processes by correlating business events and information from multiple applications.

· Because of the increased enhancements in the BAM functionality included in BizTalk Server 2006, business users have the ability to detect problems before they occur. This allows them to define thresholds for the key performance indicators for their business and set alerts to notify them when these thresholds are exceeded.

· A goal of BizTalk Server 2006 is to empower business users and enable them to make educated decisions about their business. This is accomplished through providing real-time visibility and alerts into their business processes. Upon receiving the Alert, Business Users can then analyze the state of the business process and make an educated decision on resolution. They can even make a request for technical support when needed.

· BAM leverages and extends Microsoft Business Intelligence technologies

BizTalk Server 2006 Deployment Improvements

Top Level Talking Points

· Based on customer feedback, Microsoft has simplified the deployment of BizTalk Server 2006.

· The new deployment model moves away from a more complicated Hub and Spoke deployment process, where organizations were required to run separate deployments of BizTalk Server for EAI and B2B integration. With the developer deployment tool included in BizTalk Server 2006, organizations are provided with a single location for developers to deploy and test their BizTalk Server solutions quickly.

· BizTalk Server 2006 features a consolidated set of tools for deployment and management, reducing the number of tools from 8 in BizTalk Server 2004 to 3 in BizTalk Server 2006.

Windows Workflow Foundation

Top Level Talking Points

· Windows Workflow Foundation will provide a single workflow technology as part of the Windows Platform. It will consist of a .NET Framework API, an in-process workflow engine, and designers for Visual Studio 2005 for quickly building applications on Windows.

· Windows Workflow Foundation can be used by application developers in scenarios that require workflow capabilities be built into line of business applications. Examples of such scenarios include: user interface page-flow, document-centric workflow, human workflow, composite workflow for service oriented applications, business rule driven workflow and workflow for systems management.

· Windows Workflow Foundation will be the framework for workflow enabling Microsoft’s products including future versions of Microsoft Office products, code-named Office “12”, BizTalk Server, and Microsoft Dynamics.

Microsoft SOA Solution Center

Sachin R. Chavan — Sat, 06 Jan 2007 13:39:00 GMT

Interested in knowing more about Microsoft SOA Solution. Visit the Microsoft SOA Solution Center at following URL. You can find lots of technical resources like Case Studies, White Papers, Articles & Webcasts for Developers and Architects.

Microsoft SOA Solution Center: http://www.microsoft.com/biztalk/solutions/soa/default.mspx

XML Notepad 2007

Sachin R. Chavan — Sun, 31 Dec 2006 21:37:00 GMT

Today I was searching for a XML Tool and came across this on google. This is a good XML editing tool shipping from Microsoft.

You can download and browse the feature list here:

http://www.microsoft.com/downloads/details.aspx?FamilyID=72D6AA49-787D-4118-BA5F-4F30FE913628&displaylang=en

BizTalk 2006 Samples - Available on MSDN Web site.

Sachin R. Chavan — Sat, 30 Dec 2006 11:44:00 GMT

Now available BizTalk Server 2006 Samples with new additions visit the following MSDN Page for complete details. Perfect place to know more about BizTalk Server 2006.

http://msdn2.microsoft.com/en-us/biztalk/aa937647.aspx

Just Released! Enterprise Library 3.0 - December 2006 CTP (Community Technology Preview)

Sachin R. Chavan — Sat, 30 Dec 2006 11:15:00 GMT

The CTP doesn't have a lot of documentation, but there is a reasonably detailed Release Notes document explaining what's new. Here's a quick summary of what you can expect:

Core

Source Code installer
Partial Trust Support
Strong-Named Binary Assemblies

Validation Application Block

Core validation API
Minimal Validator Library
Attaching validators to objects via attributes
Attaching validators to objects via configuration
Not included yet (but in the works): Configuration tool support, complete validator library, integration with ASP.NET, Windows Forms, WCF etc.

Data Access Application Block

SQL Server Compact Edition Support
New Database.UpdateDatabase overload with updateBatchSize parameter

Configuration Tool

Visual Studio IDE integration
AppSettings support
Encryption support

Application Block Software Factory

Templates and recipes for creating application blocks and provider libraries
Preliminary documentation

Strong Naming Guidance Package

Recipes to assist in strong-naming and updating [InternalsVisibleTo] across multiple projects

You can Find the same here

NOTE: This is a CTP (Community Technology Preview) which means there are going to be things that don't work properly it is strongly recommend not to use it in production applications.

Cheers!!!

BizTalk 2006 R2 Windows Communication Foundation Adapter Video

Sachin R. Chavan — Wed, 27 Dec 2006 16:07:00 GMT

A short video that walks through how simple it is to expose a simple Orchestration as a Windows Communication Foundation (WCF) Services.

This video uses the BasicHTTP Binding and the WCF Wizard.

This video gives you a quick look into some of the new features in BizTalk 2006 R2 relating to WCF.

The video as well as code is avialable for download. Take a look at the video today!