Trouble With Pivot Task (multiple Problems) - Long

All,

I am having difficulty with an SSIS package that is simply to pivot a table, and perform calculations used in reporting.

Background:
A sample set of data is being tested to pivot for reporting purposes. The sample set of data being used is 2226 rows that will pivot to 6 rows having nearly 400 columns.

Problem 1:
This all seems quite simple except that when I try to pivot on one set of data it works perfectly, I try two at a time, again great. However, as I add three or more groups of data from a view (thus creating 3+ pivoted rows) SSIS throws an error of "-1071636293" and points the to the column containing the values to be pivoted. The error output dumps ALL source data rows with the same error code and message, even on data groups that were successfully pivoted and written to the destination DB all with correct mappings (including the noted with the error code)

Running the package on each group of data one set at a time, creating one pivot output row, always works for all data sets without throwing any errors. How is it possible that SSIS would run perfectly on each data set creating one row without any problems/errors/warnings, but when when run on all data at the same time it would fail?

Problem 2:
When running on multiple sets of data creating many pivoted output rows, the error output viewer and log file contain duplicated data rows from the original query. For example if I received the row the pivot id 1001 ONCE in the query from the DB as it is written out to the error file and viewer I see this ID TWICE for each data group. So if each data set were to contain 100 codes to match and I ran this on three data sets, creating an original 300 rows from the db query, the error file and viewer will have 303 rows. This occurs for only different ID's in depending on the data set, but one ID in particular is duplicated for ALL. How could a row be duplicated in the error output in one case but not another when the source data does not contain ANY duplicates?

Problem 3:
When running the package for three sets of data SSIS will note an error and redirect the row to the file I specified with no problem. However, as I run it on 4 or more data sets that cause more errors I receive the message: "The buffer manager attempted to push an error row to an output that was not registered as an error output. There was a call to DirectErrorRow on an output that does not have the IsErrorOut property set to TRUE." Why would the error logging work for one failure but have problems with more?


I am monitoring the package execution by:
1) logging all available output from SSIS to a file.
2) redirecting all errors from the pivot to a file which works only on the small data sets where I am expecting 1-2 rows to be the pivot result.
3) Placing data viewers on all directional arrows from original db query and pivot


Finally, even with all the errors or warnings all data is pivoted as desired and inserted into the destination database as if nothing was wrong, sometimes. Same package, same data, same errors, but delaying the package with breakpoints causes SSIS to either be successful or fail. I'll question that one later as I gather more information.

Any help on one or more of these questions would be a great help...

Thanks.







-1071636293









-1071636293