Post reply

SQL server 2014 and VS 2013 - Date Column Error and Error handling for Derived column

  • June 8, 2015 at 3:05 am

    #148748

    Hello Everyone,

    At present, I'm facing two problems in current SSIS package.

    1. Date Column Error.

    I'm using Data Flow Task to migrate data from CSV files to SQL tables, one by one using For each loop container for multiple CSV files.

    This package is throwing following error when data in date column is null.

    [OLE DB Destination [793]] Error: There was an error with OLE DB Destination.Inputs[OLE DB Destination Input].Columns[Created Date] on OLE DB Destination.Inputs[OLE DB Destination Input]. The column status returned was: "Conversion failed because the data value overflowed the specified type."

    On the other hand, the same is working well with DateTime type.

    Also, it is working fine with Date Type when I process a file separately in a different package.

    This is really weird and I am unable to understand this.

    Can anybody please shed some light on this problem?

    2. Error handling/Log for Derived column

    I'm using Data Flow Task to migrate data from CSV files to SQL tables, one by one using For each loop container for multiple CSV files.

    I would like to log the erroneous records in a separate file at data flow task.

    Say for instance, for numeric (10,2) column, if data in CSV file has value beyond this range, like 98.95784736264, then this particular record needs to be logged in a separate file and the package should process the next record.

    I’ve tried to implement it over Derived Column component, but I’m facing the following error while doing so.

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

    Please advise how to get rid of this problem.

    Any help would be much appreciated.

    Thanks,

    Ankit

  • June 8, 2015 at 7:29 am

    #1803673

    1. Try using the ReplaceNull function in your derived column

    2. Set the error handling and truncation for that derived column to Redirect Row, and then give the Derived Column transform an failure path to another file.

  • June 9, 2015 at 4:40 am

    #1803895

    Hello SSCrazy,

    Thank you for the response.

    Please know, I've resolved both these problems. Following are solutions,

    1. I've resolved the first problem by selecting an option highlighted in following screen print.

    2. The error output is resolved by setting up the error output property of Flat file

    source and Flat file destination components as “Redirect Row””

    Now, after resolving these two problems, I'm facing couple of more issues.

    1.The package creates empty log file even though the file does not contain any log records.

    2.The log file which has records in it, does not contain the header row.

    Can you please advise how to get rid of this problems?

    Thanks,

    Ankit

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

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