SSIS: Simple Custom Component (Trim)

I have been reading lots of materials and resources about developing your own SQL Server Integration Services custom component which took me a while to understand the function and required component needed to developed my own custom SQL Server Integration Services component. What I will do here is to explain and show a simple demonstration and code required to create a custom component.

This is a Trim Custom Component as this function can be done at Derived Column Component yet to make this demonstration as simple as possible, using the Trim function is sufficient enough.

To begin, create a Class Library Project then add References as shown below

Needed to add Component Name listed below:
Microsoft.SqlServer.DTSPipelineWrap
Microsoft.SQLSever.DTSRuntimeWrap
Microsoft.SQLServer.ManagedDTS
Microsoft.SQLServer.PipelineHost

Once added, lets start coding...

What need to take note is the DtsPipelineComponent which its required to create the component to be seen on SQL Server Integration Services Data Flow Transformations and the PipelineComponent base class. The other code is to declare variables.

Basically this code is to provide the component properties which first it will reset the component, then add the input and output collection everytime it loads.

This code is execute during Design Time of the component. Validation of the selected column which the code perform checking on selected column to is 'Read and Write' and make sure the datatype is String format.

This section of the code execute during early stage of Runtime which will populate the input and output collection.

This section of the code execute during Runtime and basically this is where the Trim function take place. each column has its own ID where it was generated on PreExecute function.



Next, go to your project properties then Signing Tab, then click on the "Sign the assemble" and Create a strong name key file. Last but not least, built the project and the .dll file is created at th project's Debug folder.

*Will update soon on adding the custom component that has been built to SQL Server Integration Services package.

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