question

RohitKulkarni-3496 avatar image
0 Votes"
RohitKulkarni-3496 asked RohitKulkarni-3496 commented

column display current date

Hello team,

I want to copy the data from sql server table to synapse sql pool via dynamic query.

Source Destination Values
column 1 column1 ABC
column2 column2 123
column3 column3 BANGALORE
column4 column4 SYSDATE


For column 4 i need to display the sysdate on the fly. How it can be achieved .Please advise.

Reards
RK

azure-data-factory
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

NandanHegde-7720 avatar image
0 Votes"
NandanHegde-7720 answered RohitKulkarni-3496 commented

Hey ,
Below are the ways to achieve it:
1) In the source itself update your query as
Select column1,column2,column3,getdate() as column3

2) https://docs.microsoft.com/en-us/answers/questions/318521/how-do-i-pass-a-static-value-as-source-column-in-c.html
wherein add additional columns with static values in copy activity.
You can use utcnow() function

· 12
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I don't want to create additional column in the existing column i need to add the Getdate() Is it possible

0 Votes 0 ·

Hey,
Just to clarify your ask:
While copying data from SQL server to synapse , you want Getdate() value added in column4 in synapse correct?
If that is the need, then you can follow the 2nd link which I have provided wherein your input would be utcnow() and destination would be column4 of synapse

0 Votes 0 ·

This option is working for me .But the date column is getting updated for only few rows and rest other rows for the date column is null

and even Getdate() function is not there

0 Votes 0 ·

I have written the same in select statement in source but it not reflecting in the target.


select * ,
cast(Getdate() as datetime) as [LASTIMPORTDATETIME]
from @{pipeline().parameters.SourceSchema}.@{pipeline().parameters.SourceViewName}
where @{pipeline().parameters.SourceViewName}.MainAccountCATEGORY= '@{pipeline().parameters.CompanyName}'

80406-image.png


0 Votes 0 ·
image.png (77.0 KiB)

can you also share the images of your sink and Mapping as well ?
I guess you might have missed out mapping the getdate() column in source with the sink

0 Votes 0 ·

Sink details

80492-image.png


0 Votes 0 ·
image.png (61.9 KiB)

Mapping details!

80420-image.png


0 Votes 0 ·
image.png (55.7 KiB)

In the mapping: the LastImpirtDatetime column is missing because of which the getdate() is not inserted into synapse

0 Votes 0 ·
Show more comments