Codepage Data Transformation for SQL Server 2000

CPDTS.dll - version 1.0

Contents:

Introduction

Download and Installation

Prerequisites and Constraints

How to use

Supported codepages

Introduction

Would you like to migrate to Microsoft platform but your data format and encoding is holding you from doing so? Or would you like to keep your investments untouched but at the same time add some modern services to your existing solutions?
CPDTS.dll is a data transformation COM object that converts your data from different encoding. This COM object will be used from within Microsoft SQL Server Enterprise Manager. This means there are no additional steps to move data to or from SQL, and no additional programs to convert data! With a special feature of handling Arabic on mainframe and AS400 in IBM 420 codepage which is visual order and convert is to logical order.

DTS is a set of graphical tools and programmable objects that let you extract, transform, and consolidate data from different sources into single or multiple destinations.
It is based on OLE DB architecture, so it supports a variety of data sources. For example:

• SQL Server and Oracle directly, using native OLE DB providers.

• ODBC sources, using the Microsoft OLE DB Provider for ODBC.

• Access 2000, Excel 2000, Microsoft Visual FoxPro®, dBase, Paradox, HTML, and additional file data sources.

• Text files, using the built-in DTS flat file OLE DB provider.

• Microsoft Exchange Server, Microsoft Active Directory™ and other non-relational data sources.

• Other data sources provided by third-party vendors.

Download and Installation

Steps for installation

Step 1: Copy CPDTS.dll to your hard disk.

            Create a new folder eg. DTSfolder and copy CPDTS.dll to it.

Step 2: Register the object.

            From the task bar go the Start menu \ Run and type

regsvr32 <DTSfolder >CPDTS.dll

That’s it! Now you can use the codepage data transformation COM object from within Microsoft SQL Server to convert your data!

Prerequisites and Constraints

• The number of source columns should equal the number of destination columns. This is an expected behavior because this is a one-to-one mapping.

• The source column type should be  “string”, the destination column type should be either "string" or “Unicode string”. In the case of SQL you may use data types such as nchar or nvarchar etc.

• To avoid data loss, we recommend the destination column size to be one and a half times larger than the source column size, because one character in the source can match to two characters, e.g. لا.

How to use

Step 1

From Microsoft SQL Server enterprise manager, create a new data transformation services package.

Step 2

Add the data connections, both the source and the destination connections.

Be sure to configure the DB2 source correctly, from the source properties, set the advanced \PC code page option to ANSI – Latin 1 [1252].Host CCSID to EBDIC – U.S./Canada[37] and check the options “Process binary as character”. The following is an example of such a configuration:

Step 3

Add a transform data task and correctly specify the source and destination. Create a new transformation based on the “Convert Arabic Strings”. As shown in the following figure.

Select the proper the columns mapping and you should be aware of the constrains, mentioned earlier in the Prerequisites and Constraints section.

Set the source and destination codepages, from the transformation properties.

.

Supported codepages

The Codepage DTS object supports the conversion from any Windows supported code page to any Windows supported code page including Unicode. The following are the main codepages:

Code pages