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

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

SSIS - Derived Column Error Rows will not redirect to table

Posted By:      Posted Date: October 19, 2010    Points: 0   Category :Sql Server

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?



View Complete Post

More Related Resource Links

How to recover from Derived Column Transformation Editor corrupting table metadata?

Hi there, In attempting to use the DCTE to replace the value in a field with a trimmed version (SSN = trim(SSN)), it seems that my meta-data has become corrupted by the Derived Column drop-down list. As a result, #1: I no longer see my incoming SSN field in the "columns" tree, and any reference to it is deemed "invalid", even though it's value does make it out of the Data Transformation process. How can I get back the reference to this field without redoing this entire task? And, #2: in the process, DCTE created new columns with "SSN" prefixed by the task name, such as "trim character fields.SSN". How can I delete these? It seems that one slip of the mouse in this form can lead to irreversible corruption of the meta-data, which the "debugger" then references and uses to "invalidate" subsequent work. I have tried everything I can think of to refresh this, including using the "Advanced Editor" and reloading the entire package. Any ideas? Thanks, Karl Kaiser

How to redirect bad date(xxx,9999) in datereceived column in text file in ssis

I want to redirect bad date format from flate file source to log table. I tried with redirect row but it is not working. Thanks in advance

How to get column value difference of rows in same table

Dear all, I need a TSQL statement to find the difference of values of two rows in the same table, by taking into consideration some conditions. I have the following rowss in a table named table1 dbname  sqlinst     size1   ddate sqldb1    inst1        200    1/1/2009 sqldb1    inst1        250    1/1/2010 sqldb1    inst1        170    1/1/2008 sqldb2    inst2        300    1/1/2009 sqldb2    inst2        340    1/1/2010 I need to find the difference between size1 values, for columns where their dbname and sqlinst are the same. I also need to define in TSQL that the ddate of row from where I subtract (size1) from is 1/1/2010 and that the ddate of the row I subtract (size1) is 1/1/2009 (e.g. in the above example: for sqldb1 inst1, I need to perform 250-200 and ignore 170, and for sqldb2 inst2  340-300). Please let me know if you have a solution for this. A million thanks!

How to declare a table variable in SSIS and then insert rows into it

Hello everyone,   I'll try to explain my problem as clearly as I can: 1)I have an Execute SQL Task that's inside a Foreach Loop Container. 2)Inside the Execute SQL Task, I have an int OUTPUT column. 3)With each iteration, I need to insert the data from the INT output colum into a table variable. 4)After the Foreach Loop finishes the iterations, I need to use the table variable to create a report based on the data inside.   My question is the following: How can I declare a table variable so that I can do all of the above?   Thank you, CostinP

Infopath 2007 Repeating Table - Multiple Value Column Text - Hiding Rows based on Column text values

Infopath 2007 browser based form Full Trust Example: I have a repeating table (FruitChoice) that has multiple columns. Both drop down list point to sharepoint list data sources. Choose your tree ft. drop down list – 6Ft Choose your Department drop down list - 103 This repeating table is conditional on the drop down values. This works great. Trees     Fruit       Cost   Date Ordered    Date Delivery Department 6Ft        Peaches                                                        103 3Ft        Apples                                                          102 3Ft        Peaches         &

Adding Oracle table into SSIS project --Oracle error occurred, but error message could not be retrie

Hi all I am new to design SSIS packages,to day I stated creating package SSIS package and adding the Tables,while adding tables,one of the table giving an below error . I verified the table structure it has one "CLOB" datatype. can some body help how to add this table( whole idea of the package is I have Dump those tables with structure and Data to SQL Server 2008 Database)  =================================== Oracle error occurred, but error message could not be retrieved from Oracle. Data type is not supported. (Microsoft Visual Studio) ------------------------------ Program Location: at System.Data.OleDb.OleDbCommand.ProcessResults(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.PrepareCommandText(Int32 expectedExecutionCount) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.OleDb.OleDbCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.DataWarehouse.Design.DataSourceConnection.FillDataSet(DataSet dataSet, String schemaName, String tableName, String tableType) at Microsoft.AnalysisServices.Design.DataSourceDesigner.AddRemoveObjectsFromDSV() Thanks in Advance.   SNIVAS

Using ntext column i a table with many many rows?


I want to know more about implications of adding a ntext column to an already existing table with 10000+ rows. My estimate is that only 1 out of 100 rows will haveanything stored in the ntext field. Does this mean that space required is counted for every row this table contains? Not only for those rows that have data in this field?

Shall I instead constuct a sub table storing the ntext column with a relation id pointer to the specific row in the mother tale to save space?


Error while merging: "Could not retrieve column info from table tablename" and Could not find stored


Hi i am getting following error message while merging. Can any one please help?

We are using sql server 2005
I have this problem in publisher.Gone throught his article http://social.msdn.microsoft.com/Forums/en/sqlreplication/thread/b0bbc330-91df-45d5-8dbf-e2df461a8005 but no luck.

The merge process could not retrieve column information for table 'dbo.tablename'. Verify that you have sufficient privileges on the database and retry the operation. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201016)
Get help: http://help/MSSQL_REPL-2147201016

