.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

C# .NET SqlBulkCopy and redirecting error rows

Posted By:      Posted Date: August 29, 2010    Points: 0   Category :Sql Server
I am using SqlBulkCopy within a Script Task to extract data from an Oracle server into SQL Server. Is there a way to redirect error rows with code or otherwise from the SqlBulkCopy? I have implemented this before using a regular Copy Column task and redirecting the Destination Error into a different table of all data type varchar. So I am trying to make something similar with the script task and SqlBulkCopy. Any suggestions? ThanksYou are so wise...like a miniature budha covered in fur. -Anchorman

View Complete Post

More Related Resource Links

Two rows passed to error handler instead of one....

Scenario: Flat File conneciton manager for import, and another for error exporting OLEDB destination for successful data. Transforms connecting the flat file source to the OLEDB destination on success, and to a script component, and eventually a flat file destination in the event of an error. Goal: Very basic - take rows from a flight file, pipe delimited (CR/LF for rows) source, and map it into a table, exporting records that would error out into an error file. Symptom: Given a 3 row file (just for test), where the first record is missing a column, we wind up with 2 records (the record that we know has the error, plus the following record) in the error file, and only the 3rd record in the success file. One one record is reported as going to each file (so, the error transform shows a single record, even though two wind up in that file). The couple of lines of code that move things over are as follows... _erroredRow = System.Text.Encoding.ASCII.GetString(Row.FlatFileSourceErrorOutputColumn.GetBlobData(0, (int)Row.FlatFileSourceErrorOutputColumn.Length)); Output0Buffer.AddRow(); Output0Buffer.Record = _erroredRow; What I suspect is happening here is that the delimiter at the end of the first (error) record is being missed, and thus what appears as two rows (and they do each have the appropriate CR/LF) is being treated as one. Any hints as to why this is happening a

How do we redirecting rows using stored procedure

Hi I have one SQL Execute task and it runs one stored procedure inside it. That SP inserts data into one of table.Now when I inserts records into this tbale let's say because of primary key some records are failing so how can i redirect this rows in stored procedure  

Error Firefox has detected that the server is redirecting the request for this address in a way tha

The page isn't redirecting properly                                                        Firefox has detected that the server is redirecting the request for this address in a way that will never complete.                            *   This problem can sometimes be caused by disabling or refusing to accept          cookies.

Read only error trying to copy and paste rows of records from Excel spreadsheet


I'm using Sharepoint 2010 and trying to open a list in Edit in Datasheet View. The list opens but when I try to paste lines into the sheet I get a Read-only error and it doesn't accept my paste. I removed all required fields.

I'm using XP and IE8

This function was working in SharePOint 2007.


SQL Server user-defined aggregate returns an error if aggregating 0 rows

I have this SQL Server user-defined aggregate:

[SqlUserDefinedAggregate(Format.UserDefined, Name="median", IsInvariantToDuplicates=false, IsInvariantToNulls=true, IsInvariantToOrder=true, IsNullIfEmpty=true, MaxByteSize=8000)] 
public class MedianCalculator : IBinarySerialize { 
    private List<double> values; 
    public void Init() { 
        values = new List<double>(); 
    public void Accumulate(SqlDouble value) { 
        if (!value.IsNull) 
    public void Merge(MedianCalculator other) { 
    public SqlDouble Terminate() { 
        if (values == null || values.Count == 0) 
            return SqlDouble.Null; 
        return (values[(int)Math.Floor((values.Count - 1) / 2.0)] + values[(int)Math.Ceiling((values.Count - 1) / 2.0)]) / 2.0; 
    public void Read(BinaryReader r) { 
        int c = r.ReadInt32(); 
        values = new List<double>(c); 
        for (int i = 0; i < c; i++) 
    public void Write(BinaryWriter w) { 
        for (int i = 0; i < values.Count; i++) 
After deploying this aggregate, I try to run this query:

SSIS - Derived Column Error Rows will not redirect to table


I am new to SSIS and I have created a package that generally successfully takes a Flat File Source, runs it through a Derived Column transformation and then pushes the data into a SQL Server Database.

I have a Derived Column Error Output set up, but it is not working.  If the data has an error the entire package fails, not just the specific row.  I have tested the package to load successfully and then manually gone into the flat file and changed the data to purposely create an error in one row. Having cleared the load table I rerun the package and it fails entirely rather than redirecting the one bad row.

In the Derived Column Transformation Editor I set the Error and Truncation values to Redirect Row for all the columns.

In SQL Destination Editor for the bad rows I am only mapping the ErrorCode abnd ErrorColumn values to the destination table fields of the same name.  Could this be the problem? Should I map all the fields?  I would like to map all the source (derived) fields into a single text field in the destination table if possible.

The Advanced settings have only the Table Lock and Check constraints boxes checked.  Timeout is 30.

I am not really sure how to set the Advanced Editor properties.  I have left them at the default values.  Any suggestions here?



Shared Data Set error when no rows returned



I've created a parameterised Shared Data Set and everything is fine when it returns data. However, when I set the parameters so that no data is returned I get the error message:

An error has occurred during report processing. (rsProcessingAborted)
The execution failed for the shared data set '<Data Set Name>'. (rsDataSetExecutionError)
Object reference not set to an instance of an object.

I've looked through the various knowledgebases etc and the only recommendation I've found is to add a NON EMPTY clause to the MDX query so data is always returned. This isn't ideal as I'm looking at activity over a given date range, and it's quite useful that weekends are missed out in the reports (as there is no activity during those days).

Does anyone have any other suggestions?


SqlBulkCopy SQL Express 2005 copied to few rows



When I try copy some data from remote (ODBC) SQL serverer to local SQL Express using SqlBulkCopy  I saw that I have to few rows.

First I downloading data to dataSet from remote SQL server using ODBC:


OdbcConnection dbConn = new OdbcConnection("DSN=" + settings.ODBC_DSN + ";uid=" + username + ";pwd=" + password);
OdbcDataAdapter dataAdapter = new OdbcDataAdapter();
DataSet dataSet = new DataSet();

 dataAdapter.SelectCommand = new OdbcCommand(sql, dbConn);
 dataAdapter.Fill(dataSet, settings.RECIPES_TABLE_NAME);
catch (Exception ex)



(When I check dataSet during debugging, dataSet object contained proper count of rows)
Next I copy data using SqlBulkCopy object:

Adding Dynamic Rows in ASP.NET GridView Control with TextBoxes and with Delete functionality

In my previous examples, I have demonstrated on how to add dynamic rows in GridView control with TextBoxes and how to save the values into the database. Now, seems that most of the developers are asking if how to add a delete functionality with it. So in this example, I'm going to show on how to delete a certain row in the dynamic GridView with TextBoxes.

jQuery: Highlight Gridview Rows with Checkbox

Im learning and experimenting with jQuery and ASP.NET. Below is a simple solution I created to highlight rows in a gridview by clicking on a checkbox next to the row.

Gridview setup:

Highlighting Rows with TextBox OnFocus

In another article I explained that how you can make the rows of the GridView control clickable and highlight them when they are clicked. In this article I will explain that how you can highlight the GridView rows when you focus on the TextBox which is contained inside the GridView control.

jQuery: Highlight Gridview Rows with Checkbox

Im learning and experimenting with jQuery and ASP.NET. Below is a simple solution I created to highlight rows in a gridview by clicking on a checkbox next to the row.

jQuery: Styling DropDownList Alternating Items (Rows)

In the following example, I will show how you can style any asp:dropdownlist very easily.

The example demonstrates how you can change alternating row items background color, using the fadeTo() to add a nice touch in the dropdownlist. You can change alt rows with a couple lines.

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

How to format and update GridView and DataGrid rows using JQuery

The behavior described in this question is as expected. When you set text of a cell in grid, it directly affects HTML that is going to be rendered. When you set text value of a cell, it means that you are setting innerText of the cell. The column that GridView creates for command fields (Edit, Delete and Select) are a (anchor) or button elements. So you can see what will happen if you set text value in that cell. It will wipe out those link or button controls and replace them with simple text string.

Using jQuery to Filter Table Rows

The project is using the .net GridView control, so I had limited control over the output HTML code. Still, I think this code can work for most tables. One thing to notice: you should use the class "filterable" on your table or on one of its parents for the code to work.
First, we need a text box:

Insert Rows with a GridView

In ASP.NET version 1.x, I used the footer on the DataGrid control to insert new rows by placing a series of TextBox, DropDownLists, CheckBoxes etc. controls on it and then handling a save button. This also works with the GridView in version 2.0 but with one major exception: if your data source is empty, the GridView will hide your footer (and header) and only display the EmptyDataText.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend