Forum Widgets
Latest Discussions
Azure Data Factory - help needed to ingest data, that uses an API, into a SQL Server instance
Hi, I need to ingest 3rd-party data, that uses an API, into my Azure SQL Server instance (ASSi) using Azure Data Factory (ADF). I'm a report developer so this area is unfamiliar to me, although I have previously integrated external, on-premise SQL Server data into our ASSi using ADF so I do have some exposure to the tool (just not API connections). The 3rd-party data belongs to a company named 'iLevel' (in case this is relevant). iLevel have provided some API documentation which is targeted for an experienced data engineer that understands API connections. This documentation has just a few sections before the connection focused details end. I'll list them below: 1) It mentions to download 'Postman Collection' and mentions no more than this. I've never heard of Postman Collection and I don't know why it's needed. Through limited exposure online, I don't understand its purpose, certainly not in my scenario. 2) It has the title 'Access to the API' and then lists four URLs which are the endpoints (I don't know which to use and will need to ask iLevel of this but, I guess, any will do for testing purposes). 3) Authentication and Authorization a) Generate a 'client id' and 'client secret' by logging into iLevel and generating these values with some clicks of a button. I've successfully generated both these values. b) Obtain an Access Token - it'll be easier to screenshot the instructions for this (I've blanked part of the URL for confidentiality). These are all the instructions on connection to the 3rd-party data. Unfortuntely for me, my lack of experience in this area means these instrcutions don't help me. I don't believe I'm any closer to connecting to the 3rd-party source data. Taking into consideration the above instructions but choosing to try and error in ADF, a tool I'm a little bit more familiar with, I've performed the following steps: 1) Created a Linked Service. I understand the iLevel solution is in the cloud and therefore the 'AutoResolveIntegrationRuntime' option has been selected as the 'Connect via integration runtime' value. For the 'Base URL' I've entered one of the four URL endpoints that were listed in the documentation (again, I will need to confirm which endpoint to use). The 'Test Connection' returns a successful result but I think it means nothing because if I were to placed 'xxx' at the end of the Base URL and test the connection, it stills returns successful when I know the URL with the 'xxx' post-fix isn't legit. 2) Create an ADF Pipeline containing 'Web activity' and 'Set variable' objects. The only configuration under the Web activity is the 'Settings' pane which has: The 'Body' property has (the client id and client secret taken from the iLevel solution are included in the body but blanked out): If the Web activity is successful then the Pipeline's next object (the Set variable) should assign the access token to a variable - as I understand this is what the Web activity is doing: The 'Value' property has: This is as far as I've got into my efforts of this integration task because the Web activity object fails when executed. The error message does state it is to do with an invalid 'client id' or 'client secret' - see below: You may direct to focus on the incorrect client id or client secret, however I don't have any confidence that I understand how to configure ADF to obtain an access token, and I'm maybe missing something if I see no need for Postman Collection use. What is Postman Collection and do I need it for what I'm trying to achieve? If yes, can anyone provide training material that suits my need? Have I configured ADF correctly and it is indeed an issue with the client id or client server, or is the error message received just a by-product of an incorrect ADF configuration? Your help will be most appreciated. Many thanks.
ADF unable to ingest partitioned Delta data from Azure Synapse Link (Dataverse/FnO)
We are ingesting Dynamics 365 Finance & Operations (FnO) data into ADLS Gen2 using Azure Synapse Link for Dataverse, and then attempting to load that data into Azure SQL Database using Azure Data Factory (ADF). This is part of a migration effort as Export to Data Lake is being deprecated. Source Details Source: ADLS Gen2 Data generated by: Azure Synapse Link for Dataverse (FnO) Format on lake: Delta / Parquet Partitioned folder structure (e.g. PartitionId=xxxx) Destination: Azure SQL Database Issue Observed in ADF When configuring ADF pipelines: Using ADLS Gen2 dataset with: Delta / Parquet Recursive folder traversal Wildcard paths We encounter: No data returned in Data Preview Or runtime error such as: “No partitions information found in metadata file” Despite this: The data is present in ADLS The same data can be successfully queried using Synapse serverless SQL Key Question for ADF / Synapse Engineers What is the recommended and supported ADF ingestion pattern for: Partitioned Delta/Parquet data produced by Azure Synapse Link for Dataverse Specifically: Should ADF: Read Delta tables directly, or Use Synapse serverless SQL external tables/views as an intermediate layer? Is there a reference architecture for: Synapse Link → ADLS → ADF → Azure SQL Are there ADF limitations when consuming Synapse Link–generated Delta tables? Many customers are now forced to migrate due to Export to Data Lake deprecation, but current ADF documentation does not clearly explain how to replace existing ingestion pipelines when using Synapse Link for FnO. Any guidance, patterns, or official documentation would be greatly appreciated.
Copy Data Activity Failed with Unreasonable Cause
It is a simple set up but it has baffled me a lot. I'd like to copy data to a data lake via API. Here are the steps I've taken: Created a HTTP linked service as below: Created a dataset with a HTTP Binary data format as below: Created a pipeline with a Copy Data activity only as shown below: Made sure linked service and dataset all working fine as below: Created a Sink dataset with 3 parameters as shown below: Passed parameters from pipeline to Sink dataset as below: That's all. Simple, right? But the pipeline failed with a clear message "usually this is caused by invalid credentials." as below: Summary: No need to worry about the Sink side of parameters etc. which I have used same thing for years on other pipelines and all succeeded. This time the API failed to reach a data lake from source side as said "invalid credentials". In Step 4 above one could see the linked service and dataset connections were succeeded, ie. credentials have been checked and passed already. How come it failed in data copy activity complaining an invalid credentials? Pretty weird. Any advice and suggestions will be welcomed.User Properties of Activities in ADF: How to add dynamic content in it?
On ADF, I am using a for each loop in which I am using an Execute Pipeline Activity which is getting executed for different iterations as per the values of the items provided to the For-Each Loop. I am stuck on a scenario which requires me to add the Dynamic Content Expression in the User Properties of individual activities of ADF. Specific to my case, I want to add the Dynamic Content Expression in the User Properties of Execute Pipeline Activity so that I get to individual runs of these activities on Azure Monitor with a specific label attached to it through its User Properties. The necessity to add the Dynamic Content Expression in the User Properties is due to the reason that each execution in respective iterations of these activities corresponds to a particular Step from a set of Steps configured for the Data Load Job as a whole, which has been orchestrated through ADF. To identify the association with the respective Job-Step, I require to add Dynamic Content Expression in its User Properties. Any sort of response regarding this is highly appreciated. Thank You!
Dataflow snowflake connection issue
I'm trying to set up a sink to snowflake in dataflow, but when I test the connection it doesn't work It just comes out JDBC driver communication error, tried searching online and looking at documentation link but couldn't find anything about this issue. But this same dataset works fine outside of the dataflow, I can preview the data in same dataset: There seems to be issue with dataflow. Even when I try to execute the dataflow through pipeline the same error message comes up: Does anyone know how to solve this problem with dataflows? Also with the sink settings option if I select recreate table will it create the table in snowflake if it doesn't already exist? Trying to find an easy way to copy a lot of tables into snowflake without explicitly having to create the table first especially if the metadata is only known at runtime, the pipeline copy job doesn't work as the table has to exist before it can insert data into it, but dataflow seems promising if the connection actually works.
Oracle 2.0 Upgrade Woes with Self-Hosted Integration Runtime
This past weekend my ADF instance finally got the prompt to upgrade linked services that use the Oracle 1.0 connector, so I thought, "no problem!" and got to work upgrading my self-hosted integration runtime to 5.50.9171.1 Most of my connection use service_name during authentication, so https://learn.microsoft.com/en-us/azure/data-factory/connector-oracle?tabs=data-factory, I should be able to connect using the Easy Connect (Plus) Naming convention. When I do, I encounter this error: Test connection operation failed. Failed to open the Oracle database connection. ORA-50201: Oracle Communication: Failed to connect to server or failed to parse connect string ORA-12650: No common encryption or data integrity algorithm https://docs.oracle.com/error-help/db/ora-12650/ I did some digging on this error code, and the troubleshooting doc suggests that I reach out to my Oracle DBA to update Oracle server settings. Which, I did, but I have zero confidence the DBA will take any action. https://learn.microsoft.com/en-us/azure/data-factory/connector-troubleshoot-oracle Then I happened across this documentation about the upgraded connector. https://learn.microsoft.com/en-us/azure/data-factory/connector-oracle?tabs=data-factory#upgrade-the-oracle-connector Is this for real? ADF won't be able to connect to old versions of Oracle? If so I'm effed because my company is so so legacy and all of our Oracle servers at 11g. I also tried adding additional connection properties in my linked service connection like this, but I have honestly no idea what I'm doing: Encryption client: accepted Encryption types client: AES128, AES192, AES256, 3DES112, 3DES168 Crypto checksum client: accepted Crypto checksum types client: SHA1, SHA256, SHA384, SHA512 But no matter what, the issue persists. :( Am I missing something stupid? Are there ways to handle the encryption type mismatch client-side from the VM that runs the self-hosted integration runtime? I would hate to be in the business of managing an Oracle environment and tsanames.ora files, but I also don't want to re-engineer almost 100 pipelines because of a connector incompatability.Data flow sink to Blob storage not writing to subfolder
Hi Everybody This seems like it should be straightforward, but it just doesn't seem to work... I have a file containing JSON data, one document per line, with many different types of data. Each type is identified by a field named "OBJ", which tells me what kind of data it contains. I want to split this file into separate files in Blob storage for each object type prior to doing some downstream processing. So, I have a very simple data flow - a source which loads the whole file, and a sink which writes the data back to separate files. In the sink settings, I've set the "File name option" setting to "Name file as column data" and selected my OBJ column for the "Column Data", and this basically works - it writes out a separate file for each OBJ value, containing the right data. So far, so good. However, what doesn't seem to work is the very simplest thing - I want to write the output files to a folder in my Blob storage container, but the sink seems to completely ignore the "Folder path" setting and just writes them into the root of the container. I can write my output files to a different container, but not to a subfolder inside the same container. It even creates the folder if it's not there already, but doesn't use it. Am I missing something obvious, or does the "Folder path" setting just not work when naming files from column data? Is there a way around this?
Problem with Linked Service to SQL Managed Instance
Hi I'm trying to create a linked Service to a SQL Managed Instance. The Managed Instance is configured with a Vnet_local endpoint If I try to connect with an autoresolve IR or a SHIR I get the following error The value of the property '' is invalid: 'The remote name could not be resolved: 'SQL01.public.ec9fbc2870dd.database.windows.net''. The remote name could not be resolved: 'SQL01.public.ec9fbc2870dd.database.windows.net' Is there a way to connect to it without resorting to a private endpoint? Cheers AlexADF connection issue with Cassandra
Hi, I am trying to connect a cassandra DB hosted in azure cosmos db. I created the linked service but getting below error on test connection. Already checked the cassandra DB and its public network access is set to all networks. Google suggested enabling SSL but there is no such option in linked service. Please help. Failed to connect to the connector. Error code: 'Unknown', message: 'Failed to connect to Cassandra server due to, ErrorCode: InternalError' Failed to connect to the connector. Error code: 'InternalError', message: 'Failed to connect to Cassandra server due to, ErrorCode: InternalError' Failed to connect to Cassandra server due to, ErrorCode: InternalError All hosts tried for query failed (tried 51.107.58.67:10350: SocketException 'A request to send or receive data was disallowed because the socket is not connected and (when sending on a datagram socket using a sendto call) no address was supplied')
