Hi All,

I am trying to create ETL using store procedure .

Please helo me to create derived column in qyery

add columns
TimeIn =GETDATE()
BBxKey =100
LatestVersion=1

Thanks in Advance

Regards,

Vipin jha

Hi Vipin,

If these values are constants then you can make SQL query inside Stored Procedure like below.

SELECT Col1
	, Col2
	, getdate() AS 'TimeIn'
	, 100 AS 'BBxKey'
	, 1 AS 'LatestVersion'
	, Col3
FROM YourTable

You can also use SSIS Derived Column Task for same work.

Hi Vipin,

If I understand correctly, your OLE DB Source stored procedure already returns some columns, then you want to create three columns as new columns.

If in the scenario, we can use Derived Column Transformation to add three new columns. The following screenshots are for your references:

If there are any other questions, please feel free to ask.

Thanks,
Katherine Xiong

HI Rana ,

as you can see that BBXkey column you have hard coded 100,

I am loading 100 of source query using singl DFT, so the expression of BBxKey will be changed on every expression.

for example

when source A then expression of BBxKry will be Substring(Col001,1,7)

when source B then expression of BBxKry will be Substring(Col001,1,7) +(Col002,1,10)

when source C then expression of BBxKry will be Substring(Col002,1,7)

as you can see that I want to change the expression as the source changed.

please suggest .

Regards,

Vipin jha

Hi Vipin,

Are you use a variable as the OLE DB Source, so the source table is dynamic based on the variable value? If in this scenario, we can use the expression like below for the derived column BBxKey:
@[User::source]=="A"?Substring(Col001,1,7): @[User::source]=="B"?(Substring(Col001,1,7)  +Substring(Col002,1,10)): @[User::source]=="C"?Substring(Col002,1,7): ""

If there are any other questions, please feel free to ask.

Thanks,
Katherine Xiong

(@[User::source]=="A"? Substring(Col001,1,7): (@[User::source]=="B"?(Substring(Col001,1,7)  +Substring(Col002,1,10)): 
(@[User::source]=="C"?Substring(Col002,1,7): "")))

Cant we take it dynamically , because if the new fiulw come in , then developer have to open the package nad need to do it manually