ADF dataflow data Preview Error
hi All, I have data flow as seen below. all linked service and data set working fine and i can see the data preview but wheb i use the same linked service and dateset in the dataflow It throw error as shown below i am useing managed private endpoint to coonect the blob starga it is owrking for all pipe line. the ADF and the MI has staorgae account contributor role assigned. Error: at Source 'sourcedata': This request is not authorized to perform this operation. When using Managed Identity(MI)/Service Principal(SP) authentication 1. For source: In Storage Explorer, grant the MI/SP at least Execute permission for ALL upstream folders and the file system, along with Read permission for the files to copy. Alternatively, in Access control (IAM), grant the MI/SP at least the Storage Blob Data Reader role. 2. For sink: In Storage Explorer, grant the MI/SP at least Execute permission for ALL upstream folders and the file system, along with Write permission for the sink folder. Alternatively, in Access control (IAM), grant the MI/SP at least the Storage Blob Data Contributor role. Also please ensure that the network firewall settings in the storage account are configured correctly as turning on firewall rules for your storage account blocks incoming requests for data by default, unless the requests originate from a service operating within an Azure Virtual Network (VNet) or from allowed public IP addresses. Any kind of help is highly appreciated
Excel column header verification using schema in database
I have a requirement where we need to do data quality check on the excel files in Azure Blob with the Schema stored in the Database. Azure Blob has a container in which we have multiple excel files with data. These files generally follow a structure and few business rules, for example, if the data is related to employee there will be 10 columns, all rows in colA = 'abc' (same data), colB should be date in some format, colC is number and less than 5 and likewise. Similarly different excels have different headers, no of columns, structure and business rules. A table is maintained in the database with the structure and business rules. ExcelTemplateId ExcelTemplateName ColumnName MaxLength DataType DefaultValue 1 abc name 255 varchar 1 abc empId 10 int 1 abc dept 100 xyz I need to create an adf pipeline which will read the excel files one by one from the source and compare with the schema (present in the database) and copy the good data to location01 and bad data to location02. Location01 and 02 can be a table in database. I do not wish to create one pipeline for each excel sheet, rather it should be a dynamic one which would handle all excels. How can I achieve this?
OData Connector for Dynamics Business Central
Hey Guys, I'm trying to connect Dynamics Business Central OData API in ADF but I'm not sure what I'm doing wrong here because the same Endpoint is returning data on Postman but returning an error in ADF LinkedService. https://api.businesscentral.dynamics.com/v2.0/{tenant-id}/Sandbox-UAT/ODataV4/Company('company-name')/Chart_of_AccountsHow do I unpivot with schema drift enabled?
I have a source without a pre-defined schema. I derive each column name using a column pattern expression: Data preview shows what I expect (which is a file in a blob container): I then have a Select step that selects each column and renames 'Marker name' to 'Marker_name22': Data preview again shows what I expect (same columns with 'Marker name' renamed). Now in the unpivot step, I would like to ungroup by the 'Marker_name22' column and unpivot all other columns, but the 'Marker_name22' column is not available: I am unsure how to proceed from here. Thanks in advance for the help.
Manage Pipelines (Start/Stop/Monitoring)
I cannot find a way to manage many pipelines for ETL. For example, in case of multiple pipelines execution, if i want to disable the execution of any pipelines - how can this be done ? Is there a tool by Microsoft or any third party tool, which can help manage, execution and monitoring of pipelines in ADF ? Also, are there any best practices or patterns to manage multiple pipelines ?
ForEach Activity: Immediate Pipeline Failure on Any Child Activity Failure
Hello Azure community, I’m working with azure data factory and have a pipeline set up with a ForEach activity that runs three activities in parallel: 1. Notebook A 2. Execute Pipeline B 3. Notebook C My requirements is to ensure that if any one of these activities fails (e.g. Child Activity A or B or C fails after 2mints of pipeline start) the entire pipeline should fail immediately, regardless of the status of other activities (still running). Could you please guide me on how to achieve this behaviour? Thank you for assistance!
'Cannot connect to SQL Database' error - please help
Hi, Our organisation is new to Azure Data Factory (ADF) and we're facing an intermittent error with our first Pipeline. Being intermittent adds that little bit more complexity to resolving the error. The Pipeline has two activities: 1) Script activity which deletes the contents of the target Azure SQL Server database table that is located within our Azure cloud instance. 2) Copy data activity which simply copies the entire contents from the external (outside of our domain) third-party source SQL View and loads it to our target Azure SQL Server database table. With the source being external to our domain, we have used a Self-Hosted Integration Runtime. The Pipeline executes once per 24 hours at 3am each morning. I have been informed that this timing shouldn't affect/or by affected by any other Azure processes we have. For the first nine days of Pipeline executions, the Pipeline successfully completed its executions. Then for the next nine days it only completed successfully four times. Now it seems to fail every other time. It's the same error message that is received on each failure - the received error message is below (I've replaced our sensitive internal names with Xs). Operation on target scr__Delete stg__XXXXXXXXXX contents failed: Failed to execute script. Exception: ''Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Cannot connect to SQL Database. Please contact SQL server team for further support. Server: 'XX-azure-sql-server.database.windows.net', Database: 'XX_XXXXXXXXXX_XXXXXXXXXX', User: ''. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access.,Source=Microsoft.DataTransfer.Connectors.MSSQL,''Type=Microsoft.Data.SqlClient.SqlException,Message=Server provided routing information, but timeout already expired.,Source=Framework Microsoft SqlClient Data Provider,'' To me, if this Pipeline was incorrectly configured then the Pipeline would never have successfully completed, not once. With it being intermittent, but becoming more frequent, suggests it's being caused by something other than its configuration, but I could be wrong - hence requesting help from you. Please can someone advise on what is causing the error and what I can do to verify/resolve the error? Thanks.Can an ADF Pipeline trigger upon source table update?
Hi, Is it possible for an Azure Data Factory Pipeline to be triggered each time the source table changes? Let's say I have a 'copy data' activity in a pipeline. The activity copies data from TableA to TableB. Can the pipeline be configured to execute whenever source TableA is updated (a record deleted, changed, a new record inserted, etc..)? Thanks.
