.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

SUBSTRING within OLE DB Source sql vs Derived Column

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

One requirement of a project I'm working on is that the value within a column of my source be truncated to 30 characters.  The source column is currently a length of 50.  The previous developer was selecting the column, then downstream, used a derived column to substring the value into a new column.  The data set within this package is quite large and I'm attempting to clean it up.  The full 50 length value is never used (other than for the source of the 30 length derived column).

The question - is it good, bad, or indifferent practice to do this SUBSTRING from within the OLE DB Source SQL Command right up front, rather than passing the entire length down to a derived column?  I would assume, although maybe not eloquent possibly lending itself to more difficult debugging, it would cut down on the amount of data in the pipeline and negate the need for a derived column transformation entirely, which I assume would cut down on process time.

I realize both would work - just curious about best practice/performance impacts.



View Complete Post

More Related Resource Links

Efficiency: new column in source query or derived column task?

Hi All, I've just started working on an SSIS package that pulls data from an OLE DB Source by a query. A new column needs to be added based on the value of a queried column. I was wondering if it's better to do that in the query or with a derived column? A simple example: I have a table that contains CustomerName and CustomerCode (this one can be V /valid/ or I /invalid/). I need to store the CustomerCodeDesc in a separate column in the destination table. Is it better to alter the query like this: SELECT CustomerName, CustomerCode, CASE WHEN CustomerCode = 'V' THEN 'Valid' ELSE 'Invalid' END AS CustomerCodeDesc FROM CustomerTable Or is it better to use a DerivedColumn task in the DataFlow? Or maybe it doesn't really matter...

SqlBulkCopy: the locale id '0' of the source column '<column name> ' and the locale id '1



I have same issue, can anyone help?


The locale id '0' of the source column 'StandardName' and the locale id '1033' of the destination column 'StandardName' do not match.

I have check source and destination table both are same. Please help me.

BDC Entity as a Data Source for a Lookup column

Is it possible to create a custom lookup field type to have a BDC entity as the data source?

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

Using derived column for calculations

Hi Guys, Im trying to calculate a value using a derived column. I have this following recordset in my data flow. C1 C2 ITM 88 ITM 10 TX 52.45 DC 92.18 I have this formula: 1. DC = (DC- TX) * 1.12 So the result would be like this. C1 C2 ITM         88.00 ITM         10.00 TX         52.45 DC         44.50 Thanks,  

Load and parse a file where the file path is located in a column of a flat file source

Hi, I have a situation where I have a CSV that contains a bunch of data that populates a bunch of related tables with FK constraints directly.  Thats no problem.  The problem is that one of the columns specifies a filepath to an XML file that is supposed to contain data to be populated in a table with an FK constraint.  How do I specify the file name to the XML source component at runtime? For example TableA has columns: TableAID GroupName TableB has columns TableBID TableAID (FK to Table A) MemberID (the data in question) TableA has a 1 to many relationship with Table B. The CSV has a bunch of columns including: TableAID File path to list of members for each group. I don't know how to solve this problem.  I tried using a ForEach ... but I couldn't get it to work. Thanks in advance for any assistance.

Dynamic Column in Excel Source

Hi,I am having Excel Source Which needs to be imported into Sql Server Table using SSIS.In the Excel Source I dont have Month and Year Column.But in Table I have Month and year column and both the columns are Primary Key columns.So i am not able to Import data from Excel to Table.So is there any possiblities to add Columns Dynamically in Excel source inorder to  get the Year and Month

Derived Column Transformation My Expressin is not working


Hi Folks

My expression is not working -its always returning "N" if I find the word AIWORKS I want to return "Y" otherwise if not found "N"


finally if i want to test a whole lot of conditions why is this come up in red
COMPANY == SUBSTRING(LTRIM("AIWORKS"),1,7) ? "Y" : "N" ||COMPANY == SUBSTRING(LTRIM("Google"),1,6) ? "Y" : "N

thanks in advance



derived column


I am in the process of placing the data in a file into a table.
There is a field called MyDate in the file which is coming in as ddmmyyyy
Now I would like to place this into the MyDate field of my table1. This field is of type smalldatetime in the table.
I am using a derived column and in the expression I have place the following code but it shows it as red.
What is wrong please?

Saving Date column from XML Source to SQL Destination


Hi, I'm trying to load data from an xml file to SQL Server table. I've columns of type DateTime in my xml file. I've used Data Conversion to convert the datetime column to DT_Date and trying to save/update the data using Slowly Changing Dimension, which is throwing an error:

    "[Derived Column [928]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (928)" failed because error code 0xC0049067 occurred, and the error row disposition on "input column "DATEVAL" (973)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure."

I've been trying to fix this thing by using all the data types that are related to dates(DBTIMESTAMP,DT_DBDATE, etc) but in vain. Can someone suggest me how to fix this.

Thanks in advance,


Derived Column & Lookup


I have a derived column (MyNewID) that strips off the data I require like this:


This leaves me with a string that would look similar to 12345 (or whatever my ID is). What I need to do now is use MyNewID column to perform a lookup from a another table. I have tried using the the lookup, but can;t find a way to passw my new derived column's value into my Lookup's SQL query (which looks something like this:

 select Field1, Field2
 from tblSales s
 where s.SaleID = (this is where my parameter/derived column needs to be)

Is there a way to pass the value through somehow?  Am I using the right method to do this?

Displaying column data from another source?


 I have a gridview databound to a database table, and i want to make a gridview column display data that is accessed using the primary key data from the databound source to address a table which is not the one databound - how would I do this please?

Insert an item in a Lookup column's source List from the NewForm


When Lookup Column of a List does not contain a value in the DropDown menu can it be added/inserted into the source Lookup List from the NewForm without leaving the NewForm? 

Is there code or example how to insert the Item in the source List, then refresh/repopulate the Lookup Column's DropDown menu (in the NewForm)?


derived column


in my source i have month field and tear  and day fields but i added fulldate that is required  and i have to insert data to the respected rows based on the respected fields columns

y        m   d         fulldate

2006 08   6     2006-08-6

2007 09  18     2007-09-18

Source Column

I am wondering if it is possible to change the source column of a list.  Title (single line of text is automatically the source column.  I have a Column that is a people of group column that i want to change to the source column.  Is that possible.  I dont care if I have to change the column name to title or some weird thing like that.  thanks for the help

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?



Refresh data source view gets 'column' argument cannot be null

When I try to refresh a data source view in SQL Server 2008 BIDS, I get the error: System.Data   'column' argument cannot be null.  Parameter name: column.   How to resolve this?  The underlying tables have no data yet.
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