Could not find stored procedure 'dbo.MSmerge_sel_sp_6481B06512B54C91FCC171C542E7'. (Source: MSSQLServer, Error number: 2812)
Get help: http://help/2812


Select Primary Key Column of Table returns 0 rows


I have three tables: Design, Box, Wall.

Design has a column DesignId that is a GUID. It does not allow nulls, is unique, and is the primary key. The identity is set to false for compatibility with Entity Framework v4.

Box has  a column BoxId that is a GUID. It does not allow nulls, is unique, and is the primary key. The identity is set to false as well.

Box also has a column DesignId that is a GUID. It does not allow nulls, is not unique and is not the primary key.

Wall has  a column WallId that is a GUID. It does not allow nulls, is unique, and is the primary key. The identity is set to false as well.

Wall also has a column DesignId that is a GUID. It does not allow nulls, is not unique and is not the primary key.

Wall also has a column BoxId that is a GUID. It does not allow nulls, is not unique and is not the primary key.

Box.DesignId has been set as a ForeignKey relating to Design.DesignId.

Wall.DesignId has been set as a ForeignKey relating to Design.DesignId

Wall.BoxId has been set as a ForeignKey relating to Box.BoxId.


If I execute      SELECT DesignId FROM Design     I see the correct number of rows.

If I execute     SELECT BoxId From Box        I see zer

ERROR:value for column in the table is DBNull



I have a strongly-typed dataset "TABLE" and I am want to retrieve its value..
Sometime the value is NULL. And I am getting an error "The value for column ' PHONE_NO ' in table 'User' is DBNull.

Here what I tried and it failed.. 
object value = userTable[0]. PHONE_NO[0];
string str = (value is DBNull) ? "" : (string)value;

I also tried. 
string str = userTable[0]. PHONE_NO[0] == null ? "" : userTable[0]. PHONE_NO

How can I fix it. 

Thank you


Hidding table rows if one column has no value


I have a form with a repeating table, which is pulling data from a SharePoint list.  The table has 5 columns, the first 4 columns are read only, populated from the SharePoint list, the last column is a text input field.  People input a number into the text field for some of the rows.  Is there a way to hide all the rows that do not have a number in the last column and only show the rows that have a number in the last column?

How to insert column wise data as a seperate rows using ssis


Hi All,

I have my data in excel like this ....


SSIS Derived Column and Data Conversion Task


I am passing down a variable, filename, as  string and then using Derived Column to pass it down to the destination. The filename is numeric (date) like such: 20101010. So, I need to convert the filename into a date formmat before I pass it to the destination table. Therefor, I am using a data conversion and converting it to a Date format. I tried using both database date or the date format and it keeps failing.

Here is what the failure report:

[Data Conversion [42297]] Error: Data conversion failed while converting column "DateAdded" (29685) to column "Copy of DateAdded" (42310).  The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".  

Any ideas to resolve this issue?



Call a SQL Server function(cleans html tags) on a derived column in SSIS


I have a task to move table having "text" data type for a column from SQL Server to CSV file so while transfering the data using SSIS I want to perform Cleanup on this column such as removing HTML tags, White spaces etc and then transfer it. Note: I dont have any option to store the table in the staging database as I will be running it on the production server directly.

Can any one help me accomplish this task???





How can i convert Integer to String in SSIS Derived column.


i have gender column with 0,1 values

i`ve to convert it into 0=male and 1=female,i tried this


BUT it is giving error

TITLE: Microsoft Visual Studio

Error at Data Flow Task [Derived Column [46]]: The data types "DT_WSTR" and "DT_I4" are incompatible for binary operator "==". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

Error at Data Flow Task [Derived Column [46]]: Attempt to set the result type of binary operation "Gender == 0" failed with error code 0xC0047080.

Error at Data Flow Task [Derived Column [46]]: Computing the expression "[Gender] ==0?"Male":[Gender]==1?"Female":"NA"" failed with error code 0xC0047084. The expression may have errors, such as divide by zero, that cannot be detected at parse time, or there may be an out-of-memory error.

Error at Data Flow Task [Derived Column [46]]: The expression "[Gender] ==0?"Male":[Gender]==1?"Female":"NA"" on "input column "Gender" (114)" is not valid.

Error at Data Flow Task [Deri

Derived column TRANSFORMATION Parsed Error


Hi i am getting following error message while executing a package that 
updates the column value using a derived column transformation..
[TRR_FE_Parse_File [271]] Error: The "component "TRR_FE_Parse_File" (271)" failed because truncation occurred, and the truncation row disposition on "output column "SrcFileName" (32263)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "TRR_FE_Parse_File" (271) failed with error code 0xC020902A. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.
Error: SSIS Error Code DTS_E_THREADFAILED.  Thread "WorkThread0" has exited with error code 0xC020902A.  There may be error messages posted before this with more information on why the thread h

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:
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