Hi. This is my first time posting (have read and gotten LOTS of help in these forums tho) so forgive my etiquette. I am trying to pull mainframe (EBCDIC) datasets (flat-files) into SQL Server 2005 using an SSIS package (have 25 separate files/tables to do this with). Some of the data is in packed decimal (comp-3) format. I have the flat-file connection setup using the correct code-page and now need to use the "Unpack Decimal" transform I've read about to get the comp-3 fields converted into my table. My problem is I don't have Visual Studio 2005 installed and don't have the availability to install C# Express (not sure that would work but I suppose it might). A friend of mine has Visual Studio 2005 and SQL Server Express (which doesn't include SSIS and the "runtime" library required to build the assembly). Seems I've gotten a little stuck (as a database developer) with the 2005 platform. This is a huge show-stopper for me and I need some help. I understand (from my readings of ALL the postings in here regarding Unpack Decimal), that if I drop the compiled dll into \Microsoft SQL Server\90\DTS\PipelineComponents and run gacutil on it I should be able to add it to my toolbox. I need someone to get me a compiled version PLEASE and ASAP... this is quite a large project and I'm dead in the water if I can't unpack these decimals... HELP !!!!

http://www.ssistalk.com/UnpackDecimal.zip My official page describing this can be found here: http://www.ssistalk.com/how-tos/unpack-decimals-comp-3-from-the-mainframe/ That is the entire kit from Microsoft's site, plus a compiled .DLL along with installation instructions.

Trying this now. Will keep you posted (and close this thread if we're good). Stand by...

Well even logged in as local admin I have no access to the assembly folder - I guess this is bad for me (my company probably has a policy set...)

Continuing saga - OK I ran gacutil and it went into the assembly folder... BUT 1. it doesn't show in the .NET Framework Components list (under Choose Toolbox items) 2. if I browse and select the dll I get an error saying "There are no components in <filename> that can be placed on the toolbox" (<filename> = the name of the dll) I don't need to re-start or anything right ? I know I must be missing something...

Did you add the .dll to the C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents directory?

Yes and I got that to work fine. Sorry this is killing me but now, no matter what data type I set my destination field to I get "Data conversion failed" "The value could not be converted because of a potential loss of data"... (hitting the error on my flat file source...hmmmmmmmm). Any ideas anyone ?

Dan Reinholdt wrote: Yes and I got that to work fine. Sorry this is killing me but now, no matter what data type I set my destination field to I get "Data conversion failed" "The value could not be converted because of a potential loss of data"... (hitting the error on my flat file source...hmmmmmmmm). Any ideas anyone ?Did you read the readme.txt file?Your input needs to be of type, bytes (DT_BYTES), and there are scale limitations. The prerequisits should all be in that readme.txt file.

yes I see that... just not sure what the scale value should be...hmmmmm

Dan Reinholdt wrote: yes I see that... just not sure what the scale value should be...hmmmmmWhat is the scale of the COMP-3 field? That should be the value you specify in the UnpackDecimal component.PIC S9(5)V99 COMP-3. (Scale of 2)PIC S9(5)V9999 COMP-3. (Scale of 4)PIC S999V9 COMP-3. (Scale of 1)

ah so it is simply the number of decimal places (scale) got it. Well it's all running with no errors but I seem to be getting more nulls than I thought I should. Will have to verify some of the input data. These are "payment" records so I'm expecting SOME values... QUESTION : this is a delimited file does it matter how wide I define the byte stream length in the connection manager ? I have these defined as the viewable length of the data when opened in Notepad (6 characters). I know I just don't have something right just not sure what...

You are limited to a maximum of 14 bytes per field going into the UnpackDecimal component.Setting it to the display length, in bytes should work.Your decimals are packed, right and not zoned? That is, it truly is binary data in your files, and is not readable numbers, correct?

Yeah they're all little boxes and question marks and other annoying characters but thereare aCERTAIN number of them. Also I'm pushing the decimal output from UnpackDecimal into SQL Server MONEY typed fields... no issues there...

Dan Reinholdt wrote: Yeah they're all little boxes and question marks and other annoying characters but thereare aCERTAIN number of them. Also I'm pushing the decimal output from UnpackDecimal into SQL Server MONEY typed fields... no issues there...So do you have this working now?

Well I'm going to mark this thread as "answered" because you did take care of my described issue (which I GREATLY appreciate you ROCK Phil thanks). But if anyone would think about the following and possibly reply I'd appreciate it... 1. FTPing file from UNIX to Windows 2. Have an ASCII file connection manager (code page 20127) - one of few formats the wizard recognizes for this file 3. Convertingthree packed fields,two of 9999.99 format and one of 0000 format a - file reader is set to use the "viewable number" of characters (byte stream data type allows for length which defaults to 50 - opening the file in Notepad displays a fixed number of "non printable" characters in each column so I'm using those lengths) b - PackedScale propertiesare set to 2 and 0 appropriately 4. No errors are reported during task execution 5. Resulting data looks "odd"... a - 1st 9999.99 field has nothing but nulls (different sets of garbage visible in the file-could all those still mean "null" ?) b - 2nd 9999.99 field has mostly the value 0.0000 (money field interpretation of 0.00 I'm guessing) but also a few nulls c - 0000 field has nothing but nulls (which I believe because the garbage visible in the file for that column is 4 square boxes - every one SO OBVIOUSLY I need to verify the input file data before I cause an alarm but maybe if anyone could confirm or deny or just think out loud or maybe have done this... appreciate it.

If you can provide a sample input file, I'd be happy to take a look at it.My e-mail address is in my profile. (Replace "_dot_" with a period)

I could also try to help out if you would like. Email is in my profile.

Eric Wisdahl wrote: I could also try to help out if you would like. Email is in my profile.Yep, sorry. We worked this out offline.The solution was for Daniel to transmit the file as non-ASCII from the mainframe down to his machine.

hi, I have installed this component but how do I actually use it to import a bin file into a sql table ? Are there any instructions on how to do this >? I have tried adding a flat file source and changing the code page but I dont know what format to choose ... regards Jamie

You might check out http://www.aminosoftware.com/ i know they are working on a suite of EBCDIC tools/components for converting into SQL SERVER via SSIS.

Hello,I am pretty much in the same situation, must load an EBCDIC file with packed decimal in comp-3 into a SQL table.I have followed all the steps indicated in this thread and others and so far I've been able to get the "UnpackDecimal Data Flow Transformation" to work ... sort of.The problem is that in the destination table the decimals appear allways as NULL.The destination decimal column type in the table is set as VARCHAR (I've tryed to set Integer with no success).Any help on this will be apreciated. If you need more information please ask.Thanks.

Does it work only for SQL 2005 or is it applicable for all versions? I have SQL server 2008 but I did not see the component in the list after adding the DLL in both folders.