SSIS: Using UniData ODBC Driver on Windows Server 2003 x64

Problem
IBM does not provide a 64-bit ODBC driver for UniVerse or UniData so although you can install the driver on a 64-bit Windows Client PC, you cannot see it in the ODBC driver's list in ODBC administration in the control panel.

Cause
IBM don't supply a 64-bit version of the ODBC driver for UniVerse or UniData

Solution
The ODBC administrator accessible from Control Panel on a 64-bit Windows machine is the 64-bit version, so the UniVerse ODBC driver will not show up in the list of the available drivers.
You can still use the 32-bit version of the driver on a 64-bit version of Windows, you just need to make sure that you use the 32-bit ODBC administration console rather than the 64-bit one when dealing with the DSN.


64-bit Windows has the familiar: C:\Windows\System32 directory.

it also has a C:\Windows\SysWOW64 directory that serves a similar function as a repository
for system files.


The assumption that most people would make is that the 32-bit system files would go in the System32 directory and the 64-bit system files would go in the SysWOW64 directory. However, this is not the way it works.


Some things in 64-bit Windows are not exactly as they appear on the surface:.

The\windows\system32\odbcad32.exe program is really the 64-bit ODBC Administrator, and the \windows\SysWOW64\odbcad32.exe is the 32-bit ODBC Administrator.

If you use the odbcad32.exe in the SysWOW64 folder you will be able to define DSNs with our 32bit driver.

Reference taken at : IBM Support

Additional Steps

After adding the UniData ODBC Driver and realize the package unable to run, the next step is the solution to run the package successfully.

In your Integration Services Project, go to
Project>[Project Name] Properties...



Go to Configuration Properties Treeview > Debugging, change Run64BitRunTime at Debug Option Section and change value to 'False'



The reason why we need to set the Run64BitRunTime to 'False' as the UniData ODBC Driver was built on 32 bit development. If the Run64BitRunTime set to 'True' then the package would have error due to missing driver. The UniData ODBC Driver is stored in 32 bit Data Source (ODBC therefore its required to set the Run64BitRunTime to 'False' in order to detect and run the package successfully.

SSIS: Connect Using IBM UniData ODBC Driver

Straight to the point on how to create connection using IBM UniData ODBC Driver in SQL Server Integration Services 2005/2008. This driver allow connection from IBM UniData to SQL Server where SQL Server Integration Services as the intermediaries. I assume that UniDataODBC and Uni Call Interface Configuration Editor (UCI Editor) from UniData 7.1 Client installation has been installed.

Add ODBC Data Source, Control Panel>Administrative Tools>Data Source (ODBC)

Setup the Data Source Name, Database and User base on your IBM database configuration. As for the Server dropdownlist, the list is available base on the configuration at Uni Call Interface Configuration Editor (UCI Editor)

Create or open Integration Services Project then on Connection Managers tab, right click to add a new connection manager.



Select ODBC Connection Manager Type



Basically that is all needed to setup the connection using IBM UniData ODBC Driver. If using Windows Server 2003 x64 then view my post on SSIS: Using UniData ODBC Driver on Windows Server 2003 x64

Blog Migration

Took some time to check on my old blog which surprisingly had quite a number there and I decided to so called migrate in to this just for the sake of safe keeping as I did put effort into blogging it out.

My Old Rig - Praetorian Storm

System Specification

Pentium 4 (2.66GhZ) with Cooler Master Ultra Vortex heat sink
Kingston 1GB DDR RAM
Motherboard: MSI 865PE Neo2-P
Sound Card: Creative X-Fi Extreme Music
Graphic Card: Winfast A6600GT TDH with Zalman VF-700cu
Power Supply: Cooler Master Real Power 480W
80GB Maxtor PATA HDD
80GB Seagate SATA HDD
160 Seagate SATA HDD
DVD Burner: SONY DRU-810A(Burning 16X Speed)

Fans:
1 X 12cm Cooler Master Blue UV Sensitive
1 X 8cm Custom made 2 colour + custom Blue Flame fan grill
2 X 8cm Cooler Master White Neon
1 X Vantec Dual Fan UV Sensitive
3 X Normal 8cm Cooler Master

Additional Info:
-Blue UV sleeving are done on most cable and wiring
-UV Drawing are done on Winfast A6600GT TDH using Pilot Pastel Pen
-Internal casing are pasted with Dull Black sticker to give more UV effect

Just to post up some of my old rig that I spent countless time and serious cash. So this was my first system that mod which I gave a project name Praetorian Storm and one of the boldest modding I done is the graphic card. Having to draw ultra violet ink on the PVC board of my graphic card is seriously dangerous as any wrong move will cause my entire system *KABOOM* yet I manage to get it done without any problem.

This system already obsolete as the graphic card long time been sold, motherboard gone case, HDD ended up as an external storage, casing let my dad to use on his system and other parts just keep in almost like a pile of junk. It was great get to mod and create something different from other people as part of my hobby but now I just don't have the time to mod and basically financially tight to get new hardware yet I'm sure one fine day I will start modding again as my buddy is eager to see me back in action again~

For now all I have is just a Download Station System which its on 24/7 to download all sorts of data which I might post up the casing I'm currently using and really limited modding is done on it but still looks cool due to its simplicity and efficient cooling performance.

SQL Server 2008: MERGE Statement

Previous version of SQL Server required INSERT, UPDATE or DELETE statement in order to perform MERGE function from two table with similar attributes. Now with the release of SQL Server 2008 along together with the support of MERGE statement which perform the long tedious statement within few lines of statement. To keep things short and simple, just review the sample statement and sample tables below:

Base on the image, the MERGE statement will perform merging function as dbo.New_Table will merge with dbo.Existing_Table with the condition of MATCHED and NOT MATCHED. Basically the MERGE statement will check each row with ID is the matching criteria and if it match then UPDATE SET will update dbo.Existing_Table selected column with dbo.New_Table selected column. If the ID not match then INSERT will insert dbo.New_Table row into dbo.Existing_Table. The result is show below on dbo.Existing_Table:

This is really a basic MERGE statement as more complex MERGE can be done which allow flexible condition and result to be shown. I will blog more on SQL Server 2008 MERGE Statement once I have futher knowledge on this function.