Recent Discussions
Scheduled trigger is running 1second early
A scheduled trigger configured to execute every 10 minutes exhibits an issue where one of the six runs in every hour executes 1 second earlier than the expected schedule. This inconsistency impacts the timing accuracy of the trigger. My query is why is it running 1 second early? Due to this we are having issues skipping of few Jobs scheduled during that time.
Need ADF pipeline suggestion
I have an ADF pipeline that copies Files from source to destination. Both Source and destinations are different folders within adls only. My pipeline design is as follows 1.) Lookup activity- A sql server Stored procedure that returns sourcepath and the destination path. This is connected to a Foreachloop 2.) Foreachloop activity - Has 10 as the batchcount. Within this activity I have Copydata activity 3.) Copydata activity - I have the source and sink paths set from the storedprocedure output columns. Source and destination Location is ADLS gen2. It works fine but I have about 1 millions files that the stored procedure returns and it takes about 20 mins to complete 1000 rows/files to copy. What settings/config can I change to make this run faster?Azure Data Studio - SQL DB Project build error MSB4020
Hi folks. I'm just getting started with ADS as we need to move an existing on-prem DB to Azure. I 'm trying to use ADS to set up the target DB. I installed the SQL Database Projects extension and created a project from our on-prem DB, but every time I try to build it I get Error MSB4020: stdout: C:\Program Files\dotnet\sdk\7.0.403\Sdks\Microsoft.NET.Sdk\targets\Microsoft.NET.Sdk.targets(1199,3): error MSB4020: The value "" of the "Project" attribute in element <Import> is invalid. [c:\testing\WT pre-migration\WT pre-migration.sqlproj] Here's the proj file: <?xml version="1.0" encoding="utf-8"?> <Project DefaultTargets="Build"> <Sdk Name="Microsoft.Build.Sql" Version="0.2.0-preview" /> <PropertyGroup> <Name>WT pre-migration</Name> <ProjectGuid>{AE8E25C1-F1D6-4447-A008-2C24C82B51FA}</ProjectGuid> <DSP>Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider</DSP> <ModelCollation>1033, CI</ModelCollation> </PropertyGroup> <Target Name="BeforeBuild"> <Delete Files="$(BaseIntermediateOutputPath)\project.assets.json" /> </Target> </Project>
adf upsert
Hi all, I'm trying to create sample Upsert data pipeline in ADF (insert missing records and update changed ones). I created sample source and target Postgres table with all data types available, the number of source and target columns slightly differ. CREATE TABLE "schema1".source_for_upsert_0001 ( col_serial SERIAL PRIMARY KEY, col_smallint SMALLINT, col_integer INTEGER, col_bigint BIGINT, col_decimal DECIMAL(10, 2), col_numeric NUMERIC(10, 2), col_real REAL, col_double DOUBLE PRECISION, col_smallserial SMALLSERIAL, col_serial_alias SERIAL, col_bigserial BIGSERIAL, col_money MONEY, col_char CHAR(5), col_varchar VARCHAR(50), col_text TEXT, col_bytea BYTEA, col_timestamp TIMESTAMP, col_timestamptz TIMESTAMPTZ, col_date DATE, col_time TIME, col_timetz TIMETZ, col_boolean BOOLEAN, col_uuid UUID, col_json JSON, col_jsonb JSONB, col_xml XML, col_inet INET, col_cidr CIDR, col_macaddr MACADDR, col_bit BIT(8), col_varbit VARBIT(16), col_interval INTERVAL, col_point POINT, col_line LINE, col_lseg LSEG, col_box BOX, col_path PATH, col_polygon POLYGON, col_circle CIRCLE, col_tsquery TSQUERY, col_tsvector TSVECTOR ); INSERT INTO "schema1".source_for_upsert_0001 ( col_smallint, col_integer, col_bigint, col_decimal, col_numeric, col_real, col_double, col_smallserial, col_serial_alias, col_bigserial, col_money, col_char, col_varchar, col_text, col_bytea, col_timestamp, col_timestamptz, col_date, col_time, col_timetz, col_boolean, col_uuid, col_json, col_jsonb, col_xml, col_inet, col_cidr, col_macaddr, col_bit, col_varbit, col_interval, col_point, col_line, col_lseg, col_box, col_path, col_polygon, col_circle, col_tsquery, col_tsvector ) VALUES ( 1, 100, 1000, 1234.56, 1234.56, 12.34, 12345.6789, 1, DEFAULT, DEFAULT, '$1234.56', 'A', 'Sample Text', 'This is a text field.', E'\x48656c6c6f', '2024-12-13 12:00:00', '2024-12-13 12:00:00+00', '2024-12-13', '12:00:00', '12:00:00+00', TRUE, '550e8400-e29b-41d4-a716-446655440000', '{"key": "value"}', '{"key": "value"}', '<note><to>User</to><message>Hello!</message></note>', '192.168.1.1', '192.168.0.0/24', '08:00:2b:01:02:03', B'10101010', B'1010101010101010', '1 year 2 months', '(1,1)', '((0,0),(1,1))', '((0,0),(1,1))', '((0,0),(2,2))', '((0,0),(1,1),(2,2),(2,0),(0,0))', '((0,0),(1,1),(2,2),(2,0),(0,0))', '<(1,1),1>', 'cat & dog', 'cat:3A dog:2A' ); CREATE TABLE "schema1".target_for_upsert_0001 ( col_2_serial SERIAL PRIMARY KEY, col_2_smallint SMALLINT, col_2_integer INTEGER, col_2a_integer INTEGER, col_2_bigint BIGINT, col_2_decimal DECIMAL(10, 2), col_2_numeric NUMERIC(10, 2), col_2_real REAL, col_2_double DOUBLE PRECISION, col_2_smallserial SMALLSERIAL, col_2_serial_alias SERIAL, col_2_bigserial BIGSERIAL, col_2_money MONEY, col_2_char CHAR(5), col_2_varchar VARCHAR(50), col_2_text TEXT, col_2_bytea BYTEA, col_2_timestamp TIMESTAMP, col_2_timestamptz TIMESTAMPTZ, col_2_date DATE, col_2_time TIME, col_2_timetz TIMETZ, col_2_boolean BOOLEAN, col_2_uuid UUID, col_2_json JSON, col_2_jsonb JSONB, col_2_xml XML, col_2_inet INET, col_2_cidr CIDR, col_2_macaddr MACADDR, col_2_bit BIT(8), col_2_varbit VARBIT(16), col_2_interval INTERVAL, col_2_point POINT, col_2_line LINE, col_2_lseg LSEG, col_2_box BOX, col_2_path PATH, col_2_polygon POLYGON, col_2_circle CIRCLE, col_2_tsquery TSQUERY, col_2_tsvector TSVECTOR ); I used "data flow". Upsert source: Upsert derived column - as we don't have "updated at" timestamp column in source / target, I plan to use md5 of all present row values to compare changes Upsert Alter Row - Upsert if: isNull(md5_columns)==false() Upsert Sink: Debugging: Could someone kindly look into it to advise what could be wrong? The last screenshot seems intended to clarify, but it doesn't really help to see the root cause.PostgreSQL - Flexible Compute - VNet DNS Update
Hello, I have a customer planning to update the VNet DNS from custom to Default. The client is running a PostgreSQL - Flexible server with VNet integration. Does the DNS change impact PostgreSQL - Flexible compute? What is the best way to update the PostgreSQL - Flexible compute without having to restart? Thanks, Terru
AZURE SQL DB Deadlock
I see more than 2000 deadlock everyday for my Azure SQL DB in deadlock metrics but at the end it is not causing any missing data or any transaction drop. My application not using any retry logic then how it is possible that deadlock is getting automatically resolved has no impact at all ?
Clarification on Staging Directory Usage for SAP CDC Connector in Azure Data Factory
Hi! I'm currently working on a project where we are ingesting data from SAP using the SAP CDC connector in Azure Data Factory(Data flow). The source is S4HAHA CDS views. We are using a staging directory for the data flow with a checkpoint mechanism, similar to described here: https://learn.microsoft.com/en-us/azure/data-factory/connector-sap-change-data-capture My question is: Does the staging directory only act as a temporary storage location during ingestion from sap? If i understand correctly its used for retries, but no real usage once the deltas have been ingested. After the data has been loaded to the destination(in our case container inside of ADLS), is the data needed for maintaining delta states? Can the data be safely deleted(from the staging container) without impacting the subsequent load runs? We were thinking of implementing a 7 day retention policy on the staging container so we can manage storage efficiently. Thank you in advance for any information regarding this.Executing Oracle Stored Procedure in Azure Data Factory
Hello There, I want to execute(call) an Oracle Stored Procedure from Azure Data Factory (ADF). The Stored Procedure resides in an On-prem oracle server and I was able to establish the connectivity via the Self Hosted Integration Run Time. The oracle stored procedure has 1 input parameter (user defined datatype of object) and 1 output parameter (Varchar2 datatype). Can you please navigate me in terms of which activity to use in ADF to call the Oracle Stored Procedure and show how it is done in terms of variable declarations and the call command. A walk through example with screenshots would be really helpful. Also, I need to capture the Oracle Stored Procedure Output parameter value in ADF so that I can use that to control the pipeline flow in the subsequent steps. Thank You
Azure Synapse: What’s Next?
With the recent introduction of Microsoft Fabric, which aims to unify various data and analytics workloads into a single platform, how will this impact the future of Azure Synapse Analytics? Specifically, will Azure Synapse Analytics become obsolete, or will it continue to play a significant role alongside Microsoft Fabric? Additionally, what are the recommended migration paths and considerations for organizations heavily invested in Azure Synapse Analytics?”
What is the way to use OUTPUT parameter for an Oracle Stored procedure in ADF pipelines?
I have a oracle database package and i am trying to call a stored procedure inside that package. The procedure has a OUT parameter which we want to use in the activities further in ADF pipelines. But ADF pipelines does not have a way to get the OUT parameter values and use it in pipeline. This is a very important feature.
Using "Expect Unique" assert type on ADF flow with additional streams
Hello, I'm trying to use ADF functionality assert with an additional stream but it seems it doesn't work. It recognizes the column name and I can select it but when I try a data preview, I get a "Resolved attribute(s) [...] missing from (list of attributes coming from main source) in operator !Project (list of attributes from main source + that attribute)" The attribute does exist in my source, I can see it in the data preview, and use it in lookup. I tried other attributes with same result. The documentation is not very clear on whether or not this scenario is possible. I'm guessing not but would like confirmation. Thank you.
Writing data to a lookup field via alternative keys
Hi Folks, I have 2 table, 1. Contact with a secondary key 2. Policyholder with a lookup field to contact and a column that contain the contact secondary key guid as a string I want to take the column named : contactSecondaryKeyString in policyholder which contain the secondary key string(guid) and push it to my lookup field policyholder in the table policy holder. As per microsoft it is possible :Copy and transform data in Dynamics 365 (Microsoft Dataverse) or Dynamics CRM - Azure Data Factory & Azure Synapse | Microsoft Learn Here is my mapping code : and I get the error : "ErrorCode=DynamicsOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=,Source=,''Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Dynamics operation failed with error code: -2147217149, error message: 'prsc_phplan' entity doesn't contain attribute with Name = 'prsc_policyholder@prsc_systemid' and NameMapping = 'Logical' I tried many variation and still can't figure it out...What Synapse Serverless SQL pool authentication type for ADF Linked Service?
Hi, I'm relatively new to Azure Data Factory and require your guidance on how to successfully create/test a Linked Service to the Azure Synapse Analytics Serverless SQL pool. In the past, I've successfully created a Linked Service to a third-party (outside our domain) on-premises SQL Server through creating a self-hosted integration runtime on their box and then creating a Linked Service to use that. The Server Name, Database Name, Windows authentication, my username and password all configured by the third-party is what I entered into the Linked Service configuration boxes. All successfully tested. This third-party data was extracted and imported, via ADF Pipelines, into an Azure SQL Server database within our domain. Now I need to extract data from our own (hosted in our domain) Azure Synapse Analytics Serverless SQL pool database. My attempt is this, and it fails: 1) I create a 'Azure Synapse Analytics' Data Store Linked Service. 2) I select the 'AutoResolveIntegrationRuntime' as the runtime to use - I'm thinking this is correct as the Synapse source is within our domain (we're fully MS cloud based). 3) I select 'Enter manually' under the 'Account selection method'. 4) I've got the Azure Synapse Analytics Serverless SQL endpoint - which I place into the 'Fully qualified domain name' field. 5) I entered the data SQL Database name found under the 'SQL database' node/section present on the Data >> Workspace screen in Synapse. 6) I choose 'System-assigned managed identity' as the Authentication type - this is a guess and I was hoping it would recognised my username/account that I am building the Linked Service with, as that account also can query Synapse too and so has Synapse access. 7) I check the 'Trust server certification' box. All else is default. When I click test connection, it fails with the following message: "Cannot connect to SQL Database. Please contact SQL server team for further support. Server: 'xxxxxxxxxxxx-ondemand.sql.azuresynapse.net', Database: 'Synapse_Dynamics_data', User: ''. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access. Login failed for user '<token-identified principal>'." I've reached out to our I.T. (who are novices with Synapse, ADF, etc.. even though they did install them in our domain) and they don't know how to help me. I'm hoping you can help. 1) Is choosing the 'Azure Synapse Analytics' the correct Data Store to chose when looking extract data from an Azure Synapse Serverless SQL pool SQL database? 2) Is using the AutoResolveIntegrationRuntime correct if Synapse is held within our domain? I've previously confirmed this runtime works (and still does) as when importing the third-party data I had to use that runtime to load the data to our Azure SQL Server database. 3) Have I populated the correct values for the 'Fully qualified domain name' and 'Database name' fields by entering the Azure Synapse Analytics Serverless SQL endpoint and subsequent SQL Database name, respectively? 4) Is choosing 'System-assigned managed identity' as the Authentication type correct? I'm guessing this could be the issue. I selected this as when loading the mentioned third-party data into the Azure SQL Server database, within our domain, this was the authentication type that was used (and works) and so I'm assuming it somehow recognises the user logged in and, through the magic of cloud authentication, says this user has the correct privileges (as I should have the correct privileges so say I.T.) so allow the Linked Service to work. Any guidance you can provide me will be much appreciated. Thanks.Extending by a function output
datatable(ids: dynamic) [ dynamic(["value1", "value2"]) ] | function(ids) This above snippet works fine and returns a table datatable(date: datetime, ids: dynamic) [ datetime(2022-01-01), dynamic(["value1", "value2"]), datetime(2022-01-02), dynamic(["value3", "value4"]) ] | extend outputs = function(ids) This one however complains that extend expects a scalar and not table that the function returns datatable(date: datetime, ids: dynamic) [ datetime(2022-01-01), dynamic(["value1", "value2"]), datetime(2022-01-02), dynamic(["value3", "value4"]) ] | extend outputs = toscalar(function(ids)) When using toscalar, ids cannot be referenced. Is there a workaround? The function take in dynamic and returns a tubular expression of one row and two columnsAzure SQL programmatic access
I work with a team of statisticians who would like to programmatically access an Azure SQL database via R/R Studio without human interaction. Access is setup currently with Microsoft Entra with MFA. The team ideally want to run their R script and connect to the database, without having to be there to enter their login credentials and MFA, can this be done via a key or SAS token? What is the best or recommended method to do this? Thank you. ZImporting bacpac to Azure database fails
I am trying to import a database from a bacpac file to our azure sql server. I am following these steps: Creating a bacpac file from our production database running on an MS SQL Server 2022. Importing it into azure using sqlpackage But, it keeps failing with *** An unexpected failure occurred: Data plan execution failed with message One or more errors occurred. (One or more errors occurred. (One or more errors occurred. (Violation of PRIMARY KEY constraint 'PK_ForexQuote'. Cannot insert duplicate key in object 'dbo.ForexQuote'. The duplicate key value is (1). The statement has been terminated.))). The ForexQuote table DOES NOT have any duplicate primary keys. If I omit this table in my bacpac export I get a similar error on another table. I dont understand. What is going on here?Send files from Blob Storage to Vendor API
Hello, In an Azure Blob container in our tenant we have several thousand .json files that need to be ingest by a vendor search api. The vendor example is to send as PUT /v2/indices/{index public key}/documents/{document id} to their api. My background with ADF is copyfiles from a local fileshare to blob. I just copy with source and sink - works Never having done this before and using Copilot it suggested creating a pipeline, using the Get Metadata activity. I did that. The setting of Get Metadata point to a Dataset. (see attachments for images - not the blob setting show successful connection and preview) at this point I just tried to debug it and got this message: Error code 2001 Failure typeUser configuration issue Details The length of execution output is over limit (around 4MB currently) Activity ID b74f3802-804f-4b04-91c2-3f68546a40a5 Each files is about 20KB, but I suspect it is trying to get all the files as one. If this is the case, how do I get it to iterate one by one? Copilot said to use a Filter activity, but that is AFTER the Get Metadata statement. Any help on how to proceed OR troubleshoot this better? Thanks, VAzure Sql Database Linked Service Lookup Activity Error
Started receiving this error when previewing results in both lookup and copy activities that are connected to an Azure SQL Database. My production pipeline (that is triggered) fails on the Azure Database lookup activity. Does anyone hav any insights on what is causing this issue? And/or how to resolve? Thanks!
ServiceNow Connection - data request URL too long for pagination
Hi, So we've encountered an issue after setting up a connection between data-factory and ServiceNow. Our team has been trying to query a really big table (alm_asset) from our ServiceNow instance - and when we try to add Pagination to be anyhting but empty, for some reason DataFactory lists all of the columns to be queried. Now that column query list we couldn't find, and our REST request could not be executed because of the too long url, so pagination could not fit. The API request to ServiceNow failed. Request Url : -- removed -- , Status Code: BadRequest, Error message: {"error":{"message":"Pagination not supported","detail":"The requested query is too long to build the response pagination header URLs. Please do one of the following: shorten the sysparm_query, or query without pagination by setting the parameter 'sysparm_suppress_pagination_header' to true, or set 'sysparm_limit' with a value larger then 4182 to bypass the need for pagination."},"status":"failure"} This 4182 is just a on a sub-production instance, on produciton instance we have significantly more data. Can somebody help how to edit the params sent for the REST API through that connector?
Recent Blogs
- We are pleased to announce that you can now purchase reservations for General Purpose SQL Database configured with Zone Redundancy. SQL DB General Purpose service tier offers two redundancy option...Dec 19, 2024
