SSIS – Use timestamp to detect changes

April 19, 2016 Leave a comment

Last week, a solution was required to build an SSIS solution that can pick up all changes from a database using the versionnumber (timestamp) and sync the changes to an external database using the timestamp of the database. This is quite easy to achieve by:

  1. Step 1, retrieve the previous timestamp. Storing the previous timestamp can be easily achieved by create a file using File.WriteAllText method.

 Dim runLastFile = System.IO.Path.Combine(currentPath, “LastRun.txt”)     System.IO.File.WriteAllText(runLastFile, lastRun)

I know it’s in VB but you can easily convert it to C#, since it’s an old solution i didn’t bother to convert it. If the file is not yet existing, you need to create a logic to get the lowest timestamp. Like this:

declare
@minVersion binary(10),
@minVersion_str nvarchar(42)

SET @minVersion = ( SELECT MIN(timestamp fieldName) FROM {sourceTable} )
SET @minVersion_str = upper(sys.fn_varbintohexstr(@minVersion))
SELECT @minVersion_str as LowestVersion

2. Step 2, get the current timestamp from the source database by using MIN_ACTIVE_ROWVERSION, the challenge however is to store this as string/text so the SQL query can be constructed later on in the process. After 15 minutes of googling I was able to find it somewhere (credit to the forum, lost the link).

declare
@currVersion binary(10),
@currVersion_str nvarchar(42)

SET @currVersion = MIN_ACTIVE_ROWVERSION()
SET @currVersion_str = upper(sys.fn_varbintohexstr(@currVersion))
SELECT @currVersion_str as CurrentVersion

3. Step 3, construct the SQL query using the previous timestamp. Something like this:

                         SELECT {sourceColumn}
FROM {sourceTable}
WHERE {timestamp fieldName}  >= @previousTimeStamp

4. Step 4, if the process is successful don’t forget to store the current timestamp.

5. Step 5, start from step 1.

Categories: Microsoft SQL Server Tags: ,

Key to successful BizTalk Deployment

January 25, 2015 Leave a comment

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/

Visual Studio 2012 – Install Shield LE Basic Tutorial

Starting Visual Studio 2012, the Setup project for Visual Studio is not supported anymore and the only free alternative is to use the Install Shield Limited Edition (LE).

In this post, I will describes the steps to successfully:

A. Deploy a project to GAC (Global Assembly Cache)

B  Generate/pacakage Single MSI for installation

 

Steps:

1. Start by downloading the Install Shield here. And installing it.

2. Next is to create a new install shield limited edition project.

3. Follow the wizard, and in the step 2, right click the destination computer and select [GlobalAssemblyCache]

4. Drag and drop the Primary output from the Solution (top pane).

5. Go to every item in the [GlobalAssemblyCache], right click Dependencies from scan at build. Be sure to unselect other reference assemblies so it will not be registered in GAC.

Single MSI Generation:

6. Now to create a single MSI setup, Go to step 6 (Releases), Select the Single Image. In the build tab, Set compression = compressed, and on the Setup.exe tab set the Setup Launcher to No.

7. Right click the Solution, then select Configuration Manager, in the InstallShield project set the configuration to Single Image. Then Right click Install Shield project – > Build.

8. Go to Install Shield Project – > Express – > Single Image – > DiskImages -> Disk1. Viola. the MSI can be found there.

 

 

 

ESB Toolkit overrides BizTalk config (BTSNTSvc.exe.config)

February 17, 2014 1 comment

I’ve setup a BizTalk Server 2013 environment with ESB Toolkit 2.2 and since I use Enterprise Library for logging I also updated the BTSNTSvc64.exe.config file, but when I tried to run a sample application it throws an error:

Activation error occured while trying to get instance of type LogWriter, key “”:Microsoft.Practices.ServiceLocation.ActivationException: Activation error occured while trying to get instance of type LogWriter, key “” —> Microsoft.Practices.Unity.ResolutionFailedException: Resolution of the dependency failed, type = “Microsoft.Practices.EnterpriseLibrary.Logging.LogWriter”, name = “(none)”.

Exception occurred while: while resolving.

