2

Have a large number of (TSV) files that need to be imported (periodically) (via SSIS package) into existing MSSQL DB tables. Getting many data type issues from the OLE DB Destination tasks eg:

[Flat File Source [2]] Error: Data conversion failed. The data conversion for column "PRC_ID" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

and the type suggestions from the connection managers for each table from Flat File Source tasks are not accurate enough to prevent errors when running the import package (and the DB types are the correct ones, so don't want to just make them all (wrongly) strings for the sake of loading the TSVs).

enter image description here

Is there a way to load the type data for the columns in some single file rather than one by one in the connection manager window for the Flat File Source tasks (this would be hugely inconvenient as each table may have many fields)?

I have the creation statements that were used to create each of the tables that the TSVs correspond to, could that be used in any way? Can a Flat File Source inherent data types for columns from its OLE DB Destination? Any other ways to avoid having to set each type by hand?

Hadi
  • 36,233
  • 13
  • 65
  • 124
lampShadesDrifter
  • 3,925
  • 8
  • 40
  • 102
  • 1
    Currently looking at an article here (https://www.sqlservercentral.com/articles/auto-assign-column-data-types-for-flat-file-source-import) that looks promising. Will post fuller answer if resolves. – lampShadesDrifter Oct 01 '19 at 00:53
  • Note that in the method described above, there are times where the autofilled data types are wrong (not sure why). – lampShadesDrifter Oct 02 '19 at 23:20

1 Answers1

1

There is no difference between changing columns data type from the Flat File Source, and between keeping all data types as string and linking them to OLE DB Destination (different data types). Since both methods are performing Implicit Data conversion since flat file are text files and store all data as text (columns don't have metadata).

If you are looking to automatically set the data types, I don't think there is another way to do that other than the solution you mentioned in the comments or creating package programmatically(even I don't find it useful to do it that way).

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • So when I use other tools like `bcp` from the `mssql-tools` package (https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-migrate-bcp?view=sql-server-2017), is that how `bcp` is able to write TSVs to MSSQL without asking for type information (by assuming that all of the types are strings and converting to the table's declared types when importing into the DB)? – lampShadesDrifter Oct 01 '19 at 19:53
  • 1
    @lampShadesDrifter all values are implicitly converted, if there is some values that cannot be converted an exception is thrown – Hadi Oct 01 '19 at 19:56