Post reply

Error loading multiple CSV files in SSIS

  • November 7, 2024 at 7:49 am

    #4478335

    Hi everyone

    I have a bunch of CSV files that I need to bulk insert into a table.  I saw this video on YouTube and implemented it.  It works for some files but not for others.

    https://www.youtube.com/watch?v=1vM9s7OxnTM

    Basically, the approach from the video is to use a multi flat file connection manager to handle loading multiple flat CSV files from a folder.  I am getting below error.  How do I fix it?

    Error:

    Error: 0xC02020A1 at Update Table Stocks, Stock CSV Files [12]: Data conversion failed. The data conversion for column "NAME" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
    Error: 0xC020902A at Update Table Stocks, Stock CSV Files [12]: The "Stock CSV Files.Outputs[Flat File Source Output].Columns[NAME]" failed because truncation occurred, and the truncation row disposition on "Stock CSV Files.Outputs[Flat File Source Output].Columns[NAME]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
    Error: 0xC0202092 at Update Table Stocks, Stock CSV Files [12]: An error occurred while processing file "C:\Users\Me\Documents\Trading\Trading_Data\Stock_Prices\ADAP.O#Adaptimmune Therapeutics ADR Representing 6 Ord Shs (D).csv" on data row 2.
    Error: 0xC0047038 at Update Table Stocks, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on Stock CSV Files returned error code 0xC0202092.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

    File:

    SYMBOL,INTERVAL,NAME,DATE,OPEN,HIGH,LOW,CLOSE,VOLUME
    ADAP.O,D,Adaptimmune Therapeutics ADR Representing 6 Ord Shs,2019-09-03 12:00:00 AM,1.6,1.6401,1.51,1.56,298735
    ADAP.O,D,Adaptimmune Therapeutics ADR Representing 6 Ord Shs,2019-09-04 12:00:00 AM,1.59,1.66,1.55,1.65,395428
    ADAP.O,D,Adaptimmune Therapeutics ADR Representing 6 Ord Shs,2019-09-05 12:00:00 AM,1.68,1.8,1.62,1.66,214112

    The SS table that gets the imported data is below (ie these are the only fields I need):

    CREATE TABLE [dbo].[Stocks](
    [SYMBOL] [nchar](10) NOT NULL,
    [TRADE_DATE] [date] NOT NULL,
    [OPEN_PRICE] [numeric](12, 5) NOT NULL,
    [HIGH_PRICE] [numeric](12, 5) NOT NULL,
    [LOW_PRICE] [numeric](12, 5) NOT NULL,
    [CLOSE_PRICE] [numeric](12, 5) NOT NULL,
    [VOLUME] [numeric](18, 0) NOT NULL
    ) ON [PRIMARY]
    GO

    Thank you

    • This topic was modified 2 hours, 37 minutes ago by  water490.

  • November 7, 2024 at 9:23 am

    #4478356

    OK, those errors are actually pretty helpful.

    There must be one or more occurrences of the column NAME in file

    ADAP.O#Adaptimmune Therapeutics ADR Representing 6 Ord Shs (D).csv

    where the data is longer than what is being handled by the package.

    Either the target table's column is too short, or the configured max length of NAME within the package is too short.

    Another possibility is that  the data coming in is non-VARCHAR, but you have your package and target table configured to work with VARCHAR.

    • This reply was modified 1 hour ago by  Phil Parkin. Reason: Fix typo

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

Viewing 2 posts - 1 through 1 (of 1 total)

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