ssis
I've a SSIS Data Flow Task. Within which i've souce flat file and OLEDB destination. I've an error output sent to redirect row on OLEDB Destination.
The problem is that the redirect row always run even though there's no error. Is this a bug with SSIS package? If someone could help, will be appreciated.
e.g.
You've come across a fairly well-known problem. When SSIS is trying to import the data, it provides locale-aware conversions and the format YYYYMMDD doesn't convert nicely. You can use the "import as string and convert to actual datetime" approach you've outlined in your solution but that's slower and consumes more resources than using the native approach of telling SSIS to quit being so damn smart.
Right click on your flat file source and select the Show Advanced Editor. In the Input and Output Properties, expand Output Columns, find your column (BirthDate) and change the FastParse property from False to True.
With only that change, the package will execute successfully
Also, an excellent answer from SO on the same issue
And so to force the same session to be used, in your connection manager, under the Properties settings, you can set the RetainSameConnection to True to force a single connection for your package versus using a pool.
Best Answer
I am looking for the article that describes this behaviour better than I'll do here and will update if I can find it. I thought it was by Kirk Haselden or John Welch but no so luck.
If the batch size is set to 0, default, either the entire data flow will make it in or none of it. Generally, this is what you want as it gives the best performance and consistency.
If you have an exception in in the commit size (full or otherwise) and have set redirect error rows, the error row(s) and all the valid ones will all be mingled in the exception flow.
If your desire is to get all to "good" rows in and spit the "bad" ones to a staging area, the pattern described in the article is to try and commit successively smaller batches into the destination table. Eventually, you'll partition the inserts down to a valid sized batch or find the offending row(s) which are then stored to a staging area for review/cleanup.
The implementation is to have serial error redirect OLE DB destinations with decreasing rows per batch commit sizes, starting at say 10,000 and having the penultimate one with a size of 1. The last OLE DB destination would be to the staging/error table.
All of this assumes that you're using "fast load" version for the OLE provider. If you're "table or view" or "table or view from variable" you're already doing singleton inserts (and probably killing your performance) so the redirect stuff isn't needed to find a bad row.