Hi all,
You know the coalesce function is not offered in the derived column component. how can I use the coalesce function within the component? And how can I use a user defined function within derived column component? Would you be kind enough to give an example?
Thanks! Any reply or comment is highly appriciated.
Robert
Can anyone show how to alter the value in a column using DerivedColumn component when creating an SSIS package programatically.
View 4 Replies View RelatedI am getting this error,
[Derived Column [192]] Error: Converting an input column from type DT_STR to type DT_WSTR failed. An error occurred while performing the implicit conversion on the input column.
But I don really understand y there is a attempt to type cast at all ?
Please advise ....
thanks in advance
If there are two columns in a Derived Column Component, Is there a way we can put a delimiter between them say a '||' symbol or so and build an expression. All I was able to do was concatenate them.
[Col1]+[Col2]
thanks in advance.
Hey. I need to see if "/" is present in the column11 and if it's then just pass it as is or do the substring part. How do I get this to work? It's giving me an error. This is for a TimeDate column. I can get a 20060813 or 2006/08/13.I'm using the below and it's giving me an error saying that It should be DT_BOOL and I'm trying to return DT_I4.
findstring(Column11,"/",2) ? Column11 : SUBSTRING(TRIM(Column11),1,4) + "-" + SUBSTRING(TRIM(Column11),5,2) + "-" + SUBSTRING(TRIM(Column11),7,2)
Thank you
Tej
I am reading an excel file with a field that consists of lastname, firstname. I am using the Derived Column transformation to separate the two fields. The firstname expression works fine: SUBSTRING(F1,FINDSTRING(F1,",",1) + 1,LEN(F1) - FINDSTRING(F1,",",1))
However, the lastname field keeps giving me an error when I use SUBSTRING(F1,1,FINDSTRING(F1,",",1) - 1). I'm sure I've used this substring before in visual studio. Could this be a bug? The error is below.
[Add Columns [2886]] Error: The "component "Add Columns" (2886)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "LastName" (3100)" specifies failure on error. An error occurred on the specified object of the specified component.
Hi,
I have created a program that imports a csv into the sql server. but during that import I need to track all the errors that occured for some malformed rows. I think I need to use the error output collection of the dataflow components to track the errors. I figured out that every dataflow component has a error output collection along with the data output collection. I want to write those error outputs into a separete database. So, I have created a SQL server data destination component and created a path between derived columns error output and it input collection. But it is not working as expected. can any body help on this?
or can anyone give me any example how to use/handle error output collection in SSIS?
I will appreciate all kind of suggestions.
thanks
Can somebody please help me with the implementation of a logic in round off to the left of a decimal point.
Something like this in excel "=ROUND(x/12*31%,-2)" is to be implemented in SSIS. The Round function in the derived column is not permitting -2 for the length parameter. Please help
Value x Excel SSIS
627900 16200 16221
187000 4800 4831
277760 7200 7175
763000 19700 19711
1387500 35800 35844
1465200 37900 37851
2725000 70400 70396
292800 7600 7564
317200 8200 8194
The table lists the values for X in the formula and the respective result calculated by Excel. I would want SSIS to give the same results like excel is giving. Please help me to make it work.
Can somebody please help me with the implementation of a logic in round off to the left of a decimal point.
Something like this in excel "=ROUND(x/12*31%,-2)" is to be implemented in SSIS. The Round function in the derived column is not permitting -2 for the length parameter. Please help
Value x Excel SSIS
627900 16200 16221
187000 4800 4831
277760 7200 7175
763000 19700 19711
1387500 35800 35844
1465200 37900 37851
2725000 70400 70396
292800 7600 7564
317200 8200 8194
The table lists the values for X in the formula and the respective result calculated by Excel. I would want SSIS to give the same results like excel is giving. Please help me to make it work.
Hi ,
Year function in derived column gives error if the incoming date is less than 1/1/1753. Is this Issue or required behaviour
Thanks
Dharmbir
Hi all--I've got a derived column transformation where I am adding a field called Import_Date. I'm telling it to add as a new column and use the function "GetDate()" to populate the field. When I run the package, it returns NULL as the data value for all rows. Any idea why this might be happening?
View 5 Replies View RelatedHello All.
Hopefully someone out there will have an idea as this isdriving me nuts.
Ihave some sample problem. I want to use function replace() on Derived Column.
For example.
when strDate = 2007/03/22
I used ==> replace(strDate, "/", "") ==> 20060322
But If strTest = "123.10" ====>> 123.10
How can i do to replace ( " )double qoute ?
by function replace()
what is a statement for replace (") in Derived Column ?
please tell me for this event.
any suggesstion appreciated
Thank you very much.
Chonnathan
When data is imported from our legacy system, the same functions need to be applied to several columns on different tables. I want to build a kind of "Function Library", so that the functions I define can be re-used for columns in several packages.
The "Derived Column" transform seems ideal, if only I could add my list of user-defined functions to it. Basically I want to inherit from it, and add my own list of functions for the users to select.
Is this possible ?
What other approaches could I take to building about 30 re-usable functions?
how to declare multiple derived columns in SSIS Derived Column Task in one attempt.as i have around 150 columns coming from Flat file. I had created the required Expression in Excel and now i want add those in derived column task but its allowing only 1 expression at a time.
View 4 Replies View RelatedHi
I'm a relative SQL Server newbee and have developed a function that converts mm/dd/yyyy to yyy/mm/dd for use as in a DT_DBDATE format for insert into a column with smalldatatime.
I receive the following erros when using the function in the Derived Column Transformation Editor. First, the function, then the error when using it as the expression Derived Column Transformation Editor.
Can anyone explain how I can do this transformation work in this context or suggest a way either do the transformation easier or avoid it altogerher?
Thanks for the look see...
******************************
ALTER FUNCTION [dbo].[convdate]
(
@indate nvarchar(10)
)
RETURNS nvarchar(10)
AS
BEGIN
-- Declare the return variable here
DECLARE @outdate nvarchar(10)
set @outdate =
substring(@indate,patindex('%[1,2][0-9][0-9][0-9]%',@indate),4)+'/'+
substring(@indate,patindex('%[-,1][0-9][/]%',@indate),2)+'/'+
substring(@indate,patindex('%[2,3][0,1,8,9][/]%',@indate),2)
RETURN @outdate
END
********************************
And the error...
expression "lipper.dbo.convdate(eomdate)" failed. The token "." at line number "1", character number "11" was not recognized. The expression cannot be parsed because it contains invalid elements at the location specified.
Error at Data Flow Task [Derived Column [111]]: Cannot parse the expression "lipper.dbo.convdate(eomdate)". The expression was not valid, or there is an out-of-memory error.
Error at Data Flow Task [Derived Column [111]]: The expression "lipper.dbo.convdate(eomdate)" on "input column "eomdate" (165)" is not valid.
Error at Data Flow Task [Derived Column [111]]: Failed to set property "Expression" on "input column "eomdate" (165)".
(Microsoft Visual Studio)
===================================
Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.SetInputColumnProperty(Int32 lInputID, Int32 lInputColumnID, String PropertyName, Object vValue)
at Microsoft.DataTransformationServices.Design.DtsDerivedColumnComponentUI.SaveColumns(ColumnInfo[] colNames, String[] inputColumnNames, String[] expressions, String[] dataTypes, String[] lengths, String[] precisions, String[] scales, String[] codePages)
at Microsoft.DataTransformationServices.Design.DtsDerivedColumnFrameForm.SaveAll()
Hi guys,
Do you have any idea why COALESCE function gives timeout in SQL2000 and works in SQL7.0
for ex.
in SQL7.0 this statement works fine in one of my SP
ROUND(COALESCE(ABS((SELECT SUM(Amount)))))
but in SQL2000 my SP is not working and my program gives timeout. But, if I change to ISNULL it works fine. Any clues on this issue?
This problem was there in SQL65 with ISNULL and then we have changed to COALESCE. Now, again repeated in 2000.
hi,
my question is about using coalesce function in SQLServer. This function brings up the first not null value. my problem is i cannot get the corresponding field name. this is what i use
SELECT COALESCE(Stop1, Stop2, Stop3, ...., Stop10)
FROM ...
WHERE ProjectID = #URL.ProID#
it returns,
(no column name)
----------------
1 somebody@somthing.com
the fields in the DB goes like stop1, stop2, stop3,...
so, i need to get which stop i am .thanks in advance.
I have three fields in a table say [F1, F2 & F3]. I need to fetch anyone of these three fields which has the maximum value between them.
In Simple words i'm looking for some function which is similar to COALESCE function which returns the first NOT NULL value of the fields that were passed as arguments.
FYI I'm using SQL Server 7.0 which does not supports UDF's
Earlier response appreciated
Thanks and Regards
Chandru
If you use Coalesce with Count will it return all values even when null? Reason I pose this question to you is that I am running the below query and am getting escalated results than what should be returned. 1st stop on the debug train is how does Coalesce handle it....for example Boston should return only 143 but the query retunrs 194 for Boston?
Code:
Select
Count(NumOnsite),
originatingCity,
Coalesce(Convert(varchar(4000),NewspaperNames), Convert(varchar(4000),MagazineNames)) As PaperNames
From readytoshipOffsite
Group By originatingCity, Coalesce(Convert(varchar(4000),NewspaperNames), Convert(varchar(4000),MagazineNames))
Hi all
If you look at BOL for [COALESCE] function we have:
Syntax
COALESCE ( expression [ ,...n ] )
Arguments
expression..is an expression of any type.
Return Types
Returns the same value as expression.
-------------------------------------------
Ok as it claimes , arguments can be of any type also the Return value, BUT when i execute the following command:
Print COALESCE('An String',12) -- just for example
and in generel when you have an string argument in this function you will receive the following error:
Syntax error converting the varchar value 'An String' to a column of data type int.
Could anyonel help me?
Thanks in advance.
Regards.
Hi, I downloaded "Microsoft SQL Server Compact Edition" v3.1, installed, created database in VS2005, created table TESTTABLE with one column TEST NVARCHAR 200 and run this command against it:
SELECT coalesce(test,NULL) from testtable
but it says
Error : The function is not recognized by SQL Server Compact Edition. [ Name of function = coalesce,Data type (if known) = ]
In this forums there are more usages of coalesce function, so I think it is supported and fully functional, but where is the problem, then ?
My system is Windows Vista Business (Czech version), version of sqlcese30.dll and sqlceqp30.dll is 3.0.5300.0.
thank you for suggestions
J.S.
I have a excel file which has a column called "Code" and their values are A,B,C,D,E,F,G,H. I want to create a new column called "status" based on the values of "Code".
Code:
A
B
C
D
E
F
G
H
If A,C,E,G then "status" = "Active" else if B,D,F,H then "Status" = "Inactive". I like to do it using "Derived Column".
Hi,
I have dates in "mmddyy" format coming from the sources and they are older dates of mid 80s like 082580 for instance.
When I cast it this way (DT_DBTIMESTAMP) Source_Date , It says ok but throws a runtime error.
When I hardcode a date in same format, (DT_DBTIMESTAMP) "082580" , It becomes red (an indication of syntax error) . Please note that we use double quotes in expressions in Derived Column Transformation; So an anticipation that using double quotes over single ones would be the syntax problem would be wrong.
Any help in this will sincerely be appreciated.
Thanks
Posted - 09/10/2007 : 15:53:26
Hey all - got a problem that seems like it would be simple (and probably is : )
I'm importing a csv file into a SQL 2005 table and would like to add 2 columns that exist in the table but not in the csv file. I need these 2 columns to contain the current month and year (columns are named CM and CY respectively). How do I go about adding this data to each row during the transformation? A derived column task? Script task? None of these seem to be able to do this for me.
Here's a portion of the transformation script I was using to accomplish this when we were using SQL 2000 DTS jobs:
'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************
' Copy each source column to the destination column
Function Main()
DTSDestination("CM") = Month(Now)
DTSDestination("CY") = Year(Now)
DTSDestination("Comments") = DTSSource("Col031")
DTSDestination("Manufacturer") = DTSSource("Col030")
DTSDestination("Model") = DTSSource("Col029")
DTSDestination("Last Check-in Date") = DTSSource("Col028")
Main = DTSTransformStat_OK
End Function
***********************************************************
Hopefully this question isnt answered somewhere else, but I did a quick search and came up with nothing. I've actually tried to utilize the script component and the "Row" object, but the only properties I'm given with that are the ones from the source data.
thanks in advance!
jm
Hi,
I am trying to create a inline function which is listed below.
USE [Northwind]
SET ANSI_NULLS ON
GO
CREATE FUNCTION newIdentity()
RETURNS TABLE
AS
RETURN
(SELECT ident_current('orders'))
GO
while executing this function in sql server 2005 my get this error
CREATE FUNCTION failed because a column name is not specified for column 1.
Pleae help me to fix this error
thanks
Purnima
Hi,
In a nut shell I want to be able to instruction some Data Analysts on how to modify SSIS packages using the simpliest solutions possible. This is because there are many different data sources and some of these data sources have a huge number of fields, and yes you guessed it these data sources are subject to change on a regular basis.
A very common task they will need to do is to modify an SSIS package to do a to transform of a source date string format of "YYYYMMDD" into a date data type field within a table.
Similar threads have advised the use of the Data Flow Transformations->Derived Column for this sort of thing.
So within the Expression Text box I have inserted the following SSIS compatible SQL to convert the above string into a british format date data type; -
Code Snippet
(SUBSTRING(DOB_SRC,8,2) + "/" + SUBSTRING(DOB_SRC,5,2) + "/" + SUBSTRING(DOB_SRC,1,4))
But really what I want to be able to do is to instruct the Data Analysts to do is something like; -
ConvertTextToDate(DOB_SRC)
Where I previously defined that behaviour of ConvertTextToDate as a public VB.NET function.
Can someone please help. I'm pretty certain I'm not the only one with this type of requirement.
Thanks in advance,
Kieran.
Table structure as follows
Employee
Empno empname salary
commission
I want to have an other employee table named employee_modified
Empno empname salary
commission derived_column1(salary+commission)
derived_column2(derived_column1 + xxxx) and so on derive other
columns based on the earlier derived columns)
Is that possible to do it.. or am I doing something wrong.
something like
Select empno , empname , salary , commission,
(salary + commission) as derived_colum1 ,
(derived_colum1 + xxxxx) as derived_colum2 ,
(derived_colum2 + xxxxx) as derived_colum3
into employee_modified from employee
Hi, how are you?
I'm having a problem and I don't know if it can be solved with a derived column expression. This is the problem:
We are looking data in a a sql database.
We are writting the SQL result in a flat file.
We need to transform data in one of the columns.
For example: we can have 3 digits as value in a column but that column must be 10 digit length. So we have to complete all the missing digits with a zero. So, that column will have the original 3 digits and 7 zeros. How we can do that tranformation? We must do it from de the flat file or it can be a previous step?
Thanks for any help you can give me.
Regards,
Beli
Hi, wondering if anyone can help me. I currently have a field that has a date and time in it in the format dd/mm/yyyy hh:mm:ss. Ideally I would like to get rid of the time part of it altogether but for it to still be recognised as a date as opposed to a string. However, I've been told that this is not possible in 2005, is this true? If this is the case, what would be the best way to set the time to 00:00:00 after the date for all records on that field?
View 1 Replies View RelatedI have two columns made up of 4 digits numbers eg col1 1234, col2 9876
I want to create a derived column so i get a eight digit column, eg col3 12349876
I cannot seem to get the expression right, I seem to be always adding the value which i do not want, can someone help me out with the expression
Thanks
Robbie
I am trying to transfer data from SQL Server 2005 table to another SQL Table. In the source table there is a field called Region [nvarchar(max)]. The values for these fields will be like APAC-China, NA-Racine, etc., i.e Region followed by the country name seperated by - symbol. I want the destination tablre with 2 fields Region[nvarchar(max)] and Country [nvarchar(max)]. I am using a Derived Column task to achieve the same.
Can anyone please help me out in doing this data conversion?
Thanks
Hi,
I am doing a task which will get all data based on several base and crosswork tables. I used stored procedure to get the data, but the next step will be using derived column to massaging data, and to load to the destination database. Is there a way to do that since I must generate data on the fly?
Thanks,
Megan
Hi Guys,
How can i put in a derived column the value of 3 columns? I've tried these:
[MyId]+[Paper1]
"[MyId]+[Paper1]"
but no luck. How can i put it right?
Thanks
Gemma