Trouble with Pivot Task (multiple problems) - Long

Errsomeone


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

Jamie Thomson

Problem 1:

What is "a set of data"

What does the data look like

What's the error message for that error code

-Jamie

Errsomeone



Jamie,

A set of data is the group that will be selected to make the first pivoted row.

So if the columns from the view that is queried exist as:

Site ID | Pivot ID | Pivot Value | Active Flag
251 | 1001 | Yes | Yes
251 | 1002 | 15 | Yes
...
251 | 2500 | 19 | Yes
333 | 1001 | Yes | No
333 | 1002 | 40 | No
...
333 |2500 | 71 | No

The pivot output would be:
251 | Yes | 15 | ... | 19 | Yes
333 | Yes | 40 | ... | 71 | No

The Error message for this code is: "Unrecognized pivot key value." However, how could any value be unreconized if 1) the pivot actually works as expected 2) The query from the view is set right now to limit data returned (pivot id's) to those that are known and mapped in the pivot task.

If I run the pivot on 1-2 sites all works as expected with no errors.
If I run the pivot on 3-4 sites all works as expected WITH redirect error output for all sites
If I run the pivot on 6+ sites all fails with no redirect noting how "isErrorOut" is not set.

All data for all sites tested has been verified as the same. So there are no unknown pivot keys being returned, no duplicates (except for output, which is another problem).

Thanks.

Errsomeone



All,

I would not really call this a fix, but in case anyone has similar problems...

I have used a for each loop to feed the data flow task one row to pivot at a time. All rows pivot and write to the destination table without any errors being reported.

Why would pivot fail on multiple expected output rows when each is able to successfully process While It would be nice to get feedback regarding the original problems, since I found a work around this is successfully completing.

Thanks.

Jamie Thomson

Errsomeone wrote:

Site ID | Pivot ID | Pivot Value | Active Flag 251 | 1001 | Yes | Yes 251 | 1002 | 15 | Yes ... 251 | 2500 | 19 | Yes 333 | 1001 | Yes | No 333 | 1002 | 40 | No ... 333 |2500 | 71 | No The pivot output would be: 251 | Yes | 15 | ... | 19 | Yes 333 | Yes | 40 | ... | 71 | No

So are you telling me that there could be an arbitrary list of values in the PivotValue field You have to know in advance what the (finite) list of possible values is because the list of columns on the output has to be finite and known in advance as well. Each pivot value has to be mapped to a column in the output.

-Jamie

Errsomeone

Jamie,

There is a finite set known about... these are roughly ids 1001 - 1459 (with a few blocks skipped). The actual treatment of the data may be diffferent, but that would be post pivoting since all values to pivot are sourced as strings. The pivot task contains all mappings of these values. For example 1001 will be known as something like "Question" and that value 1001 and mapping will ALWAYS be available for EVERY site refernced.

So ALL sites have the same finite number of values being returned with the same id mapping information. It is currently impossible that the pivot task could receive any duplicated data (DB does not contain any) or ID's not mapped (ensured by query filter).

The pivot transformation ALWAYS works when any one (1) site data set is pivoted at a time. However, the errors begin really being problematic when pivoting 3+ at a time. Please reference the origional list of problems for more information regarding increasing the data set being used as the errors produced are very specific and always occur as I add in more data to create more rows expected to be returned from the pivot task.

At the moment, I am working around this problem with a for each loop in the control flow passing in one site at a time to the data flow task.

Thanks.

Jamie Thomson

Hi,

Hmm...problematic it seems. I wish I could pick through this to see what the problem is but the only way really is to be sittig at your machine with you. I'll try re-reading it all again later but I can't promise anything.

Sorry i can't be of more help.

-Jamie

Errsomeone

Ok,

How about a more direct problem... I have been using the workaround loop package version.

The data flow path is:
1) DB query
2) Pivot
3) Data conversion
4) Calculations
5) DB insert ( different db than source )

I have data viewers on:
1.1) the db query (1)
2.1) the pivot output (2)
2.2) the pivot error ouput (2)

I see 371 rows passed from the db query (1) to the pivot (2). The pivot task (2) passes 372 rows to the error output.

How could the pivot replicate a row

There are not any dupliations of the key value (I checked the XML).

The other thing I do not get is that running this in two different enviroments yelds different results with the same data and same versions of Visual Studio. Running locally all works and reports successful (with loop workaround) without any errors or duplication... Running remotly on a test server reports one error per pivot and writes out one duplicated row to the output but still pivots all rows and inserts them into the destination as if no error occured.

What are the most up-to-date product versions

Any thoughts