I would like to run following T-SQL statement in the Derived Column:
declare @strData varchar(1000), @strSQL varchar(3000)
select @strdata = 'LANEX ~11/13/06 10:10:08 ~112233 9th ST S. Arlington, 22204, VA ~ 093300~XYZ '
select @strSQL ='select datalength('''+replace(@strData,'~','''),datalength(''') +''')'
--print @strSQL
exec (@strSQL)
Thank you!
I have a table in my report which shows sales values for each month by looking at the month number
so for Jan i use
=IIf(Fields!period_.Value=1,Fields!nett_.Value,0)
for Feb
=IIf(Fields!period_.Value=2,Fields!nett_.Value,0)
this is all good and I get a series of rows per customer with the correct value in the correct column
JAN FEB Mar
Customer A 250
Customer A 350
Customer A 5000
.
However I want to create a summary a line
JAN FEB Mar
Customer A 250 350 5000
I tried using SUM but this doesn't give all of the values in the summary line. It might just give the first and the rest are zeros. Or if the customer didn't have a value in Jan, but did in Feb and March, Feb's value is shown, but March is at 0 etc.
Have spent far too long on this today, so if anyone as any suggestion (come back Crystal all is forgiven ) on any approach I could take to this I'd appreciate it.
Cheers
Steven
Hi, all experts here,
Thank you very much for your kind attention.
I am having a question on derived column expression. The expression I am trying to use for the derived column is as below (column1 is a numeric data type column):
case
when column1<0 then 'yes'
else 'no'
end
But I got the error message though, would please any experts here give me any advices on the expression I used? What is wrong with the expression I used above?
Thank you very much and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
The following simple expression is not working for in a derived column transform. I have tried several things back and forth with lots of frustration.
DAY((DT_DATE)mydate)
In fact any of the date/time functions are not working for me and I keep getting the following error
[Derived Column [380]] Error: The "component "Derived Column" (380)"
failed because error code 0xC0049067 occurred, and the error row
disposition on "output column "Derived Column 1" (824)" specifies
failure on error. An error occurred on the specified object of the specified component.
mydate happens to be imported as string[DT_STR] column from a flat file source.
Thank you in advance.
Hi.
I am using the following expression to check if the first charcter of a string is not the letter "E" and if it is, strip it off by selecting the remainder of the string:
SUBSTRING([Derv.comno],1,1) == "E" ? SUBSTRING([Derv.comno],2,10) : [Derv.comno]
This is ok in 99.9% of cases, but ideally I would like to be able to check, and alter the string if the first charcter is anything but numeric
I had something like this in mind:
SUBSTRING([Derv.comno],1,1) != ("1","2","3") ? SUBSTRING([Derv.comno],2,10) : [Derv.comno]
but the syntax is incorrect.
Could you tell me if what I am attempting is actually possible, and if so, point me in the right direction regarding the syntax!
Thanks
I'm trying to import from Excel, using
(Assignment == 0 ? "In Use" : Assignment == 1 ? "In Stock" : "Retired") to convert 3 values to text and I' getting the following error:
An error occurred while attempting to perform data conversion. The source type could not be converted to the destination type.
Could someone please tell me what I am doing wrong?
Thanks
Dean
ps. just fixed it myself by changing the data type of the input column
Greetings,
I have an existing 2000 DTS package that uses the following case statement:
Case
When TERMS_PERCENT ='0'
then 0
else cast(TERMS_PERCENT as decimal(6,2))/100
end as TermsPct
to convert a source DT_STR(4) datatype to a DT_Numeric(5,2) destination column and would like to use an equivelent derived column expression in 2005. Being a DBA by nature and experience I'm having trouble converting this statement to a valid expression without failure, any help would be greatly appreciated.
What is the equivalent SSIS expression for this C expression to format an unsigned integer by padding it with leading zeros?
sprintf(publ_doc_id, "%010u", id)
Hi all!
I am importing data from a CSV to a DB with a SSIS package.
Among some things things it already does, it has to decide if the relation between one row and the following is acceptable.
If it is not, the 2nd row is discarded, the next one is taken and the relation value is calculated again to decide whether to keep this one or not, and so on.
To calculate this value, I need to apply a formula that includes sin(), cos() and acos() functions.
I have already written this formula as a scalar-valued function in my SQL Server.
So, my question is:
- Is there a way to call a function (a UDF) within the Expression in a Derived Column dataflow item?
and if not,
- Hoy can I use trigonometric functions within the Expression in a Derived Column dataflow item?
I hope someona can tell me something about this... I'm falling into despair! :-s
Thanks a lot!!
Cau
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 want to pas one Derived Column Value is "Null" Default . How to Give the Expression "
For Example"
The Derived Column i gave one Column Name
Derived Column Name Expression Data Type
Price ""
Any one give me the Solution
one of my SSIS packages use this expression to put todays date in if it is NULL:
(ISNULL(datejs)) ? GETDATE() : datejs
however, what I really want to do though is put a default date in like '2007-01-01' but I get syntax error because SSIS thinks it's a string, which it is I suppose.
Is it possible to do what I want it to?
Thanks
Hi,
i am facing problem to dervie one column value from another column using either if and case statements in expression window of dervived columns transformation.
let me give the exapmle. i get 1 column from source system name as "col a" and i want to insert 2 columns into my destination as col A and col B. based on the values of col A i want to derive the values of col B,like if col A value is 0 then col B value is Good else BAD.
Can any one asssit in this regard how to achive it?? and is it possible to use IF and CASE statement in this dervived column tranformation???
Sreenivas
hi,
This field comes from a flat file and sometimes own zero and sometimes own a empty position (after 'EUR'):
2006053000499236000005307700108287457080200408287452006052953990000000010000EUR
2006053000499236004414989200101423426004400501423422006052953990000000010000EUR0
[Column 12] == "0" ? [Column 12] : ?????????
TIA
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
I am trying to put the following as an expression in the SSIS Derived Column Transformation Editor.
DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
It is not allowing it. This works fine in a regular SQL statement.
Does anyone know how I can get this to work?
I am trying to figure out my expression here with derived column
source
Unavailable/Planned
output
Planned(which is after "/".
I achieved this in sql but not finding solution in SSIS Derived column transformation.
How can i write expression for below input?
1858;# 1234;#Chun Yang
I want to display only name.
Can someone confirm this for me? The expression language in SSIS has the same limitations on date ranges as Sql Server? That limitation is that valid date ranges are from Jan 1, 1753 to Dec 31, 9999.
When ever I try to do a date function (DATEPART, for example) in a Derived Column Transformation on a date less than 1/1/1753, I get an error. I initially discovered this when bringing data over from Oracle to Sql Server. Just as a test, I created a text file filled with various dates and tried to import it. Whenever a date is less than 1/1/1753, it blows up.
For example, this expression code - DATEPART("YEAR",Date) will yield this error - [Derived Column [24]] Error: The "component "Derived Column" (24)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "YEAR" (80)" specifies failure on error. An error occurred on the specified object of the specified component.
As a workaround, I've been using a Script Component to do date checking, but this is obviously not ideal.
I'm importing a csv-file delimited with semicolons. Firstly I LTRIM the columns "in place" and the data imports fine. All the numbers in right columns in the target table. Then I add another Derived Colum Transformation to replace decimal character comma (,) to a dot (.) in order to convert the string/varchar value to numeric. But here I run into trouble. Running the task ends in success but the result in the target table (same as above) is not. All the commas are now dots as expected but what is worse is that SSIS have added values in cells that should not be there. I get values in cells that shoud be empty!
Shortly: Only LTRIM([Column1]) as expression and "Derived Column" as Replace 'Column1' works OK.
But adding REPLACE-expression (i.e REPLACE(LTRIM([Column1]) , "," , ".") to this breaks things up
I'm aware that I could do this with SQL but this is not the point...
Any ideas?
BR Jompe
I have one column CandidatePlaced (Data type Boolean).
Using data conversion i changed data type to DT_WSTR and then i used derived column to manipulate the data. Ex. 1 = "Yes" and 0 = "No"
[Candidate Placed ?] == "1" ? "Yes" : "No"
But at end of result i got all the columns as No. Some should be Yes.
Hi everyone,
I am a newbie to SSIS and looking for answer to a simple search/replace style question.
I am building a derived column and looking for some expression syntax that can search for several strings in one column and set a value in a new column.
A bit like a case statement:
CASE ProductLine
WHEN 'R1' THEN 'Road'
WHEN 'M1' THEN 'Mountain'
WHEN 'T1' THEN 'Touring'
WHEN 'S1' THEN 'Other sale items'
ELSE 'Not for sale'
END,
The twist is that 'R1','M1','T1','S1' could feature anywhere in the string that is ProductLine.
Thanks for all your help,
regards,
Chris
Hi guys,
I am looking for some syntax to help me with a traditional search/replace style requirement. I am wanting to examine the contents of one column and populate another.
similar to this SQL case statement
CASE ProductLine
WHEN 'R1' THEN 'Road'
WHEN 'M1' THEN 'Mountain'
WHEN 'T1' THEN 'Touring'
WHEN 'S2' THEN 'Other sale items'
ELSE 'Not for sale'
END,
the twist is that R1, M1 etc. can appear anywhere in the ProductLine column.
thanks for any assistance you can provide.
regards,
Chris
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 RelatedHow can I validate the date that is coming across in a 8 byte character field via an expression? It will either be a valid date or 0. If it is zero I want to make it a null.
View 1 Replies View Related
Chaps,
apologies for the drip-drip approach......
Is it possible to do pattern matching against a string using FINDSTRING or similar in a derived column expression without recourse to including 3rd party regexp style plugins?
I want to seach for a reference in a string which will have the format ANNNNNAAA ie. an alpha with a certain value followed by 5 digits followed by three alphas with specific values.
eg Z00001YYY or X00022HHH
thanks for your assistance,
regards,
Chris
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
Can anyone show how to alter the value in a column using DerivedColumn component when creating an SSIS package programatically.
View 4 Replies View Related
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
I have a report which contains a parameter called SuppressZero which depending on its value I want to filter out certain data. This parameter can have 3 different values and for each value I need to have a different filter expression. What I would like to do is implement the following:
If SuppressZero = 1
Filter where Quantity <> 0
If SuppressZero = 2
No Filter
If SuppressZero = 3
Filter where Quantity <> 0 Or InStockFamily = "Y"
How can I do this in my report?
This is a very weird issue, and I can't seem to pinpoint the actual cause. I have a dynamic expression for Excel files that gets evaluated at run-time. One of the parameters for creating this path is pulled from the database - the column is nvarchar(10) and most of the values are only 2-characters long. I have tried LTRIM(RTRIM()) inside SQL Server as well as TRIM() in SSIS's Expression Editor, but nothing seems to work. At run-time the package fails, and the error I receive is because the value that gets pulled from the DB has trailing spaces. What's even annoying is the package runs fine on my XP machine, but fails on a W2K3 box - both setups are using the same back-end database.
Is this is a bug, or am I just screwing up something unknowingly? Thanks.