Exception is: InvalidOperationException – The type LogWriter cannot be constructed. You must configure the container to supply this value.

Apparently, ESBToolkit is overriding the BizTalk configuration.

Solution:

Move the configuration to <ESB Toolkit Installation Folder>\esb.config file.

Categories: BizTalk Server

BizTalk: How to fix regional settings problem (decimal number formatting)

September 3, 2012 Leave a comment

The regional settings of the operating system wherein your BizTalk Server is hosted, affects how the numbers/date are formatted. This in some cases, becomes a problem when trying to insert data in SQL Server using WCF-SQL.

For instance if the server is setup to use Dutch regional settings, the decimal number is separated by (,) comma. When you try to insert a data for instance 10.001, it becomes 10001. To fix the problem, you need to change the regional settings of the service account wherein the host instance is running.

Basically there are two things you can do.

One is to logon the account -> Go to regional settings -> Change the number formatting.

Now, if it’s impossible to login the account, you can fix it by changing the registry settings, for this you need the SID of the user account.

Updating Regional settings of an account using registry:

1. Open Regedit.

2. To get the SID, navigate to HKLM\SOFTWARE\Microsoft\Windows NT\ProfileList

Browse to every folder below and using the value in ProfileImagePath, you can determine the correct user account.

Then use the SId of the folder.

3. Once you have the GUID, browse to: HKEY_USERS\<SID in step 2>\Control Panel\International

Change the string values, for Decimal, change the sDecimal value.

 

BTDF: System.OutOfMemoryException while deploying using Powershell Remoting

July 17, 2012 Leave a comment

Recently when I tried to deploy a BizTalk solution packaged with BTDF (BizTalk Deployment Framework) in combination with Powershell Remoting I encountered this error:

Error: Failed to add resource(s).

Change requests failed for some resources.

BizTalkAssemblyResourceManager failed to complete end type change request.

Exception of type ‘System.OutOfMemoryException’ was thrown.

 

I checked the memory on the remote server and even restarted it but it didn’t help, I still encounter this error.

As it turns out, as well as I’ve suspected it’s the memory of the powershell. To increase the size of the memory, run the PowerShell Command prompt on the target server and enter the ff:

Set-Item WSMan:\localhost\Shell\MaxMemoryPerShellMB 512

Or set it even to higher.

 

 

NFS: How to connect to NFS using Windows Server 2008 R2 without using User Mapping Server

June 29, 2012 6 comments

When connecting to NFS shared folder the windows credentials needs to be mapped to a equivalent unix account+ group. 

In Windows Server 2008 R2 the support for User Mapping is dropped and the same functionality can only be achived using Identity Management for Unix Components (extension schema for Active Directory).

Below describes on how you can connect to a NFS folder without using User Mapping Server.

A. Install NFS Client

Step 1. Enable File Services Role. Go to Server Management – > Add Roles -> File Services

Step 2. Install Services for Network File System. Go to File Services – > Add Role Services

 

B. Update NFS Client Registry

In this step, we are going to map the anonymous user credential to the unix account credential that you’ll be using to connect to NFS share. First you need to get the User Id and Group Id of the unix account from the unix administrator. It should be of decimal value like: UserId= 6500000 GroupId=4200. Once you have it, we can proceed.

1. Open Regedit.

2. Go to \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ClientForNFS\CurrentVersion\Default.

3. Create 2 DWORD value, one for AnonymousUid with decimal value=<User Id> and another for AnonymousGid with decimal value=<GroupId>.

It should look like this:

4.  Restart the NFS Client. Go to Administrative Tools -> Services for Network File System (NFS) ->

C. Test NFS Connection

1. Open command prompt.

2. Type:  mount -u:<UserName and not UserId> -p:<Password> <SharedNFSFolder> <drive letter to mount, Ex: J:>

3. dir <drive letter:>

Copy file to this NFS folder. This is only way to confirm that the registry hacking is successfully. Because by default if the anonymous access is turned on in NFS side, you can see the files without having to supply user/password.

Note: Limitation is that, you can only connect to a single NFS share because it would use the same UserId and Group Id everytime you connect.

Categories: Windows Server Tags: ,
%d bloggers like this: