Search code examples
sql-serverdatabasedatessisderived-column

SSIS Foreach loop error on CSV files

Right, so I have a foreach loop with a data flow inside going through a group of files with all the exact same format. This changes a few things with a derived column which is all getting dumped into a SQL Server Database which will become my staging table.

The problem is that some files throw up an error even though the files are all formatted identically, the error is always around the date. It will go through 4 files with no problem and then on the 5th file it stops working.

What am I trying to do?

  • I want to get a whole load of files with the same format and put the data from within into an SQL database while changing some formats.

What have I tried to do?

  • I tried to reformat the date to be dd/mm/yyyy which is the format I want the date to have anyway. The reformatting worked but then on the same file that had errors before it came up with a type cast error.

This is the error I get: [OLE DB Destination [59]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Invalid character value for cast specification".

[OLE DB Destination [59]] Error: There was an error with OLE DB Destination.Inputs[OLE DB Destination Input].Columns[Amount] on OLE DB Destination.Inputs[OLE DB Destination Input]. The column status returned was: "The value could not be converted because of a potential loss of data.".

[OLE DB Destination [59]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "OLE DB Destination.Inputs[OLE DB Destination Input]" failed because error code 0xC0209077 occurred, and the error row disposition on "OLE DB Destination.Inputs[OLE DB Destination Input]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

*[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Destination" (59) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (72). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. *

It's happened before and I got around it by creating a new foreach loop to handle the new files with the new format (I could not see any change in the format but did it to continue).

This is the format for the Data Flow: enter image description here

This is the image of the Derived Column enter image description here

Any help would be greatly appreciated! if you need me to clarify anything just let me know.

Solution

  • Your columns appear to be in different orders. I replaced the tabs with pipes and got the following:

    Working:

    Staffordshire County Council|Staffordshire County Council Other|247 Cars Willenhall Ltd 15/06/2017|1126.97|Transport - Escorts|Transport - Escorts|opendatacommunities.org/id/county-council/staffordsh‌​ire

    Not Working:

    Staffordshire County Council|Childrens Services SEND|247 Cars Willenhall Ltd|273.42|06/07/2017|Transport - Escorts|Transport - Escorts opendatacommunities.org/id/county-council/staffordsh‌​ire

    In the first one the date is on the other side of the amount, and appears to be included in the "247 Cars Willenhall Ltd" string.

    If you enable the Data Viewer by right-clicking the arrow between the last two components and selecting the option, you'll get a clear view of how this is affecting your data flow (while running/debugging the package).