April 30, 2018 at 1:55 pm
Currently in my org there is some migration activity going on from sql 2012 to 2016.
So currently there is a need to upgrade the excel sheets from .xls format to .xlsx format since our target server is 64 bit.
I am only facing issue for one spreadsheet and it is for a specific column which contains null value and few other values like 3,5.
Whenever the source excel is of .xls format the ssis package execution is successful as when i preview them the blank cells are coming as null
but when i am converting that to .xlsx format and i am previewing that column value the null values are coming as blank and the package execution
is getting failed with below error :
The column status returned was: "The value could not be
converted because of a potential loss of data.".
[Excel Source ] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.
Appreciate any help on this.
April 30, 2018 at 2:06 pm
This statement is false:
So currently there is a need to upgrade the excel sheets from .xls format to .xlsx format since our target server is 64 bit.
The bitness of a server does not have anything to do with supported Excel formats. You may have to install a JET driver to read them, but that's about it.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 30, 2018 at 2:14 pm
In that case it should fail for the rest of the modified spreadsheet but it is only failing for a specific sheet , i am wondering what is the issue when i am converting the specific one to new one and why the null are coming as blank.One query here can i try executing the old .xls version in the 64 bit server.
May 1, 2018 at 12:31 am
samal.sidharth221 - Monday, April 30, 2018 2:14 PMIn that case it should fail for the rest of the modified spreadsheet but it is only failing for a specific sheet , i am wondering what is the issue when i am converting the specific one to new one and why the null are coming as blank.One query here can i try executing the old .xls version in the 64 bit server.
Please refer this,
May 1, 2018 at 8:55 am
samal.sidharth221 - Monday, April 30, 2018 1:55 PMCurrently in my org there is some migration activity going on from sql 2012 to 2016.
So currently there is a need to upgrade the excel sheets from .xls format to .xlsx format since our target server is 64 bit.
I am only facing issue for one spreadsheet and it is for a specific column which contains null value and few other values like 3,5.
Whenever the source excel is of .xls format the ssis package execution is successful as when i preview them the blank cells are coming as null
but when i am converting that to .xlsx format and i am previewing that column value the null values are coming as blank and the package execution
is getting failed with below error :The column status returned was: "The value could not be
converted because of a potential loss of data.".[Excel Source ] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.
Appreciate any help on this.
I don;t know about you, but I've never seen how you could ever import a value that contains "3,5", as any kind of number, unless your version of Excel interprets a comma as a decimal point. Assuming that the comma is indeed the decimal point (presumably, this is a European or British version), then if you try to make that an integer column, that's going to be the source of your problem. You can't have the freedom of Excel AND then import such data to SQL Server where the data type is different for different rows. Doesn't matter which version of driver you use, it's just not going to work. Your data has to conform to a single data type for each column, and no rows can deviate from the data type established by the first 8 rows. That's how Excel figures out a data type. So even if you call that column a string value, converting to integer would involve a loss of data because you lose a portion of the value of 3.5, plus you'd also have to decide how to convert that number to integer. Do you round up? round down? SSIS has no way to make that kind of choice, and thus throws the error. Bottom line here, is that you've got bad data, and I'm pretty sure SQL 2012 and SQL 2016 are unlikely to treat that differently. I'd have to see the package details to know for sure, plus I'd want to know what resulting value was passed through in SQL 2012 for that same exact spreadsheet that caused the error for SQL 2016.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 1, 2018 at 12:05 pm
sgmunson - Tuesday, May 1, 2018 8:55 AMI don;t know about you, but I've never seen how you could ever import a value that contains "3,5", as any kind of number, unless your version of Excel interprets a comma as a decimal point. Assuming that the comma is indeed the decimal point (presumably, this is a European or British version), then if you try to make that an integer column, that's going to be the source of your problem. You can't have the freedom of Excel AND then import such data to SQL Server where the data type is different for different rows. Doesn't matter which version of driver you use, it's just not going to work. Your data has to conform to a single data type for each column, and no rows can deviate from the data type established by the first 8 rows. That's how Excel figures out a data type. So even if you call that column a string value, converting to integer would involve a loss of data because you lose a portion of the value of 3.5, plus you'd also have to decide how to convert that number to integer. Do you round up? round down? SSIS has no way to make that kind of choice, and thus throws the error. Bottom line here, is that you've got bad data, and I'm pretty sure SQL 2012 and SQL 2016 are unlikely to treat that differently. I'd have to see the package details to know for sure, plus I'd want to know what resulting value was passed through in SQL 2012 for that same exact spreadsheet that caused the error for SQL 2016.
Well spotted, I missed the 3,5.
FYI, we British use 3.5, same as you 🙂
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 1, 2018 at 12:14 pm
Phil Parkin - Tuesday, May 1, 2018 12:05 PMsgmunson - Tuesday, May 1, 2018 8:55 AMI don;t know about you, but I've never seen how you could ever import a value that contains "3,5", as any kind of number, unless your version of Excel interprets a comma as a decimal point. Assuming that the comma is indeed the decimal point (presumably, this is a European or British version), then if you try to make that an integer column, that's going to be the source of your problem. You can't have the freedom of Excel AND then import such data to SQL Server where the data type is different for different rows. Doesn't matter which version of driver you use, it's just not going to work. Your data has to conform to a single data type for each column, and no rows can deviate from the data type established by the first 8 rows. That's how Excel figures out a data type. So even if you call that column a string value, converting to integer would involve a loss of data because you lose a portion of the value of 3.5, plus you'd also have to decide how to convert that number to integer. Do you round up? round down? SSIS has no way to make that kind of choice, and thus throws the error. Bottom line here, is that you've got bad data, and I'm pretty sure SQL 2012 and SQL 2016 are unlikely to treat that differently. I'd have to see the package details to know for sure, plus I'd want to know what resulting value was passed through in SQL 2012 for that same exact spreadsheet that caused the error for SQL 2016.Well spotted, I missed the 3,5.
FYI, we British use 3.5, same as you 🙂
I'm no expert on who uses commas for decimal points, but I know it;s just not on my side of the pond.. So I wasn't sure whether the UK did that or not.... Lack of exposure...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply