Bcp Into View With Derived Column

Jan 10, 2006

Hi,

I have a view that looks something like this -

CREATE VIEW myview AS SELECT
myudf(col1) as col1, col2, col3
FROM mytable

The view has an 'INSTEAD OF' trigger on it to do the correct thing on
insert. When I run the bcp, it runs successfully, but the value
inserted into col1 is always NULL. I modified my trigger to get a
better idea of what was happening, and when I look at the values of
INSERTED.col1, they are all null. It appears that bcp is setting the
column value to null, presumable because the view definintion for this
column is a derived column.

Does any one know a way around this?

Thanks!

View 5 Replies


ADVERTISEMENT

Integration Services :: How To Declare Multiple Derived Column In SSIS Derived Column Task

Jul 22, 2015

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 Related

Integration Services :: Add New Column Based On Existing Column Using Derived Column Task?

Jul 28, 2015

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".

View 4 Replies View Related

Can Someone Help Me Create A Derived Field In A View

May 22, 2008



I am a C# developer who was forced to use a DataSet with Infragistics grid instead of the class object I was using as a data source, so that I can implement an AddNew functionality directly in the grid.

In code I would simply add a read only property to do this, and it was much simpler, but if someone could point in the right direction as to can a view do what I want.

The field I want to create is called "Info" short for HasInfo.
Info has 3 values (from an enumeration) NoInfo = 0; HasResume = 1; HasHomeInfo = 2

The business logic for this is if we have a resume, I show a resume icon in the grid, if we do not have a resume, if we have home phone # or mobil # then an icon of phones are shown in the grid.

To find out if a contact has a resume the SQL is going to be something like if exists (select ContactResumeID from ContactResume Where ContactResume.ContactID = Contact.ContactID) then 1; and if that is not true; check if Contact.HomePhone is Not Null Or Empty or Contact.WorkPhone is not null or empty then 2 else 0

I can do this in C# blindfolded, but in SQL I am fish out of water for compound if statements.

The SQL for this view is shown below (creating using SQL Server Gui Selector which I think is real cool).


SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER VIEW [dbo].[Employees]

AS

SELECT dbo.Company.CompanyID,

dbo.Company.Name AS CompanyName,

dbo.Contact.FirstName,

dbo.Contact.LastName,

dbo.Position.Title,

dbo.Department.DepartmentName,

dbo.ContactPosition.WorkPhone,

dbo.ContactPosition.Extension,

dbo.Contact.ContactID,

dbo.ContactPosition.ContactPositionID,

dbo.Department.DepartmentID,

dbo.Position.PositionID

FROM dbo.ContactPosition INNER JOIN dbo.Contact

ON dbo.ContactPosition.ContactID = dbo.Contact.ContactID INNER JOIN dbo.Position

ON dbo.ContactPosition.PositionID = dbo.Position.PositionID INNER JOIN dbo.Department

ON dbo.Position.DepartmentID = dbo.Department.DepartmentID INNER JOIN dbo.Company

ON dbo.Position.CompanyID = dbo.Company.CompanyID

Thanks,

Flying Elephant Software

View 4 Replies View Related

Cast/Convert Mmddyy In String To New DB_DATETIMESTAMP Column In Derived Column Transformation

Mar 5, 2007

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

View 7 Replies View Related

Derived Column Usage When Column Does Not Exist In Source (but Exists In Destination)

Sep 11, 2007





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

View 1 Replies View Related

Altering Column Values Using Derived Column Component

Dec 21, 2007

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

Derive A Column Based On Other Derived Column In A Table .. Is It Possible ?

Mar 25, 2008


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

View 3 Replies View Related

Add Values To A Column With Derived Column Expression?

Feb 25, 2008

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

View 10 Replies View Related

Derived Column

May 6, 2008

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 Related

Derived Column

Jan 30, 2007

I 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

View 20 Replies View Related

Derived Column

Dec 3, 2007

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


View 4 Replies View Related

Derived Column

Nov 2, 2007

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

View 13 Replies View Related

Derived Column Help

Jul 16, 2007

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

View 13 Replies View Related

Derived Column

Mar 31, 2008



I am trying to create a derived column from two separate fields. One is a date field in format of 2008-03-01 and one is a time field. In DTS I use to have something like this:

DTSDestination("TransactionDate") = cdate(DTSSource("DSRHDATI")) & " " & timevalue(DTSSource("DSRHTIME"))

I am trying to figure out how to do this in SSIS. I know I have to use a dervived column but can't seem to come up with the correct syntax. Any help would be apprecaited. thanks.

Stacy

View 4 Replies View Related

Calculating Column W/ Another Derived Column

Jun 6, 2007

Can someone help me with the following query? I need to take the derived value from one column and calculate another column with it.

SELECT UserID,

((SELECT COUNT(*) FROM HPN_LeadMatches)+(SELECT COUNT(*) FROM HPN_Painters)) As Total,

(SELECT COUNT(*) FROM HPN_Leads) / Main.Total

FROM HPN_LeadMatches As Main

The error i'm getting says is unkown column 'total'. Is there another way to accomplish this?

Thanks!!

View 7 Replies View Related

Deriving A New Column From Another Derived Column

Jul 26, 2006

In a Derived Column data flow transformation, why can't I refer to a derived column (added in that same transformation) in the expression for another derived column? It seems I am forced to chain 2 DC data flows, just to do something as conceptually simple as:

a = x + y; b = a²

On a related note: Can I define a variable that is scoped to each row? Can I bind a variable in an expression to avoid creating a new row, e.g.

let a = x + y; a²

as the expression for new row b?

View 6 Replies View Related

Conditional On A Derived Column

Jan 27, 2006

Hi everyone,

I have a quizzing application where users log in, answer questions, and are ranked relative to each other. For this final ranking, I calculate their score using this formula -

score = (correct Qs answered) * 150 / (total Qs answered) + (total Qs answered)

The SQL query that i use to get this info is -


Code:

SELECT TOP 50 username, (sum(correct) * 150 / count(1) + count(1)) AS score, count(1) as totalq
FROM questionsstats
GROUP BY username
ORDER BY score DESC



This works just fine.

However, on top of this I need to put an additional restriction that only users who have at least answered 20 questions be counted in. How can I do this? Adding a simple 'WHERE totalq > 20' does not work. I get the error "Invalid column name 'totalq'.".

Surely there must be a simple way to do this?

Thanks.

View 2 Replies View Related

SSIS Derived Column

Mar 6, 2013

Two questions regarding Derived Columns in SSIS

1. In a if else expression if condition is false how do you keep the value of the source column eg: Name == "" ? "Unknown" : Name
Above will change all the non blank values to Name and not the actual value
in the Name Column eg. John

2. I have a column (unicode string)that stores date and time (The source is flat file) Is it possible to write expression to select the 1st day of month based on that date, and use this derived column as input to a table with a datetime field.

View 1 Replies View Related

Derived Attribute Column

Apr 4, 2007

Hello, first time here, first time messing with SQL.

When creating a table in SQLServer2005 can you specify a column to be for a derived attribute. example a column monthlysalary and create a column that is a derived attribute - yearly salary. Is this done when creating the table and how is the syntax ?

View 7 Replies View Related

Help In Derived Column Transformation

Nov 3, 2007

Hi All

I m designing SSIS having Data Flow as

Flat File > Derived Column Transformation > Destination Table

but while transfering record from text file to table i need some logic here

my text file is
"ID"|"Name"
1012|"10AA"
1013|"10BB"

logic is 10 should be replace by variable VID = 98

I defined this veriable as int

My ID column in database is int and Name column is varchar(50)

I try here expression like
ID != 98 ? @[User::VID] : ID
it work fine

but for varchar column I am not able to do
I got error when i write expression
SUBSTRING(Name,1,2) != "98" ? [DT_STR, 50, 1252] "(@[User::VID])" + rest of value of column : [Name]

my final output in table should be like
ID..Name
98..98AA
98..98BB


Please help me out

T.I.A

View 2 Replies View Related

ISNUMERIC In Derived Column

Feb 14, 2008

I would like to validate datatype using Derived Column.My data type are such as numeric(X,X),datetime,int, and varchar.How do I do this using Derived Column.Example if row does not qualify as ISNUMERIC()...throw it in ERROR table else send it to SUCCESS table.Any Idea ?

View 4 Replies View Related

Getting User Name In A Derived Column

Mar 29, 2006



Hi there,

is there a way that I can set the value of a column to the currrent user in a derived shape?

For instance if I want to set the value of a column to the current data I can merely use GETDATE().

What is the current user equivilent?



Thanks

View 4 Replies View Related

Derived Column Error

Apr 15, 2008

Hi,

I have just started learning SSIS. I have created a new package which is as follows :

SOURCE QUERY ----> Derived Column ------> Destination Customer Info

Source Fields are
FirstName
LastName
OrgCode
Phone
Ref1

The Derived column is adding a new Column name FullName and the friendly expression for it is FirstName + "Null" + LastName

Destination has all the columns are same as source columns with similar datatypes.

When i start the debugging process i get the following error:

[Destination - CustomerInfo [29]] Error: Column "FullName" cannot be found at the datasource.
[Destination - CustomerInfo [29]] Error: Cannot create an OLE DB accessor. Verify that the column metadata is valid.

[DTS.Pipeline] Error: component "Destination - CustomerInfo" (29) failed the pre-execute phase and returned error code 0xC0202025.

Please let me know where i am going wrong.

Thanks

View 7 Replies View Related

Derived Column In SSIS

Oct 5, 2007

Hi,
I apologize if this question has already asked (I already looked in this forum but do not find the answer), I need to have this done as soon as possible. I've following data:


column1 column2 begin_date active
------------- ------------ ---------------- -----------

1253 1057 1/1/2006 0

1253 1057 1/1/2007 0

1253 1057 4/1/2007 0

1253 1057 7/1/2007 1


I need to have the final result as following: (the new end_date column is the value of the begin_date in the next row -1)


column1 column2 begin_date active end_date
------------- ------------ ---------------- -------- ------------------

1253 1057 1/1/2006 0 12/31/2006

1253 1057 1/1/2007 0 3/31/2007

1253 1057 4/1/2007 0 6/30/2007

1253 1057 7/1/2007 1 null


Any ideas?

Thank you!

View 4 Replies View Related

Derived Column Problem

Sep 25, 2006



I want to implement all this four condition in one derived column expression.

if (Column1== 1)

{

OutputColumn = ColumnA- Column1RATE
}



if (Column2 == 1)

{

OUtputColumn = ColumnA- Column2RATE
}



if (Column3 == 1)

{

OUtputColumn = ColumnA- Column3RATE
}



if (Column4 == 1)

{

OUtputColumn = ColumnA- Column4RATE
}





please suggest

View 2 Replies View Related

Derived Column Calculation

Mar 14, 2006


Example: (47 / 204709) * 12000 = 2.754
Both values (47 & 204709) are of data type Int.
SQL Sever - Derived Column Calculation returns 2.74

The destination data type is Float

I have converted the data types to Float, Decimal & Numeric and still got the same answer.

Any suggestions

View 4 Replies View Related

Derived Column Comparison

Aug 1, 2006

I am importing States into a table and need to change all NULL fields before I perform a lookup. I'm using a derived column to replace the state value. I'd like to find all States that are blank and set it to "--".

Ironically, this works:

State != "" ? State : "--"

but

State == "" ? State : "--"

Does not work

Can someone tell me why?

View 4 Replies View Related

Help Me With Derived Column Transformation

Mar 14, 2007

I have this expression
(Registered_Units == Limited_Units) ? 0 : (Painted_Units / (Registered_Units - Limited_Units)) * 100)

It gives me 0.00, although those 3 columns have values.

if I cast it like this

(Registered_Units == Limited_Units) ? 0 : ((((DT_DECIMAL,2)Painted_Units) / ((DT_DECIMAL,2) (Registered_Units - Limited_Units))) * 100)

It gives me correct answer in whole number but .00 after decimal.


Any clue how to fix it ?

View 3 Replies View Related

Complicated Derived Column

Aug 3, 2007

I have a complicated derived column. In a Crystal report it is this:

if isnull( {YSA.YSAbA}) then
(
stringVar accountPart1 := "1";
stringVar accountPart2;
stringVar accountPart3;
stringVar accountPart4;
if left({YAAREP.A7KMCD},2) = 'aa' then
accountPart4 := '01'
else if left({YAAREP.A7KMCD},2) = 'bb' then
accountPart4 := '02'
else if left({YAAREP.A7KMCD},2) = 'cc' then
accountPart4 := '03';

select {YAAREP.A7KNCD}
case "39000": accountPart3 := "115" //this is freight
case "35000": accountPart3 := "110"
case "36000": accountPart3 := "105"
default: accountPart3 := "105";

if "SERVICE" in {YAAREP.A7KMCD}
//if MIKE RYAN
then accountPart2 := "3300";
if {YAAREP.A7KNCD} = "39000" //this is freight
then accountPart2 := "4300";
if cdbl({YAAREP.A7KNCD}) > 44000 then
accountPart2 := left({YAAREP.A7KNCD},4);
if cdbl({YAAREP.A7KNCD}) > 44000 then
accountPart3 := right({YAAREP.A7KNCD},3);
accountPart1 + "-" + accountPart2 + "-" + accountPart3 + "-" + accountPart4
)
else {@PrintDefaultSalesGL}

How should I do this in an SSIS? Or do I need to show how do this in SQL Query?

Thanks for the help!

View 3 Replies View Related

Problem In A Derived Column

Mar 26, 2007

Dear Friends,

I have an derived column in an ETL, that use the DateDiff function.

The experssion of the derived column is:

DATEDIFF("d",RKData,MaturityDate) / 365



The problem is that the result always come as an integer, and in fact the result of the expression usually has decimal numbers.. :-(

I defined the new colum of derived column as doube-precison float [DT_R8]....

Someone help me?

Thanks!

View 4 Replies View Related

Derived Column Transformation

Sep 20, 2007

Hello.
I am using Derived Column transformation for calculating the age of individual and then adding the column to my final destination. In SQL, the DOB is varchar(50) and the output column I am creating should be Integer.
Here is the expression I am using for calculating the age:
(DATEDIFF("DAY",(DT_DBTIMESTAMP)TRIM(DOB),(DT_DBTIMESTAMP)TRIM([Service Date])) / 365.25)

In SQL, I have no problems getting the age of a person, but I am having difficulties using Derived Column Transformation.
I get the following error when executing my package:

Error: 0xC0049067 at Data Flow Task, Derived Column [2086]: An error occurred while evaluating the function.Error: 0xC0209029 at Data Flow Task, Derived Column [2086]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (2086)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "_AGE" (2877)" 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.

Any assistnace would be greatly appreciated.

View 11 Replies View Related

Convertion In Derived Column

Oct 16, 2007



I have a column age ( smallint) in derived transformation .. I would likt to convert it to A or B which is char(1)
hwo can i convert smallint to char(1) in derived column?
age > 17 ?"A" : "B"

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved