0

This is a table from our PSA database from a flat file. Database table our school gave us

This is the flat file: flat file

We our trying to fill our ODS database with the data from the PSA database. This is the design of the container table from our ODS database: ods

We are using a data conversion in visual studio sql server data tools like this: ssdt

This is what the data conversion looks like: data conversion

The error we are getting is this:

[Data Conversion [2]] Error: Data conversion failed while converting column "PricePerKm" (85) to column "Copy of PricePerKm" (30).  The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
kjwdamme
  • 289
  • 4
  • 13
  • 2
    Personally, I avoid doing transformation in SSIS; it's quirky. Have you tried following a ELT method; thus loading your data as is (as a `varchar`) into a staging table and then transforming the data, using T-SQL, into your production table. – Thom A Apr 04 '18 at 11:54
  • 1
    What is the collation / locale setting of your database? I wonder if the data using commas for a decimal point is confusing it. – Tab Alleman Apr 04 '18 at 13:49
  • In the same vein what is the locale setting on the flat-file connection manager? See this maybe: https://stackoverflow.com/questions/14647532/how-to-import-a-flat-file-without-changing-regional-settings-on-the-deployment-s – Mike D. Apr 04 '18 at 13:54
  • 1
    First, remove the `$` and `,` from your fields before converting it. second _don't_ use `float`, use `decimal`. Lastly, since you already have the file in a table, don't use SSIS to transform it, use T-SQL – Nick.Mc Apr 04 '18 at 13:54
  • Yeah, actually. You're using an OLE DB source and not a flat file. – Mike D. Apr 04 '18 at 13:56
  • I used a flat file to load the data in a PSA database and now im trying to load the data into the ODS from the PSA – kjwdamme Apr 04 '18 at 14:01
  • 1
    Watch out Nick. I had to delete my answer with that same logic in it because of negative feedback – KeithL Apr 04 '18 at 14:16
  • It's OK I'm used to my advice being ignored on here – Nick.Mc Apr 05 '18 at 01:54
  • I think your source column length(85) and destination column length(30) are not equal – Yashar Aliabbasi Apr 07 '18 at 10:48

0 Answers0