Programmatically configuring error and truncation dispositions for row redirection

Hi,

I have created a SSIS package programmatically using C#.

The package should do the following take data from source A, and place rows into destination B, if there are any error rows then redirect the rows to destination C. In my package I have the following components:

DTSAdapter.OLEDBSource.1 - Used as the Source

DTSAdapter.OLEDBDestination.1 - Used for the Destination Output - (let me call this normalOutput)

DTSAdapter.OLEDBDestination.1 - Used for the Destination Error Output - (let me call this errorOutput)

All my mappings appear to be correct, I build and save the package and receive a Successful validation and Success on Execution.

However, When I open the application using the Execute Package Utility I get the warning:

Warning:No rows will be sent to the error output(s). Configure error or truncation dispositions to redirect rows to the error output(s), or delete data flow transformations or destinations that are attached to the error output(s)

How do I get around this?

I have placed on the DTSAdapter.OLEDBDestination.1 (Used for the Destination Output), on the input collection I have placed:

normalOutput.InputCollection[0].ErrorRowDisposition = DTSRowDisposition.RD_RedirectRow;

normalOutput.InputCollection[0].TruncationRowDisposition = DTSRowDisposition.RD_RedirectRow;

normalOutput.OutputCollection[0].ExclusionGroup = 1;

on the DTSAdapter.OLEDBDestination.1 (Used for the Destination Error Output) I have placed:

errorOutput.OutputCollection[0].ExclusionGroup = 1;

However this does not work, I just get the wanring displayed above.

I have also tried to set the

OutputCollection[0].SynchronousInputID for both the error output and the normal output to the same values

so that:

normalOutput.OutputCollection[0].SynchronousInputID = normalOutput.InputCollection[0].ID

errorOutput.OutputCollection[0].SynchronousInputID = normalOutput.InputCollection[0].ID

However, the above scenario does not pass the package validation, in the Execute Package Utiltity, I get the wanring mentioned above and also the error:

Error: The input "OLE DB Destination Input" (16) has an invalid error or truncation row disposition.

So my question is what are the correct configuration settings to have in this scenario?

Thanks

Just looking at your code, I see a couple things to suggest. Where you are setting the Error and Truncation dispositions to redirect, I think that needs to be done on every item in the OutputCollection and not on the InputCollection. I also think you should not be setting anything for the ExclusionGroup.

I suggest you mock up what you're trying to create manually in BIDS and look at the Advanced Editor for OLE DB to get an idea for what properties need to be set where.

Also, in the beginning of your message you listed the same component for the normal and error outputs. Was that a typo?
|||

Thanks JayH, I ended up setting the ErrorRowDisposition to redirect on the InputCollection[0] of the NormalDestination (OLE DB Destination) and then creating an error path from the NormalDestination to the ErrorDestination (OLE DB Destination) .

This works for errors such as trying to place a varchar(20) column into a float column, but for some reason you are not allowed to set the TruncationRowDisposition to redirect at this level.

So, I went ahead a created a package on the VS IDE and set the TruncationRow dispositions to redirect on every column in the Source (OLE DB Source), and made a path from the error output to a ErrorDestination (OLE DB Destination).

and then... nothing...no redirection, when running the package using the Execute Package Utility, I see the warnings that a truncation will occur, but it just goes ahead and truncates the data in the column and placing the row into the NormalDestination and I get no rows redirecting to my ErrorDestination.

Is there something I am missing? Should be using a transformation component?

|||I think I'm missing the bigger picture. I'm not sure how a transformation component could help you. I'm imagining a single source OLE DB and two destination OLE DBs, one "normal" and the other "error".

You are correct that you can't set truncation disposition on an OLE DB destination. You can set an error disposition to redirect, but only if you're not fastloading. If there is an error when fastloading, the entire load will fail.

Your pipeline metadata should have the correct column definitions, and they should match your destination. Thus you should have no truncations or type mismatches detected at the destination. The place to detect truncations is on the source when the data is read and put into the pipeline. The only errors that should get detected at the destination are constraint violations.

If you'd like to send me code, my email is jay underscore hackney at hotmail dot com.
|||

No your spot on that is the final setup I had on Friday.

Point to note: I currently have my MaxInsertCommitSize set to 1000 which allows the fast loading.

Now back to the matter at hand, so, my package now has all the output columns on the source have both the ErrorRowDispositions and TruncationRowDispositions set to redirect and I've removed the ErrorDisposition from the "nomal" destination.

Now when I run the package I receive a truncation error, it should then perform the redirect but no rows are inserted and I get the following error message:

Error: There was an error with input column "City" (109) on input "OLE DB Destination Input" (16). The column status returned was "Text was truncated or one or more characters had no match in the target code page"

Let me break down the City column:

On the OLE DB Source is varchar(50)

On the "normal" destination is varchar(20)

On the "error" destination is varchar(MAX)

I want the truncation error to occur and redirect into the "error" destination where it should be inserted without any issues.

|||Regarding the MaximumInsertCommitSize, this does not enable any type of error redirection when fastloading. All it means is that instead of your whole load failing due to an error, only that 1,000 row chunk containing the error fails, and the rest of the load is considered successful.

Everything you're describing sounds correct to me. Have you saved the programmatically generated package and viewed it in the IDE to verify that everything was created correctly? Have you set the IsErrorOut on your error output?

|||Sorry. Have to correct myself. The failed chunks do get redirected.
|||

Thanks for the information on the fastloading, for the time being I have switched it off...

Regarding the the IsErrorOut field on the error output that is... (source.OutputCollection[1].IsErrorOut), it is already set to true by the framework.

Yes, the package is saved and i've looked at it in the IDE to check it and everything is fine:

However on executing the package I am left with:

