EBCDIC TO ASCII conversion in SSIS, packed decimal field

Question

EBCDIC TO ASCII conversion in SSIS, packed decimal field

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

ava1over Old Hand Points: 383 More actions · Answer 1

No need for 3rd party software; conversion of packed data using SSIS is explained along with a demonstration here: http://dataintegrity.wordpress.com/2009/10/02/ebcdic-packed-comp-3-data-ssis/

Michael Newell SSCrazy Points: 2802 More actions · Answer 2

ava1over: Thanks for the posting that link. You are right, no need for 3rd party controls when you simply need a way to translate fields like we've been discussing throughout this thread. As new SSIS transformations are created, they tend to serve as a better starting point for these types of programming requests as opposed to relying on custom written code. Besides, the original version of my code was developed quite a few years before SQL 2005 even existed, so the only other alternative at the time was to spend big $$$ on something that although could do much more than was needed, not do a single thing well enough to warrant the price. So goes the saying, "Jack of all trades, master of none"...

blackstonem SSC Veteran Points: 239 More actions · Answer 3

Can you post an example of the fnUnpackedDecimal which you stated? Also, the code in question refers to a variable @i but it isn't declared. Is this a bug?

Thanks.

The function is really helpful.

Michael Newell SSCrazy Points: 2802 More actions · Answer 4

As mentioned earlier in this thread, the @i variable is incorrect and should be @pos. I had changed the variable name for clarity's sake in the original post and overlooked it.

So, by using the fnUnpack function, you have the ability to create additional functions that are designed to work with specific datatypes. Basically, these functions would do all the type conversion and validation so you don't have to worry about putting all that type of redundant coding in your main ETL procedure.

@blackstonem: You had asked about the fnUnpackDecimal function I had used to illustrate this concept. Here is the code that may provide more insight to what this does.

[font="Courier New"]

CREATE FUNCTION [dbo].[fnUnpackDecimal] (

@InputStr varbinary(1000) = NULL,

@precision int = NULL

)

RETURNS decimal(18,9)

AS

BEGIN

DECLARE @RetVal decimal(18,9)

DECLARE @temp varchar(2000)

SET @RetVal = 0

SET @precision = ISNULL(@Precision, 0)

SET @temp = dbo.fnUnpack(@InputStr)

SET @RetVal = CAST(@temp AS decimal(18,9))

SET @RetVal = @RetVal / POWER(10, @precision)

RETURN @RetVal

END

[/font]

This function accepts EBCDIC packed binary (varbinary) input value and unpacks into an ASCII (decimal(18,9)) value with the decimal added in a specified position.

Example: [font="Courier New"]SELECT dbo.fnUnpackDecimal(CONVERT(varbinary(5), 'P@ '), 4)[/font]

Returns: 105.0402

Note the (18,9) precision is arbitrary and should suffice for nearly any intended use. Hope this answers your question!

Michael Newell SSCrazy Points: 2802 More actions · Answer 5

I edited the original post containing the fnUnpack function to fix the undeclared @i variable error (should be @pos). Please let me know if there are any other problems with the code. Thanks.

steve block SSCertifiable Points: 7698 More actions · Answer 6

Just a quick side bar that may help someone, FTP normally does the translation from EBCDIC to ASCII. I'm not sure what needs to done on the Mainframe side before FTPing the file, but that's what we use here.

Steve

Michael Newell SSCrazy Points: 2802 More actions · Answer 7

I agree. Most vendors can easily switch translations, either EBCDIC or ASCII test as part of the FTP Upload process. But the issue of how to deal with packed data still remains. Without the COBOL copybook or other document which contains information such as position, length and precision of each packed field, I don't possibly know how one would go about trying to retrieve this data. Packed data is packed data whether it is in ASCII or EBCDIC.

jdgonzalez@jdandrachel.com Hall of Fame Points: 3161 More actions · Answer 8

What about using codepage 37 in your import process? It's been a while since I've worked with EBCDIC but I remember I had to change the codepage and was able to import data.

Leroy Dively Newbie Points: 9 More actions · Answer 9

Michael, thanks a million for posting that code. It was a lifesaver. I have an ascii text file that a client put out with the compressed data just sitting there in the middle of it all. Using your function, I was able to get the data translated to a legit date. Much appreciated.