I'm trying to load en EBCDIC file with packed decimal in comp 3 into a SQL table.
I have been searching information in this forum in several threads and have downloaded a DLL from http://www.microsoft.com/downloads/details.aspx?familyid=0e4bba52-cc52-4d89-8590-cda297ff7fbd&displaylang=en
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.
I need to do EBCDIC to ASCII conversion in SSIS. The incoming data has packed decimal fields in it. Has anyone been able to convert packed EBCDIC decimal fields to ACSCII using SSIS?
What do I need to do to import Ebcdic file with comp3 packed data fields from mf into SQL Server? Is this possible? Do I need to go into advanced properties and change the data type? Or do I need to use data conversion task for the packed fields. Any advice or if somebody could point me to a sample script would be greatly appreciated.
I am having a file in which amount fields are given in a Packed Decimal format. Can anyone suggest me how I can read this data element from the file and convert it into SQL decimal datatype.
File is a fixed length. All the amount fields are given in Packed Decimal Format and rest of the fields are given in text format. How can i identify and convert only those packed decimals using SQL/.Net.
Example : a row in a file that has some packed decimals 158203508540188236252EUR20BZK0030 Å“& 20060715 0001010100010101
I have been given some data from a Mainframe (AS400?) which has some fields coded in Packed Decimal. I have been able to load the data into a SQL2005 database table, but I now need to convert the Packed Decimal data in the binary(6) field to the appropriate integer (or float) value.
The field contains values such as the following:-
0x20202020200C
0x202020022025
0x20202020DFFA
I don't know how to interpret these. Has anyone got a function that can do this for me?
I've read several articles online that explain how packed decimal works, but none tell me how to interpret the last of my three examples. Can you help?
Hi I am importing data from mainframe using DTS the problem is that I am getting some COMP fields from the mainframe these are compressed data fields that are used to reduce storage space on the main frame. Can some one please help me understand how we can use DTS to solve this problem that is decompress it before transferring to table in SQL server. any code in scripting language to help decompress this datatype will be really appreciated.
Is it possible to use Database Mirroring when running a SQL Server 2005 database in compatibility mode 80 (SQL Server 2000)?
If upgrading to SQL Server 2005 we would have to run Principal, Mirror and Witness in mode 80 to support our Transact-SQL Queries. To upgrade our Queries would be equally (or more) costly then to buy new SAN hardware for a cluster in wich case we might well continue running our 2000 cluster. (Buying hardware is slightly more time efficent then rewriting some 1500 queries)
Hi, Is it possible to make Pivottables in Office 2000(Frontpage or Excel) and get data from cubes in Analyses Services SQL server 2005? If it works, are there any problems or do I have full functionality?
OK gang here I am again. I've gotten the Packed decimals and standard 4-byte unsigned integers to work. NOW I'm stuck on 2-byte integers.
1. My file connection defines a 2 byte column of type DT_BYTES (2 bytes in and 2 bytes out) 2. My Data Conversion task is set to convert the 2 byte ByteStream to a 4 byte unsigned integer (if I try to convert to a 2 byte unsigned integer SSIS gives me a datatype conversion (not allowed) error at design-time 3. Data Conversion task throws error at run-time stating "The value could not be converted because of a potential loss of data." 4. How could I lose data going from 2 bytes TO 4 bytes ? 5. HOW do I get this value into my SQL Server int column ? 6. The HEX representation of this data looks exactly as the 4 byte column that works looks (0x00 0x01, 0x00 0x02, etc) only 2 bytes instead of 4
HELP ANYONE. I'm at my wits end with this. I've read in this newsgroup that "Microsoft won't be building data conversion utilities for every single data source know to man" (almost like we're asking for a converter for "Johns Personal DBMS"). Mainframe, COBOL, and DB2 are entrenched in many large industries with legacy systems some of us want to interface with (to make it easier for our users to get at that legacy data). Hard to do though when MS won't take the time to bridge the communications with MAJOR systems like Oracle, DB2, and COBOL. Bridging those 3 languages alone would take care of MOST of legacy interactions. This seems a bit shortsighted and unsupportive to those of us forced to deal with ancient systems.
PLEASE help me someone get past this last issue with 2 byte unsigned binary integers
I am working on a project that will be mimicking an existing interface that we have with one our our clients. That interface today sends EBCDIC packed fields. We do not want to introduce changes to the external clients interface file when we rebuild it in SQL 2005 Integration Services and I need to find out how I can take ASCII data and convert it to the host (mainframe) representation, which is what we currently provide to our external client using Integration Services.
Has anyone had to do this? If so, can I accomplish it natively with SSIS, or do I need to look to a third party vendor for a component?
I tried to setup a flat file data source that has code page 37 (EBCDIC)
Then I have a flat file destionation that is ASCII.
And inbetween I have tried several different data flow conversion tasks liked Data Conversion, and Derived Column. But I keep getting errors about different code pages.
I also tried to load the EBCDIC data into a SQL Server DB, and it complains about different code page.
Has anyone been able to do this with SSIS out of the box, without any extra components ?
I have data arriving in fixed-width EBCDIC format. Each file contains one or more groups of records. Before and after each group there is a header/footer, which is not in the same layout as the records that it describes. Header, record and footer each have a different layout to the other but are consistent within themselves.
Thankfully the one thing header, footer and record layout have in common is their length, so at the moment, using the appropriate code page in the Flat File Connection Manager, I'm able to read all the columns as strings. The headers and footers just come through, albeit a bit weird looking, and I can filter them out with a conditional splt.
However, the header contains information that needs to be appended to each record in the group. Does anyone have any suggestions about how to achieve this? I'm trying to avoid developing a custom data source for this task but, if there's no other way, has anyone done it and do they have any tips?
I have downloaded the UnPack Decimal component and, as it needs byte stream (dt_bytes) as input, so I set up an appropriate Flat File Source columns.
But I get this error:
[Flat File Source [2201]] Error: Data conversion failed. The data conversion for column "DTCDC" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
[Flat File Source [2201]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "DTCDC" (2250)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "DTCDC" (2250)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
DTCDC is first of the columns packed. These are mostly date columns packed into 5 bytes - should be unpacked to normal SQL date.
I've tried different locale , as suggested in other threads, but it didn't help.
Can anybody help me with this issue how can I do it in (VB.NET script ,importing as String??).
Hi, how are you? I generated a Data Flow Task where a OLE DB Source connects to a SQL Server and gets data from a table. The next step, writes a txt file with the information (Flat File destination). All data is imported to txt fiel if this one is configured as Code Page: 1252 (ANSI - Latin I) in the connection manager for the flat file. But if I change Code Page: 500 (IBM EBCDIC - International) which is the one I need beacuse I have to imported in a mainframe, it doesn't work. This is the error that I receive:
Code Snippet TITLE: Package Validation Error ------------------------------ Package Validation Error ------------------------------ ADDITIONAL INFORMATION: Error at Data Flow Task [Flat File Destination [31]]: The code page on input column "STATUS_CD" (1293) is 1252 and is required to be 500. Error at Data Flow Task [Flat File Destination [31]]: The code page on input column "SRC_NUM" (1294) is 1252 and is required to be 500. Error at Data Flow Task [DTS.Pipeline]: "component "Flat File Destination" (31)" failed validation and returned validation status "VS_ISBROKEN". Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation. Error at Data Flow Task: There were errors during task validation. (Microsoft.DataTransformationServices.VsIntegration)
Does any one knows how can I convert from ANSI to EBCDIC or what I have to configure so as to not receive that error messsage? Thanks for help and time. Beli
Hello all, I was trying to run a test to write a ebcdic file out with a comp - 3 number (testing this for other people) and have run into a problem writing the string out to the flat file destination. I have the following script component:
Code Block
' Microsoft SQL Server Integration Services user script component ' This is your new script component in Microsoft Visual Basic .NET ' ScriptMain is the entrypoint class for script components Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Public Class ScriptMain Inherits UserComponent Public Overrides Sub CreateNewOutputRows() ' ' Add rows by calling AddRow method on member variable called "Buffer" ' E.g., MyOutputBuffer.AddRow() if your output was named "My Output" ' Output0Buffer.AddRow() Dim myByteArray() As Byte = {&H12, &H34, &H56, &H7F} Output0Buffer.myByteStream = myByteArray Output0Buffer.myString = "ABCD" Output0Buffer.myString2 = "B123" myByteArray = Nothing End Sub End Class
I have added myByteStream as a DT_BYTES length 4, myString as (DT_STR, 4, 37) and myString2 as (DT_STR, 4, 37) to the output 0 buffer.
I then add a flat file destination with code set 37 (ebcdic us / canda) with the corresponding columns using fixed width.
When i place a dataviewer on the line between the two the output looks as I expect ("0x12 0x34 0x56 0x7F", "ABCD", "B123"). However, when it gets to the flat file destination it errors out with the following:
Code Block [Flat File Destination [54]] Error: Data conversion failed. The data conversion for column "myByteStream" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
If i increase the size of the byte stream (say, to 50) the error goes away but I am left with the string "1234567F" instead of the appropriate hex values. Any clues on how to go about this? I obviously don't care if it gets transferred to "readable" text as this is supposed to be a binary stream, thus the no match in target page seems superfulous but is probably what is causing the problems.
NOTE: this is relating to the following thread (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2300539&SiteID=1) in that I am trying to determine why these people are not seeing the "UseBinaryFormat" when importing an EBCDIC file (i see this fine when i use an ftp'd file, but it auto converts to ascii) with comp-3 values. I also see the "UseBinaryFormat" when I am importing a regular EBCDIC file which I create that has no import errors with zoned decimals.
I am working with a legacy SQL server database from SQL Server 2000. I noticed that in some places that they use decimal data types, that I would normally think they should be using integer data types. Why is this does anyone know?
Example: AutomobileTypeId (PK, decimal(10,0), not null)
I am creating a table on SQL Server. One of the columns in this new table contains whole integer as wells as decimal values (i.e. 4500 0.9876). I currently have this column defined as Decimal(12,4). This adds 4 digits after the decimal point to the whole integers. Is there a data type that will have the decimal point only for decimal values and no decimal point for the whole integers?
I would like to cast (convert) data type decimal(24,4) to decimal(21,4). I could not do this using standard casting function CAST(@variable as decimal(21,4)) or CONVERT(decimal(21,4),@variable) because of the following error: "Arithmetic overflow error converting numeric to data type numeric." Is that because of possible loss of the value?
I wanted to convert a dataset from vb.net (2.0) to an .XLS file, by MS Jet. My national standard is using decimal commas, not decimal points for numbers signing the beginning of decimal places. But the MS Jet Engine uses decimal point,in default. Therefore, in the Excel file only string formatted cells can welcome this data, not number formatted. How can I solve or get around this problem? (with jet if it possible) iviczl
I'd like to convert a Decimal value into a string so that the entireoriginal value and length remains intact but there is no decimal point.For example, the decimal value 6.250 is selected as 06250.Can this be done?
I am designing some reports for a German branch of my company and need to replace decimal point with a comma and the thousand comma seperator with a decimal point.
e.g. ‚¬1,500,123.00 to ‚¬1.500.123,00
Is there a property that I can change in the report designer to allow this to happen or is this something I need to convert in a Stored Proc.
Anybody noticed that SQL Server rounds up if the value is half waybetween two rounded values, but C#'s Decimal.Round(Decimal,Int32)rounds to nearest even number?[color=blue]>From MSDN: "When d is exactly halfway between two rounded values, the[/color]result is the rounded value that has an even digit in the far rightdecimal position. For example, when rounded to two decimals, the value2.345 becomes 2.34 and the value 2.355 becomes 2.36. This process isknown as rounding toward even, or rounding to nearest."I perform the same calculation sometimes on the web server in C# andsometimes at the database in T-SQL, but want to get the same resultfrom both calculations. Could anybody offer any strategies for dealingwith this?Thanks ~ Matt
i am having trouble the with the line below:SELECT AVG(CAST(RATE1 AS Decimal(9, 2))) AS Expr1i am trying to do an average on the column "rate1" and want only 2decimal points but the syntax i wrote doesn't do it. is theresomething i am missing or doing wrong?when i run the above syntax i get 7.246428 and i need it to be 7.2 iknow this is possible in SQL but for some reason cant figure it out.Any help would be greatly appreciated.thanks
Is there any disadvantages of using Decimal type as a primary key? Ifso, are there other data types that can hold large numbers (more thanan int can hold: 13 digits or so) and perform better as a Key field?Thanks.
I imported some feilds from a text file. Most of which i set to varchar. However, I want one of the feilds to have 2 decimal places.
I tried changing it in the table design after the import and nothing changed. Then i tried re-writing the query for insertiing the values, where i changed varchar(11) to numeric (11,2) but i got an error. "Error converting data type nvarchar to numeric."
I just created a new table in my database. One of the fields in that table has a data type of money. Would my C# code be better off referring to this field as SqlMoney or as a decimal and why? Would I be better off altering the field in the table so that it has a data type of decimal? Thanks