Error: An OLE DB error has occurred. Error code 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Descriptioni "Invalid character value for cast specification" Information: The final commit for the data insertion has ended.

Error: There was an error with input column "City" (108) on input "OLE DB Destination Input" (29). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".

Error: the "input "OLE DB Destination Input" (29)" failed because error code 0xC0209078 occurred, and the error row disposition on "input "OLE DB Destination Input" (29)" specifies failure on error. An error occurred on the specified object of the specified component.

The strange thing is the "OLE DB Destination Input" the error refers to in the last line belongs to the "source" component. However, this should be left to "Fail component", because the redirections have been set on the output items.

Looking at the External and Input columns, on the error destination the DataTypes for the External columns are DT_TEXT and on the Input columns they match the type and length of the "normal" Destination OutputColumns which seems correct to me.

I'm at a loss....

Can you advise of any good books on programming SSIS (and handling error outputs!) using C#?

|||

Ok, I remade the new package with the VS IDE, with only one mapping on the City column.

I map the "source" city (varchar(50)) to "destination" city (varchar(5))

I map the "source" city (Error output - varchar(50)) to "error destination" city (varchar(MAX))

I set the the errorRow and truncationRow dispositions on the "source" on the city column to RedirectRow.

I then run the package and get the warning that a truncation could occur on the column city, but the package just runs through and places all the rows into the destination table, no rows are redirected - (Note that there are is data in the source which should be redirected).

Looking at the "destination" all the truncations have gone ahead leaving me with 5 character strings in the City column.

Surely I must be doing something wrong here?

|||No books that I know of. Darren probably has the most experience doing this type of stuff, but he apparently hasn't seen fit to comment.

Are you controlling your source data for the testing? Do you know that there aren't some invalid characters causing this error?

I think the best way to determine what your code should be doing is by comparing it to the XML of a package created in the IDE. You may also need to simplify your package so you can isolate components until they are working correctly. Maybe you should consider just using dead end components like Unions instead of OLE DB destinations for your normal and error outputs.

These are the disposition-related settings I think you should be using for your Source component. Are you setting the UsesDispositions on your source component?

OLE DB Source Component
UsesDispositions=True

NormalOutput
IsErrorOut=False
ErrorOrTruncationOperation=""
ErrorRowDisposition=RD_NotUsed
TruncationRowDisposition=RD_NotUsed

Normal OutputColumn
ErrorOrTruncationOperation="Conversion"
ErrorRowDisposition=RD_RedirectRow
TruncationRowDisposition=RD_RedirectRow

ErrorOutput
IsErrorOut=True
ErrorOrTruncationOperation=""
ErrorRowDisposition=RD_NotUsed
TruncationRowDisposition=RD_NotUsed

Error OutputColumn
ErrorOrTruncationOperation=""
ErrorRowDisposition=RD_NotUsed
TruncationRowDisposition=RD_NotUsed

|||

Yep, I already had a windiff moment with it to find any differences between a IDE made package and a dynamically created one, thats how I've ironed out initial issues.

I've also used the example shown here:

http://blogs.conchango.com/jamiethomson/archive/2005/08/08/1969.aspx

Setting up the whole package in the IDE and using my database tables instead, mapping just the city column and no other columns, and all the happens is I get a warning about the imminent truncation and the truncation goes ahead. So I get nothing in my error destination.

This is getting me extremely fustrated, is there anyone who has an example of setting up a simple TruncationRow redirect?

Jamie Thompson could you knock one up?

|||I think you just said that if you create a package in the IDE (using your tables), that you can't get the redirection to work there either? That's the second indication I've heard that this may be a different problem.

Let's try to establish a baseline of functionality by creating a simple package in the IDE. If you have AdventureWorks installed, try it with that database first to take your data out of the equation. If not then just use your own table.
OLE DB Source in table mode that loads the Person.Address table from the AdventureWorks database. On the columns tab, select only the City column (it is an nvarchar(30))
On the Error Output tab, set the error and truncation dispostions to redirect row Close the Source, right-click it, and select "Show Advanced Editor" On the "Input and Output Properties" tab, open "OLE DB Source Output", open "Output Columns" Select the City column and change the Length property to 10 and click OK. The Source will now warn about truncation.
Drop two Union All components on the data flow surface, connect one to the normal (green) output from the source, and the other to the error (red) output.|||

Mr JayH you are a godsend.

From reading your points the problem with the my package was made clear in the line:

Select the City column and change the Length property to 10 and click OK. The Source will now warn about truncation.
|||

Just incase anyone else runs into this problem here is my final mappings code for the "Source".

private void CreateMappings()

{

// Map OutColumns to there external metadata columns for my mappings

foreach (SSISMapping mapping in dtsMappings)

{

IDTSOutputColumn90 outputColumn = component.OutputCollection[0].OutputColumnCollection[mapping.ToColumn.Name];

IDTSExternalMetadataColumn90 exMetaDataColumn = component.OutputCollection[0].ExternalMetadataColumnCollection[mapping.FromColumn.Name];

componentInstance.MapOutputColumn(component.OutputCollection[0].ID, outputColumn.ID, exMetaDataColumn.ID, true);

outputColumn.TruncationRowDisposition = DTSRowDisposition.RD_RedirectRow;

outputColumn.ErrorRowDisposition = DTSRowDisposition.RD_RedirectRow;

// Note that this must come AFTER the mapping because otherwise the properties will

//be mapped to the ExternalMetaColumn's properties

outputColumn.SetDataTypeProperties(SSISUtilities.GetDataType(mapping.ToColumn.DataType),

mapping.ToColumn.Length,

mapping.ToColumn.Precision,

mapping.ToColumn.Scale,

SSISUtilities.DEFAULTCODEPAGE);

}

}

Note that SSISUtilities and SSISMapping are not part of the framework.