Post reply

Simple SSIS failed with The value could not be converted because of a potential loss of data.

  • February 22, 2017 at 9:57 am

    #320664

    I have a very simple SSIS:
    1. Source: 5 columns, all in string(50)
    2. Key two columns: Period, Val
    3. To do: get the output of Group by Period and sum the Val

    Design:

    Data Conversion:
    Column Val is converted to Float (I also tried Currency), same result

    Error message:

    [Data Conversion [24]] Error: Data conversion failed while converting column "Val COArea Crcy" (80) to column "Copy of Val COArea Crcy" (48). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

    I tried in Source option to ignore the error but result is the same;

    I tried close/re-open VS2015 and re-open the package, no difference.

    It would be appreciated if anyone can help me sort it out.

    Thank you very much in advance.

  • February 22, 2017 at 10:41 am

    #1929789

    Can you show us what is happening in the Data Conversion task, please?

    Also, include an example of the data in 'Val COArea Crcy'.

    What is the OLEDB source pointing at?

    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.

  • February 22, 2017 at 10:50 am

    #1929790

    Phil Parkin - Wednesday, February 22, 2017 10:41 AM

    Can you show us what is happening in the Data Conversion task, please?

    Also, include an example of the data in 'Val COArea Crcy'.

    What is the OLEDB source pointing at?

    Thank you.

    Conversion task:

     example of the data in 'Val COArea Crcy':

    OLEDB source:

    Top 10 of the table:

    Thank you again for your time.

  • February 22, 2017 at 1:05 pm

    #1929827

    Your problem is likely caused by a bad value in the currency field. 

    Having said that, if all you're trying to do is extratc and aggregate, why not aggregate during extraction - change your source to be a SQL query and perform the aggregation and grouping as part of that and then drop directly to the file. Using the DB engone for agrgeation will nearly always give you better performance than trying to use SSIS.

    Steve.

  • February 22, 2017 at 1:07 pm

    #1929829

    you could check for bad values by running something like 

    SELECT * FROM MyTable WHERE ISNUMERIC(currencyfield) = 0
    or if using a newer version of MSSQL, use TRY_PARSE instead.

    Steve.

  • February 22, 2017 at 2:22 pm

    #1929857

    Thank you very much.

    Yes there are a lots of bad data

  • February 22, 2017 at 2:25 pm

    #1929858

    stevefromOZ - Wednesday, February 22, 2017 1:05 PM

    Your problem is likely caused by a bad value in the currency field. 

    Having said that, if all you're trying to do is extratc and aggregate, why not aggregate during extraction - change your source to be a SQL query and perform the aggregation and grouping as part of that and then drop directly to the file. Using the DB engone for agrgeation will nearly always give you better performance than trying to use SSIS.

    Thanks for the suggestion, I understand SQL query would be much simpler in this case, the purpose of doing SSIS is to get ready to cope with complex scenario where it would be difficult to write query or a single query would be too large.

    Good point though.

  • February 22, 2017 at 2:28 pm

    #1929859

    halifaxdal - Wednesday, February 22, 2017 2:25 PM

    stevefromOZ - Wednesday, February 22, 2017 1:05 PM

    Your problem is likely caused by a bad value in the currency field. 

    Having said that, if all you're trying to do is extratc and aggregate, why not aggregate during extraction - change your source to be a SQL query and perform the aggregation and grouping as part of that and then drop directly to the file. Using the DB engone for agrgeation will nearly always give you better performance than trying to use SSIS.

    Thanks for the suggestion, I understand SQL query would be much simpler in this case, the purpose of doing SSIS is to get ready to cope with complex scenario where it would be difficult to write query or a single query would be too large.

    Good point though.

    Note that the performance of aggregation and sort components in SSIS is nowhere near that of the SQL Server database engine. Not even close.

    If your source data requires complex manipulation, consider writing a stored proc to do the work for you in T-SQL and use that as your data source.

    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.

  • February 22, 2017 at 2:35 pm

    #1929860

    Thanks for the suggestion, I understand SQL query would be much simpler in this case, the purpose of doing SSIS is to get ready to cope with complex scenario where it would be difficult to write query or a single query would be too large.

    Honestly, can't think of a single instance in ~20yrs of writing queries where the query would be 'too complex' when compared to what I'd have to do in SSIS.

    Also, not sure what you mean by'too large' - you're already bringing back several fields that you don't use, then running conversions that you don't need, to do an aggregate that could have been done right at the source. If you meant that the data set would be 'too large' then you're confused, because the dataset you're retrieving now is much larger than any set you'd get when aggregating at the source.

    Methinks this is a problem, either for school  or equivalent, that needs a particular solution. Apologies if I'm wrong, but if I am, then unless there's a TON of other stuff you're not telling us about the problem space, doing it this way in SSIS really isn't the way to go.

    Steve.

  • February 23, 2017 at 9:18 am

    #1929976

    halifaxdal - Wednesday, February 22, 2017 2:25 PM

    stevefromOZ - Wednesday, February 22, 2017 1:05 PM

    Your problem is likely caused by a bad value in the currency field. 

    Having said that, if all you're trying to do is extratc and aggregate, why not aggregate during extraction - change your source to be a SQL query and perform the aggregation and grouping as part of that and then drop directly to the file. Using the DB engone for agrgeation will nearly always give you better performance than trying to use SSIS.

    Thanks for the suggestion, I understand SQL query would be much simpler in this case, the purpose of doing SSIS is to get ready to cope with complex scenario where it would be difficult to write query or a single query would be too large.

    Good point though.

    This is the reason for staging tables. Let SSIS pull the data across then run stored procedures/T-SQL over the results to get it cleaned up for production.

  • February 23, 2017 at 9:36 am

    #1929980

    Thanks for the discussion

  • February 27, 2017 at 2:44 pm

    #1930561

    Just a quick reminded that you do have the ability to redirect those error rows to a predetermined location , like an errors table and not hold up the rest of the good data on account of this. The problem here is that you are doing aggregates before you have cleaned your data and thus this would not create the correct amounts per group. Thus I like the idea of a staging table just with data in its original form (not yet aggregated ) where you can see if all is well before proceeding with aggregations in SSIS.

    ----------------------------------------------------

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply