Unable to Read Input Schema in Sink settings tab in Data Flow in Azure Data Factory

Question

question

KrishnamohanNadimpalli-6337 asked Apr 26, '22 ShaikMaheer-MSFT commented May 2, '22

Unable to Read Input Schema in Sink settings tab in Data Flow in Azure Data Factory

I have a source, derived column , Sink transformation in my data flow.

Derived Column transformation , I am getting some Year and Month from Source.

But I cannot see these(Year and month) column names in Sink settings tab when using file name option as pattern. But these column names are available in Optimize Tab of Sink.

I want file names as fact_combined_pnl_$Year_$Month in the $Year/$Month folder. Please check following screenshots

Attaching Derived Column Settings, Sink Settings and Expression Builder, Sink Optimize

Please suggest

azure-data-factory

derived-col-settings.png (39.2 KiB)

sink-settings.png (50.6 KiB)

exp-builder-settings-sink.png (35.0 KiB)

sink-optimize.png (46.2 KiB)

· 1

5 |1600 characters needed characters left characters exceeded

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

KrishnamohanNadimpalli-6337 · Apr 27 at 01:57 AM

@AnnuKumari-MSFT Can you help me on this? Thanks in advance

0 ·

Answer 1 · 2022-04-27T09:15:27.647Z

ShaikMaheer-MSFT answered Apr 27, '22 ShaikMaheer-MSFT commented May 2, '22

Hi @KrishnamohanNadimpalli-6337 ,

Thank you for posting query in Microsoft Q&A Platform.

As per my understanding, you want to write expression for filename. But not able to use column names inside expression. Please correct me If I am wrong.

While writing expressions for file names we cannot use column names there. We can use parameters there. So we should have our desired values in to parameters and use them in to file names.

Please check below screen shots which gives better idea.

Hence, please consider having your year and month values in to dataflow parameters.

Also, you may consider using cache lookups. Please check this video to understand cache lookups and cache sinks and see if that helps. Thank you.

Hope this helps. Please let us know if any further queries.

Please consider hitting Accept Answer button. Accepted answers help community as well.

image.png (231.5 KiB)

image.png (287.9 KiB)

· 3

5 |1600 characters needed characters left characters exceeded

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

KrishnamohanNadimpalli-6337 · Apr 27 at 10:02 AM

But if I do this way I am getting my output with default parameter values which was given in data flows. Also I am not getting these parameter values in dropdown of column in Derived column. @ShaikMaheer-MSFT please advise

I am getting output as this . Year and month is default values

0 ·

image.png (34.9 KiB)

image.png (63.6 KiB)

image.png (7.9 KiB)

ShaikMaheer-MSFT KrishnamohanNadimpalli-6337 · Apr 27 at 10:10 AM

Hi @KrishnamohanNadimpalli-6337 ,

Parameters and column names are different. Hence you wont see them in derived column transformation dropdowns. In derived column transformation you can manually write down new column values. Hope this helps.

Please consider hitting Accept Answer button. Accepted answers help community as well.

0 ·

ShaikMaheer-MSFT ShaikMaheer-MSFT · May 02 at 09:48 AM

Hi @KrishnamohanNadimpalli-6337 , As above answer helps. Could you please consider marking it as Accepted Answer? Accepted answers helps community as well. Please let us know if any further queries.

0 ·

question