December 7, 2011 at 2:48 pm
I'm successfully moving all columns from staging to destination table, with one caveat. The column with datatype (decimal 15,2) is getting truncated. Instead of preserving the values after the decimal, it is applying zeroes. IE. instead of 7,317,540.72 I get this 7,317,540.00 (not zeroes)
I noticed that SSIS is converting the decimal datatype to numeric so I introduced a data conversion data flow task to switch it back to decimal. However, I am unable to edit the precision and scale and when I run the package I get the following error:
[OLE DB Destination [119]] Error: There was an error with input column "Copy of POAmountLC" (219) on input "OLE DB Destination Input" (132). The column status returned was: "The value violated the integrity constraints for the column.".
To begin with, I don't understand why SSIS is converting dec to Numeric at all. Secondly, why doesn't the conversion data task work? The percison and scale fields are uneditable.
Please, someone, tell me what the trick is to get OLE DB to let the datatype move across as decimal 15,2.
Thanks! Helen
December 7, 2011 at 4:57 pm
hxkresl (12/7/2011)
I'm successfully moving all columns from staging to destination table, with one caveat. The column with datatype (decimal 15,2) is getting truncated. Instead of preserving the values after the decimal, it is applying zeroes. IE. instead of 7,317,540.72 I get this 7,317,540.00 (not zeroes)
Can you give us a bit more detail about the data source? This sounds like there's an accidental int conversion somewhere in the metadata.
I noticed that SSIS is converting the decimal datatype to numeric
I wouldn't worry so much about this. Decimal/Numeric are the same basic datatype with different names. SSIS treats them a hair differently but Decimal is merely more limited.
The particular differences can be found here: http://msdn.microsoft.com/en-us/library/ms141036(v=SQL.90).aspx
Here's a TechNet article showing legal conversions: http://technet.microsoft.com/en-us/library/ms141704.aspx
What is the scale originally listed as for the incoming decimal type in the metadata? I assume it's (DT_Decimal,2) but it's good to confirm. Might be the truncation cause if it's going 28,0. Decimal is always Precision 28.
so I introduced a data conversion data flow task to switch it back to decimal.
At this point the damage is most likely already done. I'd be curious what the data-viewer showed as the current value directly off the source. It'll help determine if its the source definition (or data) or the SSIS causing the problem.
However, I am unable to edit the precision and scale and when I run the package I get the following error:
[OLE DB Destination [119]] Error: There was an error with input column "Copy of POAmountLC" (219) on input "OLE DB Destination Input" (132). The column status returned was: "The value violated the integrity constraints for the column.".
Now, that's interesting. Copy of POAmountLC shouldn't exist yet, not at the input. Can you describe the dataflow in more detail?
To begin with, I don't understand why SSIS is converting dec to Numeric at all.
Where exactly are you seeing this conversion?
Secondly, why doesn't the conversion data task work? The percison and scale fields are uneditable.
If you're using 'replace column' it needs to stay the same datatype. What you need is a new column to change the data type. But this sounds more like you used a derived column component instead of a Data Conversion component. Even re-using the same name in the data conversion component allows me to edit the numeric values.
Please, someone, tell me what the trick is to get OLE DB to let the datatype move across as decimal 15,2.
Thanks! Helen
There really isn't one. Numeric(15,2) is the equivalent of SQLServer Decimal(15,2) and Numeric(15,2). Decimal will just give you more significant places on the left in precision. There is unfortunately no Decimal(15,2) in SSIS, merely Decimal(2).
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 7, 2011 at 10:39 pm
Yes, thanks for replying.
the data source is a SQL staging table which contains the column that is needed in the target table. This particular package is bringing over other columns as well, but the only column throwing the error, is the decimal 15, 2.
I don't know what the difference is between these two questions:
What does the data-viewer show as the current value directly off the source AND
What is the scale originally listed as for the incoming decimal type in the metadata.
I think the answer to both is DT_Numeric 15, 2. This is the value that I see when I expand the External column folder within the Input/Output Properties tab of the source OLE DB data task.
The exact dataflow is as follows:
OLE DB Source -> Derived Column -> OLE DB destination.
It's in the Derived Column task that I am creating a copy of POAmountLC in order to handle occassional NULL values.
Does this shed any new light? Thanks Craig, Helen
December 7, 2011 at 10:52 pm
Here's whats exactly what is coming in and going out at each data flow task.
OLE DB Source
external column numeric [DT_NUMERIC] 15, 2
output column numeric [DT_NUMERIC] 18,0
Data Conversion
input column numeric [DT_NUMERIC] 18,0
output column numeric [DT_NUMERIC] 29, 2
OLE DB Destination
input column numeric [DT_NUMERIC] 15, 2
external column numeric [DT_NUMERIC] 29, 2
December 8, 2011 at 12:44 am
hxkresl (12/7/2011)
Here's whats exactly what is coming in and going out at each data flow task.OLE DB Source
external column numeric [DT_NUMERIC] 15, 2
output column numeric [DT_NUMERIC] 18,0
Data Conversion
input column numeric [DT_NUMERIC] 18,0
output column numeric [DT_NUMERIC] 29, 2
OLE DB Destination
input column numeric [DT_NUMERIC] 15, 2
external column numeric [DT_NUMERIC] 29, 2
can you post what exactly the error you are getting..? i am not sure of your above post..
Thanks,
Charmer
December 8, 2011 at 7:24 am
Hi Charmer,
Yes, the error is:
[OLE DB Destination [119]] Error: There was an error with input column "Copy of POAmountLC" (219) on input "OLE DB Destination Input" (132). The column status returned was: "The value violated the integrity constraints for the column.".
in summary, I am pulling from sql staging and moving to sql destination. Data type specified in both tables is exactly same, decimal 15,2.
December 9, 2011 at 1:02 am
hxkresl (12/8/2011)
Hi Charmer,Yes, the error is:
[OLE DB Destination [119]] Error: There was an error with input column "Copy of POAmountLC" (219) on input "OLE DB Destination Input" (132). The column status returned was: "The value violated the integrity constraints for the column.".
in summary, I am pulling from sql staging and moving to sql destination. Data type specified in both tables is exactly same, decimal 15,2.
mmmmmm...well, in this case i would ask you to check for the same error which i had asked on the other topic that you posted... :-)..do you remember, buddy..?
Thanks,
Charmer
December 9, 2011 at 9:03 am
Well, that there was a smokescreen and it is now being handled (ahem, null). Actually, I am puzzled why I have to handle the nulls between staging and destination, which I do as follows
ISNULL(POAmountLC) ? 0 : POAmount
since I've already handled them during import from excel, but it doesn't seem germaine to the decimal point issue.
But, but the original problem exists, which is that while SSIS project flows green end to end with no error now, I am still finding the column with decimal value truncating.
My staging table, which is being populated with SSIS data, contains all the correct datatypes and is scrubbed by the time I do what I think is the very last thing, which is to move data from staging to destination.
Even though staging table has decimal 15, 2 not null, and retains values after the decimal point (7317540.71) during import from excel, once subjected to export to the destination sql table it loses them (7317540.00)!
My control flow for that part is:
OLE DB -> Derived Column (to handle nulls for the column in question) -> Data Conversion (where I select Decimal datatype with precision 2, I have also tried Currency) ->OLE DB
Like I said, the flow is green end to end now. Any other ideas?
December 10, 2011 at 8:05 am
hxkresl (12/9/2011)
Well, that there was a smokescreen and it is now being handled (ahem, null). Actually, I am puzzled why I have to handle the nulls between staging and destination, which I do as followsISNULL(POAmountLC) ? 0 : POAmount
since I've already handled them during import from excel, but it doesn't seem germaine to the decimal point issue.
But, but the original problem exists, which is that while SSIS project flows green end to end with no error now, I am still finding the column with decimal value truncating.
My staging table, which is being populated with SSIS data, contains all the correct datatypes and is scrubbed by the time I do what I think is the very last thing, which is to move data from staging to destination.
Even though staging table has decimal 15, 2 not null, and retains values after the decimal point (7317540.71) during import from excel, once subjected to export to the destination sql table it loses them (7317540.00)!
My control flow for that part is:
OLE DB -> Derived Column (to handle nulls for the column in question) -> Data Conversion (where I select Decimal datatype with precision 2, I have also tried Currency) ->OLE DB
Like I said, the flow is green end to end now. Any other ideas?
if i am not wrong, your problem resides on the data's after the decimal points right..? in that case just go for a try of changing the source data type to double precision in the data conversion transformation...
Thanks,
Charmer
June 23, 2012 at 2:00 pm
Charmer (12/10/2011)
hxkresl (12/9/2011)
Well, that there was a smokescreen and it is now being handled (ahem, null). Actually, I am puzzled why I have to handle the nulls between staging and destination, which I do as followsISNULL(POAmountLC) ? 0 : POAmount
since I've already handled them during import from excel, but it doesn't seem germaine to the decimal point issue.
But, but the original problem exists, which is that while SSIS project flows green end to end with no error now, I am still finding the column with decimal value truncating.
My staging table, which is being populated with SSIS data, contains all the correct datatypes and is scrubbed by the time I do what I think is the very last thing, which is to move data from staging to destination.
Even though staging table has decimal 15, 2 not null, and retains values after the decimal point (7317540.71) during import from excel, once subjected to export to the destination sql table it loses them (7317540.00)!
My control flow for that part is:
OLE DB -> Derived Column (to handle nulls for the column in question) -> Data Conversion (where I select Decimal datatype with precision 2, I have also tried Currency) ->OLE DB
Like I said, the flow is green end to end now. Any other ideas?
if i am not wrong, your problem resides on the data's after the decimal points right..? in that case just go for a try of changing the source data type to double precision in the data conversion transformation...
June 23, 2012 at 2:01 pm
SJanki (6/23/2012)
Charmer (12/10/2011)
hxkresl (12/9/2011)
Well, that there was a smokescreen and it is now being handled (ahem, null). Actually, I am puzzled why I have to handle the nulls between staging and destination, which I do as followsISNULL(POAmountLC) ? 0 : POAmount
since I've already handled them during import from excel, but it doesn't seem germaine to the decimal point issue.
But, but the original problem exists, which is that while SSIS project flows green end to end with no error now, I am still finding the column with decimal value truncating.
My staging table, which is being populated with SSIS data, contains all the correct datatypes and is scrubbed by the time I do what I think is the very last thing, which is to move data from staging to destination.
Even though staging table has decimal 15, 2 not null, and retains values after the decimal point (7317540.71) during import from excel, once subjected to export to the destination sql table it loses them (7317540.00)!
My control flow for that part is:
OLE DB -> Derived Column (to handle nulls for the column in question) -> Data Conversion (where I select Decimal datatype with precision 2, I have also tried Currency) ->OLE DB
Like I said, the flow is green end to end now. Any other ideas?
if i am not wrong, your problem resides on the data's after the decimal points right..? in that case just go for a try of changing the source data type to double precision in the data conversion transformation...
Hello
I am having the same issue. Were you able to resolve your problem?
Thanks!
June 25, 2012 at 6:48 am
Can you show me an example of how exactly the output that you need?...
So that i can think of it....so help me to help you..
Thanks,
Charmer
June 25, 2012 at 8:24 am
Charmer (6/25/2012)
Can you show me an example of how exactly the output that you need?...So that i can think of it....so help me to help you..
Hello, The problem was resolved.
In the Data-conversion transformation-->(Advance Editor) Input Output , I fixed the Scale which was initially blank to 2 (in the Output data)
and this fixed the issue!
Thanks!
Janki
June 25, 2012 at 8:37 am
SJanki (6/25/2012)
Charmer (6/25/2012)
Can you show me an example of how exactly the output that you need?...So that i can think of it....so help me to help you..
Hello, The problem was resolved.
In the Data-conversion transformation-->(Advance Editor) Input Output , I fixed the Scale which was initially blank to 2 (in the Output data)
and this fixed the issue!
Thanks!
Janki
Cool 🙂
Thanks,
Charmer
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply