none
Importing DB2 flat file with Bulk Insert

    Question

  • HI,

     

    I am having trouble importing a flat file that was extracted from an AS400 server, into a SQL 2005 DB table using Bulk Insert. This file contains a column (field) that is of a Packed Decimal data type. Data in all other fields is displayed normally when viewing this file in a text editor such as Note Pad or Text Pad, but this one field comes up with unknown encoding: squares, thick vertical lines, basically strange characters and no numeric data.

     

    Does anyone have any experience dealing with file of this sort?

     

    Any insight would be appreciated.

     

    Thanks.

     

    Monday, October 22, 2007 4:54 AM

Answers

  • Hi,

     

    Ok finally, I was able to set up a Data Flow task that had Flat File, containing a Packed Decimal Field as source, UnPackDecimal Component and SQL Server talbe as destination. I think the COMP-3 data is not being manipulated at all, it looks like it is being transfered in the database as is in bytes streams. I have checked the mapping several times to make sure that the Input Columns indeed come from the UnPackDecimal Component  and not the source file...

    Still the transformation does not look right.....

     

     

    Tuesday, October 23, 2007 8:55 PM

All replies

  • You will have to decode the packed decimals.  Look at the microsoft download for an unpackdecimal transformation:

    http://www.microsoft.com/downloads/details.aspx?familyid=0e4bba52-cc52-4d89-8590-cda297ff7fbd&displaylang=en

     

    Notes on how to install this:

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2193264&SiteID=1

     

    A different way to unpack using pivot / unpivot transformations:

    http://www.ssistalk.com/2007/03/14/ssis-working-with-cobol-zoned-signed-decimals/

     

    Monday, October 22, 2007 1:06 PM
    Moderator
  • FYI - The link of mine that Eric linked to works for "zoned/signed" decimals.  Not packed.  They are different.  Packed decimals are COMP-3 in mainframe speak, and are in hex format, which doesn't translate to readable ASCII characters, usually, and as such my method won't work for packed decimals.  My method will work quite well for zoned/signed decimals though.
    Monday, October 22, 2007 4:26 PM
    Moderator
  • Eric, Phil, Thanks so much for all the input.

     

    Eric, I was able to install the UnpackDecimal Sample Component, and tried using it to convert that column, but I keep getting "Error Data Flow Task [UnpackDecimal]: Component operates only on bytes input. Other types are forbidden". And this is still in the Edit screen.

     

    Phil, excellent article. Looking at the comments I figured that this would not work for the packed decimal, and your post put in perspective. I think, I do see hex format when opening the files in ASCII text editor.

     

    Any info on dealing with those ?

     

    Thanks.

     

     

     

    Monday, October 22, 2007 5:21 PM
  • You must set the data type of the comp-3 column to byte stream and the "UseBinaryFormat" to true in the advanced editor of the flat file source.

     

    Monday, October 22, 2007 8:10 PM
    Moderator
  • Thanks. I set up a Source file flat file, UnPackDecimal Component and A destination Flat File to test. For some reason I see no data at all in that column. Wonder what I am doing wrong....

     

    Tuesday, October 23, 2007 3:43 AM
  • Hi,

     

    Ok finally, I was able to set up a Data Flow task that had Flat File, containing a Packed Decimal Field as source, UnPackDecimal Component and SQL Server talbe as destination. I think the COMP-3 data is not being manipulated at all, it looks like it is being transfered in the database as is in bytes streams. I have checked the mapping several times to make sure that the Input Columns indeed come from the UnPackDecimal Component  and not the source file...

    Still the transformation does not look right.....

     

     

    Tuesday, October 23, 2007 8:55 PM
  • Eric, I am trying to use unpackdecimal, however when it translates, it defaults to zeroes, I think this is because I have not set the "UseBinaryFormat" to true in the advanced editor of the flat file source.  I can't find where to do this though, I have looked several times.  My flat file connection is set up as Code Page 37 (IBM/EBCDIC), with one column defined as DT_BYTE.  When I go to the flat file source(advanced editor), I can't find this option that you mentioned anywhere.   Any ideas would be greatly appreciated.
    Friday, November 02, 2007 6:43 PM
  • Just to clarify, this is the advanced editor for th flat file source (as opposed to the flat file connection manager).

     

    Right click the flat file source, choose advanced editor.

    click on the "Input and Output Properties"

    Expand the Flat File Source Output

    Expand the Output Columns

    Choose the Column of interest from the list of output columns

    Find the "Custom Properties" Section

    - There should be two properties in the "custom properties" section, FastParse and UseBinaryFormat

    Set the UseBinaryFormat to true

    Monday, November 05, 2007 1:37 PM
    Moderator
  • Eric

     

    I am having a similar issue with a file coming from a mainframe.  I have downloaded and installed the Unpack Decimal.  Also, I have installed SQL Server 2005 SP 2.  But even though I am seeing the FastParse custom property in the advanced editor, I don't see the UseBinaryFormat custom property.

     

    Any thoughts as to why I don't see UseBinaryFormat?  Does the flat file have to have any special file extension for it to be recognized as a mainframe file, or some such thing?

     

    Thank you for all the great posts on this thread already!  They have been very helpful to get me this far, I just don't see the UseBinaryFormat for some reason.

     

    Thanks!

    Stephen Darnielle

    Tuesday, November 06, 2007 4:50 PM
  •  Stephen Darnielle wrote:

    Eric

     

    I am having a similar issue with a file coming from a mainframe.  I have downloaded and installed the Unpack Decimal.  Also, I have installed SQL Server 2005 SP 2.  But even though I am seeing the FastParse custom property in the advanced editor, I don't see the UseBinaryFormat custom property.

     

    Any thoughts as to why I don't see UseBinaryFormat?  Does the flat file have to have any special file extension for it to be recognized as a mainframe file, or some such thing?

     

    Thank you for all the great posts on this thread already!  They have been very helpful to get me this far, I just don't see the UseBinaryFormat for some reason.

     

    Thanks!

    Stephen Darnielle



    I too am trying the same thing and do not see the UseBinaryFormat option.

    Assuming FastParse remains False?
    Wednesday, November 07, 2007 6:08 PM
  • I also can't find the UseBinaryFormat properties option.   Has anyone else besides Eric been able to find this?!

     

    Monica

     

     

    Wednesday, November 14, 2007 1:10 AM
  • Everybody who is looking for this, I have now found it and here is what I did.

     

    1.  Applied SQL Server SP2.

    2.  Applied the latest SP's to Visual Studio.

     

    However, I don't know that #2 is absolutely required.  Because I was still not seeing UseBinaryFormat after doing both of these items.  Until...I created a new Flat File Source, for a different file just for testing because a co-worker of mine saw his OK.  Then I could see UseBinaryFormat!

     

    Which led me to delete the connection and create a new connection with a "new" flat file source to my original file.  Everything about my connection and flat file source was exactly the same but I built it after doing the service packs were applied, of which I think the SQL Server one is the important one.  The connection/flat file source apparently does not reveal UseBinaryFormat unless the connection and/or flat file source is built from scratch after the SP(s) were applied.

     

    Hopefully this works for all the rest of you that are looking for it and will save you the time I took testing/trying so many permutations of how to see this.

     

    Stephen

    Wednesday, November 14, 2007 1:24 AM
  • Ok, that would make more sense as I have had these applied for some time.  I was wracking my brain trying to find a way to reproduce your problem for some time now...  Hopefully you will be able to use the unpackdecimal with no problems now, but if not, reply back and i'll try to walk you through it.

     

    Wednesday, November 14, 2007 1:24 PM
    Moderator