CODEWITHSUNDEEP
sqloracle-databasepowershellazure-data-factory
Obongo
Obongo

Reputation: 47

Azure Data Factory v2 Not Null Columns in sink

I'm trying out Azure Data Factory v2 and I want to pipe data from an SQL source to an Oracle sink.

My problem is, that I have several Not Null columns in my Oracle tables which specify for example the date and time of which a dataset is loaded into Oracle. These columns however aren't existant in the SQL tables so when I want to start the pipeline I get the error that these columns can't be null in the Oracle sink.

My question is now, is it possible to artificially add these columns during the pipeline run so that these columns get filled by the Data Factory?
Can I use a stored procedure or a custom activity for that?
Or do I have to create a Powershell script which "hardcodes" the values I want to add to the source?

Upvotes: 1

Views: 2300

Answers (2)

Kyle Bunting
Kyle Bunting

Reputation: 176

You can accomplish this in ADFv2 using a query against your source dataset in the Copy activity to insert values.

Using the table ex_employee, with the following configuration in each database:

Source table (SQL):

ID int not null,
Name nvarchar(25) not null

Sink table (Oracle):

ID number(p,0) not null,
Name nvarchar2(25) not null,
CreatedDate timestamp not null

In the Source configuration on your Copy activity in ADF, you would select the Query option under Use Query, and input a query, such as:

SELECT ID, Name, CURRENT_TIMESTAMP AS CreatedDate FROM ex_employee

This will take the existing values from your SQL table, and insert a default value into the result set, which can then be inserted into your Oracle sink.

Upvotes: 1

Moudiz
Moudiz

Reputation: 7377

Does this column has default value ? can you add default to this column then try? I not familiar with oracle pipe data however a similar approach in the below example adding a default value to a not null column.

drop table ex_employee
/
create table ex_employee (id number(1) null ,name varchar2(100)  default 'A' not null )
/
insert into ex_employee(id)
select 1 from dual
/
commit
/

selecT * from ex_employee where id=1

Upvotes: 1

Related Questions