Why Power Query as an Activity in Azure Data Factory and SSIS? (2021-07-26)

SQLServerCentral Newsletter for July 26, 2021 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Why Power Query as an Activity in Azure Data Factory and SSIS?]( - [Much Ado about nothing: missing data]( - [How to Compare Two SQL Prompt Styles]( - [From the SQL Server Central Blogs - Filtering Extended Events Using Actions]( - [From the SQL Server Central Blogs - Is 18TB of Storage Enough?]( Question of the Day - [Azure Blob Store Redundancy]( The Voice of the DBA  Daily Coping Tip Remember a tough time during the pandemic and how you got through 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. Do You Have ALL the YAML? YAML is a file format, and as with many formats, some of us love them and some of us hate them. It seems to be better than XML in many ways, and perhaps easier to deal with the JSON. It might not be better than csv/tsv/delimited formats for large transfers but for many of us, it's a nice format for configuration items. While the format felt fairly intuitive to me, and it's not hard to write, it is quite persnickety about whitespace, which makes some plugin, like the [Red Hat YAML extension]( for VS Code one, important to helping you prevent mistakes. Even as easy as the format can be to read and understand, it's also easy to make mistakes with the whitespace as you indent and try to add subkeys. I was watching [an AWS talk]( and there was an interesting note about using YAML for control planes and being sure that you have some sort of checksum if you do. Why? Because you aren't sure if you have the entire file. A YAML file could be truncated in any file transfer, and it would still appear to be valid. Hearing that made me realize that those annoying closure tags in XML and JSON might have some value. Those of you that work with YAML, how are you sure you got the entire file? Is there something you'd program in? Do you checksum the file and pass that along? Do you include a required, closing key:value of some sort? I don't, but I might think about doing so in any place where an invalid or incomplete file might cause me problems. This certainly seems like something you'd want in a control file, like one used for Kubernetes. In most cases, we assume if we can read a file, then we have the complete file. I don't know of many customers that require some sort of checksum or validation for a file. Certainly a CSV or TSV missing rows might appear valid in an import process. XML and JSON should have a closing tag or character, so we'd hope we could catch this, but maybe not. Moving around data through files, especially data used to drive processes, should include some error handling. That would mean that we have some way to detect if part of our file is missing. There are ways, but it seems that in many cases we've gotten lazy about implementing them. Certainly I don't see people checksuming YAML files, which seems like something that we'd want to require. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Why Power Query as an Activity in Azure Data Factory and SSIS?]( diponkar.paul from SQLServerCentral There is a Power Query activity in SSIS and Azure Data Factory, which can be more useful than other tasks in some situations. [Much Ado about nothing: missing data]( Additional Articles from SimpleTalk Joe Celko explains how missing data is handled from the printing press to databases. [How to Compare Two SQL Prompt Styles]( Additional Articles from Redgate A PowerShell function that will compare two SQL formatting styles, saved in JSON, and produce a report showing the differences between the options they use to lay out your SQL code. From the SQL Server Central Blogs - [Filtering Extended Events Using Actions]( Grant Fritchey from The Scary DBA Did you know, you can use Actions to Filter Extended Events? Well, you can. Filtering is one of the greatest ways in which Extended Events differentiates itself from other... From the SQL Server Central Blogs - [Is 18TB of Storage Enough?]( John Morehouse from John Morehouse | Sqlrus.com Come to find out, nope, probably not. My good friend Jim Donahoe (blog|twitter)was very nice to tell me that he’s running 100TB of storage in his house across multiple Synology...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Azure Blob Store Redundancy Which type of redundancy offers this: data is replicated synchronously across three Azure availability zones in the primary region. Each availability zone is a separate physical location. 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) Dataset Prereq in Azure Data Factory I want to create a dataset for my Azure Data Factory v2 Pipeline. What do I need to do before I create the dataset? Answer: Create a linked service for the data store Explanation: Before you create a dataset, you need a linked service. Ref: Linked Services - [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 - Development [Having a hard time with time zones]( - I found a glitch in one of my reports. Its due to events spanning the hour difference when a time zone moves to daylight saving So 31st March 2019 was a Sunday, and being the last Sunday in March - we advance at 01:00 to 02:00 - so the time 01:30 cannot exist on 31st […] [Powershell script help sending the results in email body]( - Hi, i found some script to list out the primary ag groups in servers in powerhsell. but the results not coming in attachment. i need results through email email body. can any one help please find below script ## Setup dataset to hold results $dataset = New-Object System.Data.DataSet ## populate variable with collection of SQL […] [Storing the datediff between a row and the previous row]( - So I want to store the difference in milliseconds between rows in a column Table format ID , Label, Realdate, datediff . I've tried calculating the datediff using the following statement but if I try and combine with an update to the timediff column Im getting no joy  SELECT id,label,realdate, DATEDIFF(MILLISECOND, pDataDate, realdate) as […] SQL Server 2016 - Development and T-SQL [Best Index Choices]( - Can you guys give a recommendation on what the best indexes would be for this query? Please let me know if more info is needed. SELECT IMLSMST_SQL.ser_lot_no AS 'Ser/Lot' ,CASE WHEN IMLSMST_SQL.qty_on_hand - IMLSMST_SQL.qty_allocated - Isnull(Sum(wsPKGSL.Qty), 0) > 0 THEN IMLSMST_SQL.qty_on_hand - IMLSMST_SQL.qty_allocated - Isnull(Sum(wsPKGSL.Qty), 0) ELSE 0 END AS 'Qty Avail' FROM IMLSMST_SQL LEFT […] Development - SQL Server 2014 [Hash join in query even after having clustered index]( - Hi, I have 2 tables which join on ID column. select name from Product P inner join LineItem L on P.ID = L.ID Product table has clustered index on ID while LineItem has non clustered index on ID. Initially, both the ID columns were nvarchar and query was doing an index scan and running pretty […] [updating data and keeping a history of the changes]( - Good Morning, I am looking for some advice on some work i have been given. currently we have a small database which is based on a zip file of 5 CSV files. The CSV files get updated each week and the files then need to be uploaded into the database each week. I am ok […] SQL Server 2019 - Administration [View running indefinitely after rebuilding indexes]( - Hi, I have a Combined View which is fetching data from 2 underline views which in turn are referring to 2 tables. I did an index rebuild on the underlined tables due to fragmentation ,post which views on top of tables are working fine but the combined view on top of those views is running […] [Error when trying to restore previously encrypted database]( - Some background... we have a test environment where we are testing TDE. We log ship databases from one server (let's call it Primary) to another server (Secondary) for reporting purposes. Recently we had a power outage which caused, among other things, for log shipping to fail on many of these databases. When attempting to recreate […] [Want to Know SQL Server 2019 Reporting Services]( - what services is SQL Server 2019 provided at an advanced level? please help. SQL Server 2019 - Development [String_Agg and Group By Error]( - Hello All, I am in need of your assistance. I am trying to use the string_agg function with a group by and keep on getting the error below. Column 'Integration_02_Clinical.Schedule.Source System' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Could someone provide […] [Wrapping your SQL Server Database into a complete web app]( - Hi guys and gals, I just got laid off some few days ago, and in order to sustain a living, I had to become a bit "creative". Hence, I just started a service provider where I will create a complete Angular frontend application and a complete .Net5 based web API (CRUD), wrapping any existing database […] SQL Server 2008 - General [Cross database user permissions]( - I have two databases: Db1 and Db2. For security (and other) reasons I don't allow the database users direct access to the tables or views so all SELECTs, INSERTs, etc are inside stored procedures. All works fine except when a stored procedure in Db1 needs to access data in DB2. I have to grant the […] Reporting Services [Apply patch on the reporting server]( - I installed a 2016 standard edition of SQL Server instance along with reporting services. Now I am trying to apply CU15 on the instance but the option to select the instance isn't highlighted. I have never run into this type of issue before. For DB engine, we always use enterprise edition and I never have […] Powershell [Move files and add datetime stamp to filename]( - Trying to move files and add the datetime stamp before the extension. I only want to copy files with a .csv extension, no other directories or sub folders. This runs but doesn't move anything. I would like to also move files where files have data, no zero byte files get moved. Thanks. Get-ChildItem -Path 'C:\Line_Dumps\' […] The Future of Community [Loved it]( - Hi everyone! I just want to say that I really love the idea of the section daily coping. Do you agree with me? Please accept my applause to whoever created it! However, it seems to be a couple of days behind so plz try to keep it on. Thanks for reading.   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -