Tag Archives: Best Practices & Patterns

randypaulo 8:27 pm on January 25, 2015
Tags: , Best Practices & Patterns, , , TFS   

Key to successful BizTalk Deployment

It’s been a while since my last blog, but IMHO this is one of important blog that I’ve ever made that can be useful pointer for every aspiring developer / companies that will/already implement application integration. Below contains a list / summary of best practices I’ve acquired in my more or less 10 years working with BizTalk, SSIS, WCF and .NET integration (primarily Microsoft Technology) but this I think is also applicable to other technology.

Design / Development:

#1 K.I.S.S (Keep it simple, stupid). Don’t get me wrong, I like complex integrations, from  multiple message correlations, requirement to aggregate certain line items with certain condition in the message to having a direct binding with delivery notification set to transmitted (multiple send port / send port groups, this will result in some zombie messages). Anyone from novice to expert BizTalk Developer can build a solution, it can be done & solve  in different ways but the key here is providing a simple and effective solution for a complex integration.

#2 Isolation with maximum reusability. Isolation for me means that the application can be deployed without having too much dependency to other BizTalk artifacts that are shared. During the design it’s always a struggle on how to seperate artifacts, on which solution / projects it should be placed to avoid having to uninstall all referencing application before you can refresh the dependency (GACing works in some situation but not for all, you’ll be surprise how BizTalk deal with it internally). Once identifying or isolating a certain integration flow whenever there’s a new requirement, we should always check whether it fits on already existing solution (extend) without having to build a new integration flow. in this case we will have maximum reuse of existing solution.

#3 Consistency. Every solution that will be build should look as if it’s build / coded by one person. Having a naming convention on BizTalk artifacts, namespace, solutions, project & folder structures should be define first. Based on experience providing a proper name is always a challenge since if you define it incorrectly, it will be a costly mistake later on.

#4 Nuget – all common code / components / libraries should be referenced from a local nuget repository. Having a local nuget repository not only simplifies referencing dependencies but also saves you once you start using TFS Build Server (autorestore feature). This means that shared assemblies is not required to be checked in together with the source codes.

#5 .NET over XLST in complex mapping –  Whenever there’s a requirement for complex mapping like grouping, aggregation, unique numbering over a certain combinations or constructing a hashtable in the mapping, I always go for .NET component. Using .NET component it is easier to maintain and tested (unit testing + mockup) vs doing everything in XSLT (in which can be forgotten in time).

Build / Deployment:

#1 Release Pipeline – It’s important to define strategy on how you would build and be able to do ‘Build once’ and deploy to multiple environments. In our case, part of our strategy is to minimize branching and always build from MAIN. In TFS, we’ve implemented a Gated Checkin which means that every checkin will trigger a release pipeline. During the checkin, a build will be triggerred (MSBuild and BTDF build), it will run all unit test and copies the binaries to drop folder (only If all steps are successful). From this point the Ops can just enter a command (application name) and environment and then the automated deployment will take over..

#2 Configuration over Code – for normal deployment most of time you would see that a solution contains a powershell script for deployment, this is not bad but i think this only works on sample codes or tutorials but a problem in Enterprise deployment. For this you need a good deployment framework (compilation of scripts) to do the deployment and during the deployment preparation just replace the values in the templates like servernames, shared folders and etc.  I’ve posted some blogs earlier how to do this:

Centralize Powershell Repository:

https://randypaulo.wordpress.com/2012/02/27/powershell-centralize-powershell-script-modules-repository/

randypaulo 12:04 pm on May 29, 2012
Tags: Best Practices & Patterns,   

BizTalk Property Promotion : MessageDataPropertyBase vs MessageContextPropertyBase

When adding a property schema that can either be used for content-based routing (CBR) or just to hold metadata information about a message in BizTalk we are confronted which Property Schema Base to use:

1. MessageDataPropertyBase
2. MessageContextPropertyBase or
3. PartContextPropertyBase

Answer is quite simple:

If the promoted property can be found in the source (incoming) message you need to use MessageDataPropertyBase.

If the property can be set in envelop, pipeline or manually promoted in the orchestration using Correlation Sets then MessageContextPropertyBase is used.  This is also a useful technique if we want to maintain the original structure of the message and at the same time inject some properties that can be used in tracking or routing.

