data conversion - numeric to string loosing precision

Hi All,

i'm using a "data conversion" object to convert a numeric field to a string just before i save the record set to the database.

the problem is when this numeric field is > 0 it looses the precision on its decimal value.

example, if numeric value is 0.32

after converting this to a string, the new value will be : .32

it's lost the 0 infront of it. i can't do this converion in the query level because it's a derived field, so i need to convert it to a string before stroing it.

when converting to string i'm using the code page 1252 (ANSI - Latin I). i also tried with unicode string both looses this 0 infront.

can any one help on this?

Is your ultimate database target for the numeric data type a character based column, hence the need to retain the leading zeros?

If that is the case, retaining leading zeros can be accomplished with a derived column transform, rather than a data conversion transform. There you have access to the SSIS expression language, with a relatively standard, if meager, set of string functions, as well as type casts.

Another alternative for pretty much an data type conversion is a Script Transform, which will afford you the full power of the .NET framework, including in this case custom numeric formats for types destinated for strings.|||

Hi jaegd,

i tried that, but no luck. i use a derived table and this is my expression

ISNULL(investmentPercentage) ? 0.00 : investmentPercentage

here investmentPercentage is a numeric (15,2). i add the above expression as a new column (string) (8 chars). but still it looses the 0

.00 when it's null

.32 when it;s 0.32

any comments

AJ

|||

Use the Script Task, and follow the instructions in this link for numeric format specifiers for strings. You need to do something similar to this...

Dim number as float

number.ToString("D")

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconstandardnumericformatstrings.asp

|||

In the derived column, you can still acheive it with conditional logic The script as mentioned above is cleaner, but if you want to avoid adding that, you can try an expression like:

(investmentPercentage < 1 ? "0" : "") + (DT_WSTR, 20)investmentPercentage

This expression will add a "0" to the beginning of the string if the number is less than one (which is the only time the zero is dropped). When the number is greater than or equal to 1, an empty string is added.

NOTE: this does not take into account negative values. If investmentPercentage can be negative, you will need to tweak the expression a little (or post here, and I can help with that).

Thanks
Mark

|||

Hi Mark,

thanks for that, but it sort of half answer my question.

yes investmentPercentage can be negative or can even be null. i was wondering if there's an eaisier way. i've got a lot of cloumns like this, writing an expression for each of them this long can be tedious. is this how you would "tweak" it?

ISNULL(investmentPercentage) ? "0.00" : (investmentPercentage < 1 && investmentPercentage >-1 ? "0" : "") + (DT_WSTR, 8)investmentPercentage

|||

You might find it easier to use string formatting in the script component... I can't think of any other way that would be better.

