Your Azure data factory sink might not work as expected, have you not considered these settings already

   




ADF is a very convinient way of moving data from external system to Azure SQL storages (Azure SQL, Azure MySQL, Azure cosmos DB, Cassandra: not to mention blob, table and file systems), without writing any code. In fact dataflows are so convinent ways of handling data, that it definitely does have an upperhand as compared to Azure Synapses, where you need to write a lot of staff in Python on JuPyter Notebook to instigate the cleaning and transformation processes.

However your ADF dataflows could fail very weirdly, if you have not considered these options. These happen typically when any of your Source/sink involves an Azure SQL connection:

A. Missing property error:

Your piepline gave a nasty error as: 

What?!!! Wait -- I have validated the pipeline and published everything without any errors. Even then, why on earth, did it happen?
Solution: While creating the sink/source Linked Service, you need to select 'Legacy' instead of 'Recommended' in Version parameter:


B. Result has Zero output columns:
Your pipeline got this error, while running:

Now, there are several reasons possible.
I. Allow schema drift, while defining sink/source: You need to set the schema drift, while defining the source/sink settings:

 
II. Be mindful of what are you mapping: yes, you need to remember there is a difference between 'Currency' and 'currency'. While T-SQL syntanxes won;t mind this, but for ADF it would be considered as an unknown field. For example, the below example shows a currency field, which is a Dereived column from a previous step, but needs to be 'Currency' column of AzureSQL:


 C.Allowing Update/Upsert on AzureSQL table: if you have selected to include 'Allow Update' /Allow upsert on your Sink, you need to add 'An alter row transformation' logic:



This guy is so cool, that it just creates an intermediate block before your Sink module, that takes care of the 'Update' logic, without your needing to write anything:


Click on this new block and go to the settings to append the Alteration condition (the below figure shows an 'upsert if' and 'delete if' expression conditions, which say to update/insert if the Key column, in our case CustomerNo, is not empty) accordingly:




D. The key column is not specified on Sink: this is easy. You come to Sink >> Settings >> Check if the key column is still there or  not. If not re-select the key column and validate again. The error will go. 




Comments

Post a Comment

Popular posts from this blog

X++ : mistakes which developers commit the most

Image
   The most important thing is to remember where and when to write the code. a. Developers have an inherent habit of writing erratic select statements: select * from custTable where custTable.AccountNum == <SomeValue> Where in essence we need to make use of optimized choice of fields in the select statement. b. Making use of 'continue' keywords: While select custTrans where <someConditions> { if (cusTTrans.CustGroup != mappingTable.CustGroup) { continue; } } 'Continue' statements are precisely a very unhygienic way of designing your code. The above statement could have been easily ornamented by using exists/not exists joins. Using Continue statements would eventually result in a high performance impact. Also select statements should be made punctuated with a 'firstonly' clause -- just not from best practise standpoint, but also from performance considerations. c. Writing nested loops: This is a common mistake that perha
Read more

Make your menu items visible on main menu, conditionally,, using this cool feature of D365FO

Image
  Wassup guys? I am sure you must have encountered this situation where you needed to display your menuItems, on main menu, based on some condition, haven't you? This was only possible by using config keys and flights. Things get a bit tricky,  when suppose you have a requirement like this: you need to make it dynamic, by introducing a toggle switch on a field on a paremeter form.  Suppose on Accounts Receivables Parameters >> You have a field called ' Show US customer '. You have a requirement that says when you turn on this toggle, then only the menuItem ' Input SSN details ', under AR module. This is now highly acheivable by leveraging subscription to 'SysMenuNavigationObjectFactory' class, simply by saying a. Create a new class USMenuItemsVisibilityManager b. Copy the following method, with proper documentation (don't change signature/anything else) [SubscribesTo(classstr(SysMenuNavigationObjectFactory), staticdelegatestr(SysMenuNavigationObjec
Read more

Speed up your execution performance by using SysGlobalCaches

Image
  Ever come across a situation where you needed to  a. Using a multi-threaded batch to import a journal header and lines, from a staging table. The table already contains the Journal Ids which you need to assign to the header and allocate the lines under that header. b. You need to continuously check after each line being imported, if the journal with the Id has been created. If not, it will create the header and create the lines under it;  If yes, it will directly create the lines for the header (Find or create). c. As you could rightly guess, this architecture has an inherent sluggishness associated with it -- each time you keep on checking if the header has been created or not, it does mean a lot of performance effects, as a consequence. Also as you can understand it's a multithreaded code, which means each thread has no direct relation with the other -- which does mean there is no way I can talk between the threads/pass on any information. SysGlobalCaches to the rescue: I perso
Read more