Lastly, suppose if I have a source message without a namespace and I need to map it to my internal schema with promoted properties how will I do it?

1. Create a custom receive pipeline with Set Namespace in Component in Decode Stage. Add an XML Disassembler with schema equals to the source schema with the expected namespace.

2. Add a Map, map source to internal schema (this should contain attribute/fields that will be promoted).

3. Add a Property Schema, question is, which Property Schema based to use?

4. Update internal schema to map the fields to promoted properties.

Note: I used  MessageContextPropertyBase in Step # 3 and it works.

randypaulo 10:38 am on January 6, 2012
Tags: Best Practices & Patterns, , , Gothas, , RFC/BAPI   

BizTalk Best Practices | Tip and Tricks

Below is a list that needs to be remembered when developing BizTalk solutions.

BizTalk Orchestration:

XPath Expressions: 1. To do an XPath count:


countVariable = System.Convert.ToInt32(xpath(yourMsg, "count({XPATH Instance path,can be copied from Schema})"));

2. To extract a value using XPath (string):

stringVar = xpath(yourMessage, "string({XPATH Instance path,can be copied from Schema})")

BizTalk WCF-SQL Adapter:

When confronted with the problem about MSDTC issue, either due to cross domain or the actual MSDTC being disabled this adapter will still work by just setting the useAmbientConnection to FALSE

BizTalk mySAP Adapter:

1. RFC/BAPI function not returning any result.

When expecting a response from SAP via RFC or BAPI you always need to supply an empty node wherein you’re expecting a result. For example if the field is under the T_QAIVCTAB, in the mapping you need to generate this field by mapping a scripting functoid that returns empty string.

2. Set EnableSafeTyping to True

To avoid nasty data type issue when sending/receiving a response set this value to true in your binding. Data type error: An error occurred when trying to convert the byte array [30-00-30-00-30-00-30-00-30-00-30-00-30-00-30-00] of RFCTYPE RFCTYPE_DATE with length 8 and decimals 0 to XML format. Parameter/field name: Error message: Year, Month, and Day parameters describe an un-representable DateTime. —> System.ArgumentOutOfRangeException: Year, Month, and Day parameters describe an un-representable DateTime. at System.DateTime.DateToTicks(Int32 year, Int32 month, Int32 day) Configuration: Schema Generation: During the schema generation (WCF Consume Adapter Service) also set the Enable Safe Typing to TRUE.

3. BAPI_SALESORDER_SIMULATE.

– Be sure to pad the material number with leading zeroes up to length of 18.

public string PadMaterialNumber(string s)
{
string val = s.PadLeft(18, ‘0’);
return val;
}

BizTalk Mapping:

Microsoft.XLANGs.Core.XTransformationFailureException: Error encountered while executing the transform. Error:Transformation failed.. —> System.Xml.XPath.XPathException: Function ‘userCSharp:ConvertToDecimal()’ has failed. —> System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> System.FormatException: Input string was not in a correct format.

1. Be careful in data conversion inside the mapping especially when using Scripting Functiod to convert values. One prime example is when you use the Convert.ToDecimal method. If you use this method and the value contains scientific notation ( 3.71615996396169E-02) it will throw an exception. The correct way of doing it is by using the Decimal.Parse method.

decimal d = Decimal.Parse("8.71615996396169E-02", System.Globalization.NumberStyles.Float);

SAP Schema Generation Error:

Error Message:

Error while retrieving or generating the WSDL. Adapter message: Details: ErrorCode=RFC_EXCEPTION. ErrorGroup=RFC_ERROR_APPLICATION_EXCEPTION. SapErrorMessage=SEGMENT_UNKNOWN.  AdapterErrorMessage=Error returned by RfcCallReceiveEx while calling RFC: IDOCTYPE_READ_COMPLETE..

Cause:

Idoc segment in SAP is not set to Released. Ask the SAP Team to check whether all segments are released.

BizTalk Deployment

Use BizTalk Deployment Framework (BTDF) to simplify deployment. BTDF can be downloaded from here. BTDF quick tutorial: https://randypaulo.wordpress.com/2012/01/13/biztalk-deployment-framework-btdf-tutorial-walkthrough/ Automating/Silent Install of BTDF using powershell here:  https://randypaulo.wordpress.com/2012/01/31/automating-silent-install-biztalk-deployment-framework-btdf-using-powershell/

randypaulo 10:53 am on December 2, 2011
Tags: Best Practices & Patterns,   

SSIS – Centralize Connection Repository

Maintaining a central repository for all connection information for SSIS packages is important especially if you have multiple packages that connects to one or more different systems. Keeping track and performing changes on the connections will be hard if not done properly.

This blog entry will describe on how to use SQL Configuration Type to store the connection info in combination with the traditional File ConfigurationType (dtsConfig) to store package specific configurations.

1. Setting up the configuration store

A table with definition below needs to be created on an new/existing database, this will store the connection information.

SSIS Configurations
Column Name Type Description
ConfigurationFilter

Nvarchar(255)

 This field is used by SSIS to indentify a set of property/values pairs that are part of the same configuration entry in the Package Configurations Organizer.
ConfiguredValue

Nvarchar(255)

 It stores the value that is used to update the package property specified in PackagePath column
PackagePath

Nvarchar(255)

 The path that point to the property being configured
ConfiguredValueType

Nvarchar(255)

 the SSIS data type of the property being configured

2. Environment Variable

In this step, we would create an environment variable which will store the connectionString information to the configuration store (see step1).

1. On the Start menu, click Control Panel.

2. In Control Panel, double-click System.

3. In the System Properties dialog box, click the Advanced tab, and then click Environment Variables.

Figure 1 – System Properties

4. In the Environment Variables dialog box, in System variables frame, click New.

In the New System Variables dialog box, type SSISConfiguration in the Variable name box, and Data Source={DatabaseServer};Initial Catalog={DatabaseName};Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False; in the Variable value box.

5. Click OK.

6. Click OK to exit the System Properties dialog box.

3. SSIS Package Settings

It is assumed that the package is already in the final phase of development and the only thing needs to be done is to update it to retrieve settings from the configuration store and use it during runtime.

 3.1 Add Configuration Store connection

Since we are going to retrieve connection settings from configuration store that is a table we need to define a new OLE DB connection that does that.

1. In the Connection Managers, right click New OLE DB Connection.

2. Click New, Set the valid credentials to connect then on the Connect to a database server(this is the connection to cofiguration store) , Select <{Database}>.

3. In the Connection Managers rename the added connection to OLEDB_SSISConfiguration.

 3.2 Configure Configuration Store Connection

In the following steps we are going update the connection to configuration store to use the environment variable we defined step (3.1)

1. Click SSIS Menu, Package Configurations. Package Configurations Organizer opens.

2. Click Enable package configurations.

3. Click Add, Click Next.

4. In the Configuration Type, Select Environment variable.

In the Environment variable drop-down, Select SSISConfiguration.

Click Next.

Note:
If the SSISConfiguration is not on the list try re-opening the BIDS (Business Intelligence Development Studio)

5. In the Select Property Page in the Connection Managers,

Click OLEDB_SSISConfiguration,

Click Properties, Select ConnectionString.

Click Next.

6. Type OLEDB_SSISConfiguration in the Configuration name and click Finish.

7. It should look like this:

3.3 Configure existing connection to use configuration store.

 After the connection to configuration store is properly setup the next step is to update existing connections in the package to retrieve settings from it.

Important:
Repeat the steps per connectionin the Package. If the package has connection to 2 database, the steps below needs to be repeated 2x.Example below illustrates an SSIS package that has connection to a database: SampleDB.

1. Click SSIS Menu, Package Configurations.

Package Configurations Organizer opens.

2. Click Add, Click Next.

3. In the configuration Type, Select SQL Server.

In the Connection, Select OLEDB_SSISConfiguration

In the Configuration Table, Select [dbo].[SSISConfigurations]

In the Configuration Filter, Set it to the <name of the connection>,

Ex. OLEDB_SampleDB.

Click Next.

4. In the Connection Managers, Select the connection (name of collection in step 3), Properties, Select ConnectionString.

Click Next.

Important:
Select only one ConnectionString in this step since the entire step will be repeated per connection. This makes sure that the settings defined in configuration store can be reused in other packages.

5. In the Configuration Name, type the name of connection (in step 3). Click Finished.

6. Repeat steps for other connections (if applicable).

7. Update the SSISConfiguration Table if SQL Login is used since SSIS doesn’t save sensitive information such as password.

Note:
Set the connection manager to DelayValidation=TRUE if during the execution the validation fails. For some reason, it uses the value in the package and not the values from the SQL Configuration store (Database).
If you encountered this error:“Cannot insert configuration information into the configuration table. (Microsoft.DataTransformationServices.Wizards)   Could not complete wizard actions.”

It means that one of the properties exceed the length limit of the SSIS configuration table, normally it’s the connection string that causes the problem. Try to shortened the connection string by truncating the Application Name property or try to giving it a short name.
randypaulo 2:57 pm on June 27, 2011
Tags: Best Practices & Patterns, Naming Convention, ,   

SSIS Tips & Tricks Best Practice

I’ve been developing SSIS for quite sometime now and below are some tips and tricks I’ve used to make solution to be more consistent, fast and efficient.

1. Shape names (tranform shapes), use prefix, I use a 3 letter acronym. For ex. Execute SQL Task name it like EST Update Status.

2. Do use  Sequence Container to group related task/shapes.

3. Variables 

3.1 Limit the scope, create the variables closer to the shape that would use it.

3.2 Use Pascal casing for variables that will be configurable in dtsConfig and camel casing for the rest. This will make your life easier, by the time you’re configuring the dtsConfig it will be easy to identify which ones are to be included.

3.3 Variable access – instead of manually adding ReadOnlyVariables and ReadWrite Variables on the Script Task, you can use the script below to access and set the values of the variables without having to encounter the nasty variable is locked error.

Private Function GetValue(ByVal variableName As String) As Object
Dim var As Variables
Dim objVal As Object
Dim varName As String = "User::" & variableName
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(var)
objVal = var(varName).Value
var.Unlock()
GetValue = objVal
End Function

Private Sub SetValue(ByVal variableName As String, ByVal value As Object)
Dim var As Variables
Dim varName As String = "User::" & variableName
Dts.VariableDispenser.LockForWrite(varName)
Dts.VariableDispenser.GetVariables(var)
var(varName).Value = value
var.Unlock()
End Sub
End Class

4. Error Handling –  Use precedance constraint to handle failure instead of using Event Handlers.  The reason for this is that, it’s more simple, clean and easy to spot. 🙂 Also, sometimes using event handler will give you weird cryptic error that is both waste of time and unnerving.

5. Package properties–  Set the Protectionlevel to DontSaveSensitive and SuppressConfigurationWarning to True.

Setting the protection level will save you from:

Failed to decrypt protected XML node “DTS:Password” with error 0x8009000B

this is assuming that you are using Integrated Security or will going to use SQL Configuration Table see Item# 8.

SuppressConfigurationWarning, just like what the property indicates. This maybe because the location of dtsConfig in your production is different from your virtual or dev machine.

6. Bulk Update – You might be tempted to use a OLEDB Command to perform an update, but this is the slowest possible way since it means that this command will be executed multiple times based on the total number of rows.

Faster way is to create a temporay table on the target database, populate that table then do an execute sql task performs the update. Aside from the normal db_datareader/writer permission you’ll need a db_ddladmin permission to drop and create tables.

UPDATE d
d.Column1 = t.Column1,
d.Column2 = t.Column2
FROM DestinationTable d
INNER JOIN TmpTable t ON (d.Key = t.Key)

7. For parallel data processing you can use the Balanced Data Distributor for SSIS, this component can be downloaded seperately from
Microsoft.

For the quick demo click here.

To download click here

8. If the number of SSIS projects will grow overtime especially when connecting to different sources maintaining the connectionString will be hard if not centralized. To implement centralize configuration for SSIS see this article:  https://randypaulo.wordpress.com/2011/12/02/ssis-centralize-connection-repository/

Lastly, make sure that connecting lines between task is straight :), it just look more professional and nice if it is.

Happy coding..