As for tweaking the expression, you will have to do some extra things to take care of the negative sign (so the prepended zero doesn't end up before the negative sign). The following expression adds a case for values between -1 and 0 to handle that:

ISNULL(investmentPercentage) ? (DT_WSTR,8)"0.00" : investmentPercentage < 0 && investmentPercentage > -1 ? "-0" + SUBSTRING((DT_WSTR,8)investmentPercentage, 2, 8) : ((investmentPercentage >= 0 && investmentPercentage < 1 ? "0" : "") + (DT_WSTR,8)investmentPercentage

Let me know if this does the trick for you.

Thanks
Mark

Data conversion

can i convert the data from my Sql tables to Foxpro 2.6 , using an SP ?Are you sure you're converting FROM SQL Server TO FoxPro? Why would you want to do this? And 2.6 is a DOS version...Where are you planning to run it?|||If you are converting to foxpro, what format does it want the data in ?

CSV file, Fixed length format, tab delimited, etc ?

You can probably do it with BCP, which would be the easiest approach, right from a cmd session.|||Are you sure you're converting FROM SQL Server TO FoxPro? Why would you want to do this? And 2.6 is a DOS version...Where are you planning to run it?

my client is using fox pro 2.6 to store his data. now he's upgrading the s/w for office automation and has chosen MSSQL2k as the back end. the problem is that my client is sending data (as dbf files) to various agencies which are used for some kind of processing there.

i wud like to know whether this is possible using a Stored procedure

data conversion

I have a View that multiplies a decimal (8,5) data type * money data type (no cast or convert) and for some odd reason comes up with a bit result (0 or 1). If I take the select statement out of the View, paste it into Query analyzer and execute it I get a decimal result.

It's easy enough to put a cast into the view but I'm wondering what is going on in the view that returns the bit data type.Huh? Post the code and sample data, please.|||I don't see that happening.

set nocount on
create table test1
(col1 decimal (8, 5),
col2 money)

insert into test1 values (30.2, .6)
insert into test1 values (20.0, 1.5)

go
create view test11
as
select col1 * col2 as prod, col1, col2
from test1
go
select *
from test11
go
drop view test11
drop table test1

Data Conversion

I have single letters in a Flat file and it is in the string format,but now i want to convert it in to int format.I have tried doing this by SSIS but it is not working.
I used data conversion and copy column transformation.

I got a error message.

it automatically correct the meta data mismatch. But when i run the package, it gives the following error message.

Error: 0xC0209029 at Data Flow Task, Data Conversion [75]: The "output column "ReceiptType" (112)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "ReceiptType" (112)" specifies failure on error. An error occurred on the specified object of the specified component.

This is just one error message.

Please can you tell me the steps to do this correctly.

Also I need to know how to run a SSIS package through command line?

Thanks

Nishan

Did you say you were trying to convert letters to int? Can't do that. Is there any more info?|||

I have a flat file which has string types of data. I want to convert them into int type before I load them into destination table. I used data convertion and derived column but neither worked for me.

Ex;

Source file (String type);

column 1

B

B

C

C

Destination file format should look like this. B - > 1 , C - > 2

Destination File ( int Type);

Column 1

1

1

2

2

Can you explain the setps to do this conversion using SSIS

Thanks

|||The Derived Column task should work for you: Use the conditional operator|||Shamen,
Please keep your posts together in one thread.

This can be done in a lookup transformation. Use the following SQL for the lookup:

select "B",1
union all
select "C",2
union all
select "D",3"
....

Then hook up your source to the lookup component, selecting the second column as the return value. Coming out of the lookup component, you'll have the number associated with the letter and can then go into a flat file destination.|||

Thanks Pill and SQL Pro...I will try..at the same time I want to convert the data type too.

Yes I will keep all my posting together in one thread...

Also I have another question...Once I create the package can I run it on command line? How can I do that?

Thanks

|||Thanks I will try|||

shamen wrote:

Also I have another question...Once I create the package can I run it on command line? How can I do that?

DTEXEC runs packages from the command line.

|||

Thanks....Still I'm having a problem with data conversion...I tried using both derived column and data conversion transformation....but still no luck...

This is the error message I got when I tried with data conversion transformation.

SSIS package "Package.dtsx" starting.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.

Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.

Information: 0x402090DC at Data Flow Task, Flat File Source [1]: The processing of file "C:\p4_coventry1666_DEV02\Docs\ThirdParty\EPICWare\EPICWARE Flat File.txt" has started.

Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.

Error: 0xC02020C5 at Data Flow Task, Data Conversion [75]: Data conversion failed while converting column "ReceiptType" (67) to column "ReceiptType" (112). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

Error: 0xC0209029 at Data Flow Task, Data Conversion [75]: The "output column "ReceiptType" (112)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "ReceiptType" (112)" specifies failure on error. An error occurred on the specified object of the specified component.

Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: The ProcessInput method on component "Data Conversion" (75) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

Error: 0xC02020C4 at Data Flow Task, Flat File Source [1]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0209029.

Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.

Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x402090DD at Data Flow Task, Flat File Source [1]: The processing of file "C:\p4_coventry1666_DEV02\Docs\ThirdParty\EPICWare\EPICWARE Flat File.txt" has ended.

Information: 0x402090DF at Data Flow Task, OLE DB Destination [9]: The final commit for the data insertion has started.

Information: 0x402090E0 at Data Flow Task, OLE DB Destination [9]: The final commit for the data insertion has ended.

Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.

Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "OLE DB Destination" (9)" wrote 0 rows.

Task failed: Data Flow Task

Warning: 0x80019002 at Package: The Execution method succeeded, but the number of errors raised (7) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Package.dtsx" finished: Failure.

The program '[4360] Package.dtsx: DTS' has exited with code 0 (0x0).

|||

You can use the CODEPOINT function to return a numeric value for a character. That means you can use an expression in a Derived Column transform to do the work:

Expression: CODEPOINT(UPPER(YourColumn))-64

(the 64 is there because CODEPOINT("A") = 65)

This will work nicely where you have a one-character value to transform.

You can craft a more elaborate expression for multiple characters if required.

Dylan.

|||

Thanks Dylan...But at the same time I want to convert data type from String to Int.....

I'm supposed to get specific value for specific letters. As a example for letter B I'm supposed to get 1.

B - > 1

C- > 2

But first of all I have to figure it out how to convert data from string to Int.

Thanks

|||

Use the Lookup as Phil suggested. It is by far the simplest solution to this problem.

|||

OK, but as pointed out above, you are on a fruitless quest.

A letter is not a number, so it cannot be converted to an int.

What you are saying is like asking how mainy legs a daisy has, or how many teeth in an elephant's trunk. It just doesn't make sense to talk about converting a letter into an int. By definition an int is a number, which is not a letter.

That said, what you CAN talk about is removing the column with the letter and replacing it with a corresponding column containing an int.

Then it is just a matter of defining the rule by which you convert your letter into a number.

One such rule is to say there is a specific mapping. So B=2, F=61, R=12.

In this case the select statement above is a good way of performing that mapping. However you can't deal with unknown values. This is like saying "African elephants have 6 teeth in their trunk. Asian elephants have 9 teeth." Using this rule, you can answer the question "How many teeth in an African elephant's trunk?" but not "How many teeth in an Australian elephant's trunk?" because you have no information about Australian elephants.

The other approach is to say there is a formula, so B=2, C=3, D=4, E=5. In this case you can say that even if you have never used an "F", if one were to be used for some reason then it would be equal to 6. That is where functions like CODEPOINT can help.

This is like saying "All daisys have twice as many legs as petals." Now you can look at any daisy, and answer the question "How many legs on my daisy?"

Notice that what we have done is create two different methods for answering questions that do not make sense in the real world. And likewise, we have revealed two different methods for turning a letter into an int, even though doing so does not make sense.

You should stop thinking about turning your letter into an int, and focus instead on dropping the column and replacing it with a derived int column. You are not converting anything, you are transforming and replacing. If it looks like "converting" to your pointy-haired managers, then that's fine, but you will know better on the inside.

Good luck,

Dylan.

|||

jwelch wrote:

Use the Lookup as Phil suggested. It is by far the simplest solution to this problem.

That would depend on context and maintainability requirements.

If the letter value can be derived by formula rather than by lookup, that would be more appropriate in my opinion. If you are hardcoding values in a lookup, then you are creating a potential maintenance problem.

In addition, creating a complex union query where there are only ever two values to look up would be overcomplicated when an IF expression would be simpler.

Without knowing more about the requirements and implementation specifics, it is risky to talk about which solution is simplest. Normally I wouldn't care, but I've taken issue in this case because I've had to maintain a fair amount of work recently that was done using this "simplest" approach. Two years later, it has turned out to be a major pain.

|||

OK...Well I will try...

Thanks all

Data Conversion

This should be ez but I can't get it working;
Anyone knows how to convert a string value of '9/25/2006 4:17:12 PM'
to 'dd/mm/yyyy' and 'dd/mm/yy' format?
Db on SQL 2005.Try
set dateformat mdy
select convert(varchar(10), cast('9/25/2006 4:17:12 PM' as datetime), 103)
select convert(varchar(8), cast('9/25/2006 4:17:12 PM' as datetime), 3)
Linchi
"TBoon" wrote:

> This should be ez but I can't get it working;
> Anyone knows how to convert a string value of '9/25/2006 4:17:12 PM'
> to 'dd/mm/yyyy' and 'dd/mm/yy' format?
> Db on SQL 2005.|||testing
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:E4C66EE1-B961-41BC-B555-818C2EA41599@.microsoft.com...[vbcol=seagreen]
> Try
> set dateformat mdy
> select convert(varchar(10), cast('9/25/2006 4:17:12 PM' as datetime), 103)
> select convert(varchar(8), cast('9/25/2006 4:17:12 PM' as datetime), 3)
> Linchi
> "TBoon" wrote:
>

Data Conversion

Hello,

What will the Dataconversion do if I try to convert a source field to a datetime but the data is not a valid date?

Can I just skip the one field?

Thanks,

Michael

Hi Michael,

Most likely, you will get a conversion error. If you open the UI for the data conversion, and hit the "Configure Error Output" button, you can set the error handling behaviour on a column-by-column basis. If you just want to skip that field, you could set the error behaviour for that column to "Ignore Failure". That column will be set to NULL.

Let me know if you have further questions about this.

Thanks
Mark

Data Conversion

This should be ez but I can't get it working;
Anyone knows how to convert a string value of '9/25/2006 4:17:12 PM'
to 'dd/mm/yyyy' and 'dd/mm/yy' format?
Db on SQL 2005.
Try
set dateformat mdy
select convert(varchar(10), cast('9/25/2006 4:17:12 PM' as datetime), 103)
select convert(varchar(8), cast('9/25/2006 4:17:12 PM' as datetime), 3)
Linchi
"TBoon" wrote:

> This should be ez but I can't get it working;
> Anyone knows how to convert a string value of '9/25/2006 4:17:12 PM'
> to 'dd/mm/yyyy' and 'dd/mm/yy' format?
> Db on SQL 2005.
|||testing
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:E4C66EE1-B961-41BC-B555-818C2EA41599@.microsoft.com...[vbcol=seagreen]
> Try
> set dateformat mdy
> select convert(varchar(10), cast('9/25/2006 4:17:12 PM' as datetime), 103)
> select convert(varchar(8), cast('9/25/2006 4:17:12 PM' as datetime), 3)
> Linchi
> "TBoon" wrote:

Data Conversion

This should be ez but I can't get it working;
Anyone knows how to convert a string value of '9/25/2006 4:17:12 PM'
to 'dd/mm/yyyy' and 'dd/mm/yy' format?
Db on SQL 2005.Try
set dateformat mdy
select convert(varchar(10), cast('9/25/2006 4:17:12 PM' as datetime), 103)
select convert(varchar(8), cast('9/25/2006 4:17:12 PM' as datetime), 3)
Linchi
"TBoon" wrote:
> This should be ez but I can't get it working;
> Anyone knows how to convert a string value of '9/25/2006 4:17:12 PM'
> to 'dd/mm/yyyy' and 'dd/mm/yy' format?
> Db on SQL 2005.|||testing
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:E4C66EE1-B961-41BC-B555-818C2EA41599@.microsoft.com...
> Try
> set dateformat mdy
> select convert(varchar(10), cast('9/25/2006 4:17:12 PM' as datetime), 103)
> select convert(varchar(8), cast('9/25/2006 4:17:12 PM' as datetime), 3)
> Linchi
> "TBoon" wrote:
>> This should be ez but I can't get it working;
>> Anyone knows how to convert a string value of '9/25/2006 4:17:12 PM'
>> to 'dd/mm/yyyy' and 'dd/mm/yy' format?
>> Db on SQL 2005.

DAO to ADO conversion

Hi Techies,

We had a database MS-Access with DAO statements and we are upgrading our Database to MS-SqlServer which in need to convert the DAO statements to ADO statements.
What i want to know is there any free tool which converts automatically to convert DAO statements to ADO statements. If so what is it?

If not what is the easiest procedure to convert or otherwise should I have to do it manually convert all those DAO statements(so many). If I have to do it manually can u explain where i need to take care mainly while converting the statements.

Thank UThe Access Upsizing Wizard (http://support.microsoft.com/default.aspx?scid=kb;en-us;325017) is probably what you want.

-PatP