Handling Row-level Errors in ADF Data Flows (2021-06-16)

SQLServerCentral Newsletter for June 16, 2021 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Handling Row-level Errors in ADF Data Flows]( - [Managing SQL Snippet Collections using JSON and PowerShell]( - [SQL Server performance tuning: Nine best practices]( - [From the SQL Server Central Blogs - Moving Synapse Databases Between Subscriptions – Practical Guidance]( - [From the SQL Server Central Blogs - How to create a DataKeeper Replicated Volume that has Multiple Targets via CLI]( Question of the Day - [A Mixed Matrix in R]( The Voice of the DBA  Daily Coping Tip Look for something to be thankful for where you least expect it I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate. For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours. Tracking, Privacy, and Lots of Data Half of all advertising dollars are wasted. We just don't know which half. That's [a common view from people purchasing advertising](, and it's why I expect that this is what has led Google, Facebook, and other large firms to track people across different sites and apps. There are frameworks that make it easy to build in some metrics and derive advertising revenue from their software. Apple has been fighting back a bit. I don't know Apple is terribly altruistic here, but I do think [their changes to implement some privacy controls]( for users are good. There's an article that talks about some of their recent changes in iOS and the complaints from vendors. There are also some notes about the The [Apple white paper]( is a bit disturbing in its story, showing how a few common daily actions result in data being tracked. It certainly is something I know happens as I'll see ads for Redgate products on a volleyball site or ads for the bike in which I was interested on a technical database article. It's annoying and frustrating. I get the idea of making impressions to influence my, but it's also distracting when I'm doing something unrelated. Maybe more annoying is that once I buy something, I'm not longer interested, but I still see the ads. I don't mind a company tracking me on their site and showing me something that is possibly related to what I am doing. I do mind having apps to this across apps, and companies aggregating and selling this data. I know this has happened for years, but the scale of today is more problematic. For me, this truly is an issue that needs to be addressed with more control and respect for the human rather than the organization. I loved my iOS devices for years, until they removed the headphone jack. I switched the Android, and I've been happy, but now I'm in the same situation. I like the choice I get, but that freedom comes with a price in this space. I'm not sure I see Google, or really most manufacturers using Android, making a good choice here for humans. I'm not sure Apple is a lot better, but this is a little better. I'd like to see more platforms open, but with also some accountability and responsibility from vendors that doesn't just make the customer the product. Instead, let the customer choose what data to allow, and then you can adjust prices accordingly. If someone wants to give up all their information for less cost, fine. However, let me also choose privacy if the value is there for me. I'll happily for for it in many places. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Handling Row-level Errors in ADF Data Flows]( diponkar.paul from SQLServerCentral If you are working with ADF (Azure Data Factory) data flows, then you may have noticed there was a new feature released in November 2020, which is useful to capture any error while inserting/updating the records in a SQL database. This article will describe how to setup the error row handling feature and why it's […] [Managing SQL Snippet Collections using JSON and PowerShell]( Additional Articles from Redgate Maintain your favorite SQL snippets and queries centrally, using PowerShell to save and update each snippet collection, in JSON, and then converting them into SQL code snippets for SQL Prompt. [SQL Server performance tuning: Nine best practices]( Additional Articles from SimpleTalk SQL Server performance tuning can seem overwhelming when you don’t know where to start. In this article Robert Sheldon explains 9 best practices for performance tuning. From the SQL Server Central Blogs - [Moving Synapse Databases Between Subscriptions – Practical Guidance]( DataOnWheels from DataOnWheels One of the tasks, we often do with migration projects is move large volumes of data. Depending on how you are configured, you may need to do the migration... From the SQL Server Central Blogs - [How to create a DataKeeper Replicated Volume that has Multiple Targets via CLI]( david.bermingham from Clustering for Mere Mortals I often help people automate the configuration of their infrastructure so they can build 3-node clusters that span Availability Zones and Regions. The CLI for creating a DataKeeper Job...   Question of the Day Today's question (by Steve Jones - SSC Editor):  A Mixed Matrix in R I want to create a matrix in R that looks like this: [,1] [,2] [,3] [1,] "1" "A" "!" [2,] "2" "B" "@" [3,] "3" "C" "#" How do I do this? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) Checking the Date I run this query: DECLARE @d DATETIME2 = SYSDATETIME(); SELECT @d; This the result: 2021-06-12 12:13:54.0854894 Now I run this: SELECT ISDATE('2021-06-12 12:13:54.0854894') What is returned? Answer: 0 Explanation: This returns a 0. The ISDATE() function only checks for date, time, or datetime values. Otherwise, this returns a 0. Ref: ISDATE() - [Discuss this question and answer on the forums](    Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- SQL Server 2017 - Administration [Index combining question]( - I ran a report and execution plan suggested a 'missing index' with 4 index key columns and 4 included columns. I created the index and ran the report again it gave me 'missing index' suggestion again with now 2 key columns and 2 included columns which were part of the missing index of the first […] SQL Server 2016 - Administration [Reading multiple audit files]( - Is it possible to read multiple audit files from multiple servers all placed under a single folder ? SELECT * FROM sys.fn_get_audit_file('E:\ABC\ServerAudit\*.sqlaudit', DEFAULT, DEFAULT) order by event_time desc I have 10 audit files from 10 different servers all under this folder - E:\ABC\ServerAudit\ Thanks [replicate deleted records]( - hi guys, does any one know how to replicate "only deleted records" using transactional replication ? in other words deleted record needs to be moved to other database using transactional replication [We added 4 files to tempdb, due to Pagelatch_up being major wait]( - Yet they do not appear to be used, and lots of Pagelatch_up: tempdb waits are still showing. all 4 new files show 98% of free/available space, while the original 8 files are being used, at 50 and more percent, showing 20 or 30 % only free in them. Why are the new files not being […] SQL Server 2016 - Development and T-SQL [How to mark consequitive groups with non-unique group key]( - I have a sequence of records with ID and FLAG being a key to identify the group of records, but the FLAG here is a bit, so it is not unique declare @T table ( ID int , TS date , FLAG bit ); insert into @T(ID, TS, FLAG) values (1, '2020-01-01', 0 ) , […] Development - SQL Server 2014 [Get double records returned from SELECT because I failed to save the PK.]( - I have two tables. A lookup table that has a PK and various columns. Looks something like this: tblOperator PK OperatorID, OperatorName, BadgeID  My main table saves the BadgeID value from the tblOperator instead of the PK OperatorID. So when I do an INNER JOIN between tblMain and tblOperator, I get duplicate records returned. […] [Trying to understand percent sign next to field name]( - I have a simple query where I needed to see if a qty had decimal places. I was instructed to use field_name%1 as shown below. It works. I'm just trying to understand the process so I can learn more. What is the %1 doing/calculating? SELECT m.item_id , l.qty_on_hand FROM inv_loc l INNER JOIN inv_mast m […] SQL Server 2019 - Administration [hardening of SA account causes failed login found in error logs]( - Hi Guys, i am hardening the SA but disabling it and renaming it according to CIS hardening guide. But found multiple failed login errors in Error logs. Did a further trace using the sql profiler and discover: Login failed for user 'sa'. Reason:.... Always On Operations Dashboard i could not found any supporting technote from […] [Updating Python packages in Machine Learning Services ( MLS ) on a MI]( - Hi When MLS is set up on an Azure SQL Server Managed Instance ( compatibility level 150 ) , there are are whole batch of python packages that are installed. I know I can add or remove packages via ADS , but these appear to be packages not part of the base install. Is it […] [Always Encrypted and how to use them in queries.]( - Hello Everyone I've been testing Always encrypted on 2 test machines. I'm trying to get a feel on how easy they are to work with. My objective is to encrypt personal info of individual's but still be able to query that info. On server 1, I created Master Encryption Key, Column Encryption Key & Encryption […] SQL Server 2019 - Development [SQL agent failed job report last 24 hours]( - Hi All, Posting it on development group, if anyone can provide query. Basically, I am seeing 100+ failed jobs daily and it is taking more time to check in agent history and 80% of auto success in 20% are failed. Is there any way can only get report of failed non success jobs. -- Failed […] [MAXRECURSION 0 Option]( - Hi Everyone, I am trying to apply a Multi table function that uses a recursive CTE. When I apply it I get the error: The statement terminated. The maximum recursion 100 has been exhausted before statement completion. I've looked online about this error and apparently I need to add: Option ( MAXRECURSION 0 ) Apparently […] [sql server keeps looking for my User Defined Scalar Function in the Master DB]( - I have a Visual Studio project web site that I have been using for years. The move to SQL server 2019, has apparently broken it. When I call the dataadapter.Fill function it tries to find my User Defined Scalar Function in the Master Database. System.Data.SqlClient.SqlException: 'Cannot find either column "master" or the user-defined function or […] [outer apply alternative]( - My goal is to get the results below. Trying to get the nearest date and result from the #Data table where the resultdate<=ProcDate. An outer apply is taking 4 hours for a 400 row table. Another more efficient way of doing this? I have several more '#Data' tables (CTE's) to join to the #patient table […] Amazon AWS and other cloud vendors [Cost effective VPN solution for AWS with MFA]( - Hi, I've recently setup VPN on our AWS account and had my AWS bill go up by $200. It appears that you pay for it even when nobody uses it. You can't stop it like EC2 instance. I found this article: It says you can automate the removal of VPN configuration and re-creation it […]   [RSS Feed]([Twitter]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -