GetErrorDescription with oledb destination fast load mode

I have an OLE-DB Command transformation that inserts a row. If the insert SQL command fails for some reason, I use the "Redirect Row" option to send the row to a script component. Inthere, I get the error description into a string variable in order to log the error into an error table.

For example, if a primary key violation arises, I would like the error description to be "The data value violates integrity constraints". I get it using the ComponentMetadata.GetErrorDescription. When I use the "table or view mode", I get the error description above without any problem. But If I use the "table or view fast load", the description is something like "No status available". But, If I use the error output to fail the component, in the OnError, I get the right error description. Is there a way to have both behaviour, I mean, to be able to redirect error rows to an output and have the cotrrect error description (like the one in OnError event handler) using fast load mode?

Thank you!

Ccote

Hi ccote,

I am having exactly the same problem. Did you have yours resolved?

|||You can't get an error description using "Fast Load" as that is a bulk load operation.|||Even if I don't use the fast load option, the error description is vague. It returns something like "data violate integrity constraints". If I let the component fail and don't redirect the row, it gives the name of the constraint violated. It there a way to capture this detail error message?|||

I do not believe this information is available within the data flow. In any event, I've never been able to get more information than what you have described getting, above. Still, this is not necessarily a dead end.

Generally what I will do is set up an "error table" for each table into which I load data with an SSIS package. The error table will have a similar schema as the "real" target table, with a few exceptions:

It has much more lax integrity constraints - there are no foreign keys, and all columns allow NULLs, for example, so the odds of an INSERT failing are greatly reduced.

It has two additional columns, ErrorCode and ErrorDescription, into which I load the values added by the OLD DB Destination component's error output.

GetErrorDescription with oledb destination fast load mode

I have an OLE-DB Command transformation that inserts a row. If the insert SQL command fails for some reason, I use the "Redirect Row" option to send the row to a script component. Inthere, I get the error description into a string variable in order to log the error into an error table.

For example, if a primary key violation arises, I would like the error description to be "The data value violates integrity constraints". I get it using the ComponentMetadata.GetErrorDescription. When I use the "table or view mode", I get the error description above without any problem. But If I use the "table or view fast load", the description is something like "No status available". But, If I use the error output to fail the component, in the OnError, I get the right error description. Is there a way to have both behaviour, I mean, to be able to redirect error rows to an output and have the cotrrect error description (like the one in OnError event handler) using fast load mode?

Thank you!

Ccote

Hi ccote,

I am having exactly the same problem. Did you have yours resolved?

|||You can't get an error description using "Fast Load" as that is a bulk load operation.|||Even if I don't use the fast load option, the error description is vague. It returns something like "data violate integrity constraints". If I let the component fail and don't redirect the row, it gives the name of the constraint violated. It there a way to capture this detail error message?|||

I do not believe this information is available within the data flow. In any event, I've never been able to get more information than what you have described getting, above. Still, this is not necessarily a dead end.

Generally what I will do is set up an "error table" for each table into which I load data with an SSIS package. The error table will have a similar schema as the "real" target table, with a few exceptions:

It has much more lax integrity constraints - there are no foreign keys, and all columns allow NULLs, for example, so the odds of an INSERT failing are greatly reduced. It has two additional columns, ErrorCode and ErrorDescription, into which I load the values added by the OLD DB Destination component's error output.

Get the Row Count

I have created a package which is transforming the data from Source OLEDB Sql Server to Destination OLEDB Sql Server programatically in VB.NET

During execution phase, I want to generate an event after every 1000 rows are transformed. As per I think, OnProgress event of IDTSEvent doesn't support this kind of a thing.

And after my Transformation is completed, I want to know how many rows have being transformed.

So how can the above two task be performed?

Add a Row Count component to your data flow to capture the number of rows.

You should be able to fire events using the FireProgress or FireInformation methods of the ComponentMetaData class. See this topic in Books Online: Raising Events in the Script Component (http://msdn2.microsoft.com/en-us/library/aa337081.aspx)

|||

I am creating the package programmatically and not using designer for it.

I have already created the package which has OLEDB and Source and Destination and added to TaskHost. For Row Count Component I think I have to create another Task Host and then add it to main pipe. But it has to be added as For Each Loop. But if I use For Each Loop then one Task Host will process at a time.

So what is the solution for this?

|||

I have added Row Count Component as Transformation (DTSTransform.RowCount) to the ComponentMetaData.

Now I get the number of Row Transformed after post execute event whereas I need to raise an event after every 1000 rows are transformed. According to me after PostExecute event of the DataFlow, the number of Rows Transformed as set into the variable by Row Count Component. So how can I raise event after every 1000 rows?

|||One way to do this would be to add a script transform to monitor the number of rows and fire the event after 1000 rows pass through.|||

I have used Script Component to transform. As I am creating this programmatically, I have copied the code generated in xml format of dtsx file to my application as a string array exactly as in the file to SourceCode property.

I have set PreCompile Property to false. So I don't need to set the BinaryCode property.

I have used code as below:

Dim DFTransform As IDTSComponentMetaData90

DFTransform = DTP.ComponentMetaDataCollection.New()

DFTransform.ComponentClassID = "Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost, Microsoft.SqlServer.TxScript, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

DFTransform.Name = "ScriptTransform"

DFTransform.UsesDispositions = False

Dim TransInst As CManagedComponentWrapper = DFTransform.Instantiate()

TransInst.ProvideComponentProperties()

DTP.PathCollection.New().AttachPathAndPropagateNotifications(DFSource.OutputCollection(0), DFTransform.InputCollection(0))

TransInst.SetComponentProperty("VsaProjectName", "ScriptComponent_3a1cf20682b14906bbdc971f7768e55c")

TransInst.SetComponentProperty("SourceCode", AddSourceCode(DFTransform.ComponentClassID))

TransInst.SetComponentProperty("BinaryCode", AddBinaryCode)

TransInst.SetComponentProperty("PreCompile", False)

TransInst.SetComponentProperty("UserComponentTypeName", "Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost, Microsoft.SqlServer.TxScript, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")

TransInst.AcquireConnections(Nothing)

TransInst.ReinitializeMetaData()

Dim output As IDTSOutput90 = DFTransform.OutputCollection(0)

Dim outputColumn As IDTSOutputColumn90 = output.OutputColumnCollection.New()

outputColumn.Name = "myCount"

outputColumn.SetDataTypeProperties(Wrapper.DataType.DT_I4, 0, 0, 0, 0)

DFTransform.OutputCollection(0).ExternalMetadataColumnCollection.IsUsed = False

TransInst.ReleaseConnections()

But when I compile my application I get following errors:

Error Code :-1073450910

Sub Component :- Script Component [43]

Description :- System.NullReferenceException: Object reference not set to an instance of an object.

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.CreateUserComponent()

Error Code :-1073450901

Sub Component :-DTS.Pipeline

Description :- "component "Script Component" (43)" failed validation and returned validation status "VS_ISBROKEN".

Is there anything which I need to do differently