Extending Custom Execution in the SSIS Catalog – Level 21 of the Stairway to Integration Services (2021-04-21)

 SQLServerCentral Newsletter for April 21, 2021 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Extending Custom Execution in the SSIS Catalog – Level 21 of the Stairway to Integration Services]( - [Common Data Transformations in Power Query]( - [Batch Processing using Flyway]( - [From the SQL Server Central Blogs - Book Review: High Performance SQL Server (Second Edition)]( - [From the SQL Server Central Blogs - The PowerShell Basics If Statement–#SQLNewBlogger]( Question of the Day - [An Empty Integer Numpy Array]( The Voice of the DBA  Daily Coping Tip Get active by singing today (even if you can’t) 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. Changing Edge Security Often organizations have thought about setting security at their borders or boundaries, often thinking of firewalls as their primary security tool. While firewalls do help (quite a bit), they aren't the only tools that we need to ensure security. Those of us working with databases have known that things like SQL Injection mean that we need not only to be concerned about the client connection, but the data that is being moved as well. One thing that has started to happen more is data being copied out of a single large database and into various other systems. These could be systems like warehouses or cubes, but data is also moving into other types of data stores, such as Power BI, which might be considered a new type of "edge" system. Certainly there are edge databases and replicated copies of things, but this means that our security needs to account for these new paradigms. There was [an article]( recently that talks about some of the challenges with the new world where we have lots of new devices connecting to our systems, and likely new applications that may move data around. I suspect that things like zero trust and new security by default practices will take some time to filter to many companies, but they will be important in the future. Criminals and hackers are getting better at quickly exploiting holes, so all organizations will feel some pressure to increase security in the next few years. While encryption of our data is going to be important, the management of keys to access the data becomes the sticking point. I have yet to see any really good systems that don't require a lot of work, but there are good solutions in a connected world to ensure clients are validated before they are allowed to access data. I'm hoping that we also start to see new ways for software developers to ensure data isn't being stored in local caches or files without some protection. We need to find ways to ensure that software developers adopt zero trust methodologies when they access and manipulate data in their applications. I also hope that more companies start to hold vendors accountable for strong security in their applications. With more and more access points to our networked systems, we need to ensure that all our software takes security seriously. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Stairway to Integration Services]( [Extending Custom Execution in the SSIS Catalog – Level 21 of the Stairway to Integration Services]( Andy Leonard from SQLServerCentral.com In this installment of the Stairway to Integration Services, Andy Leonard shows you how to execute packages synchronously or asynchronously. [Common Data Transformations in Power Query]( Additional Articles from MSSQLTips.com In this article we walk through an example of using Power Query to transform source data that can be used in a Power BI report. [Batch Processing using Flyway]( Additional Articles from Redgate How to create a batch file that executes any number of database migration tasks across a range of servers and databases, using Flyway. From the SQL Server Central Blogs - [Book Review: High Performance SQL Server (Second Edition)]( Tsql101 from Marlon Ribunal - SQL, Code, Coffee, etc. One of the technical books that sit on my work desk (or dining table, whichever is the case because I work from home full time now) at arm’s length... From the SQL Server Central Blogs - [The PowerShell Basics If Statement–#SQLNewBlogger]( Steve Jones - SSC Editor from The Voice of the DBA Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. This is a fairly simple construct, but I...   Question of the Day Today's question (by Steve Jones - SSC Editor):  An Empty Integer Numpy Array I am creating an empty numpy array, but I want the values stored in here to be integers. What do I add to this code: import numpy a = 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) Running SSIS in ADF I want to execute an existing SSIS package using Azure Data Factory (ADF). I have uploaded my SSIS package into Azure and have a new pipeline. What activity to I add to the pipeline? Answer: Add the Execute SSIS Package activity Explanation: There is an Execute SSIS Package activity you can use. Ref: - Migrating SSIS packages to Azure - Lift and Shift using Azure Data Factory - - Run an SSIS package with the Execute SSIS Package activity in Azure Data Factory - [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 [Availability Group]( - Trying to understand the pros and cons. So I have SQL Server default instances and I am planning to configure as AOAG. However, the best practice I have heard is add the nodes to cluster first and then install SQL Server and configure AOAG? But I have situation where all the nodes are not available […] SQL Server 2017 - Development [SQLCMD.exe get installed with SSMS?]( - Is the SQLCMD.exe supposed to be installed when SSMS is installed on a workstation? I've been told it is, but I cannot seem to locate it after SSMS was installed. I've looked where I typically find this exe , run a DIR search. I do not find this exe.  SQL Server 2016 - Administration [fn_dump_dblog]( - Hi All I have been trying to figure out what has caused a spike in the size of a particular log backup. ' I am seeing entries for LOP_MODIFY_COLUMNS & LOP_MODIFY_ROW I found this in attempt to get an understanding... LOP_MODIFY_COLUMNS: Designates that a row was modified as the result of an Update command. LOP_MODIFY_ROW: […] SQL Server 2016 - Development and T-SQL [Log Updates To Table]( - I've a user table that has four fields that need to be manually verified (as a daily batch) by an administrator any time they're changed. The four fields may be changed over the course of a couple of different web pages. The table does have a 'modified' column that records the date the last change […] [Transaction log and updates on large tables]( - I’ve inherited a query that on first glance should be re-written. The main part of which is performing an update on a table (200m+ rows) in a while and using nested selects in a not exists. I realise this is quite vague so can provide more detail. The query has a tendency to fill my […] [Complex .Json Data Parsing with multiples Array elements into SQL (MS-SQL 2016)]( - DECLARE @JSON NVARCHAR(MAX) =N'{ "code": 200, "msg": "success", "data": [{ "ITEM": "SP", "FACTORY_NO": [{ "FACTORY_NO": "1", "ORDERQTY": 10 }, { "FACTORY_NO": "1", "ORDERQTY": 20 } ], "DETAILS": [{ "LINE_INFO": "INLNE", "CARD_NUMBER": 13 }, { "LINE_INFO": "OUTLINE", "CARD_NUMBER": 14 } ] } ] }' Expected OUTPUT IN MS-SQL 2016 ITEM FACTORY_NO ORDERQTY LINE_INFO CARD_NUMBER SP 1 […] [SSRS Report doesn't refresh data]( - Hi all, I have SSRS Report that uses SP as a source. I have one filter - Activation Date and use such script for it: select distinct [Activation Date] from TableTest. Every month data in the TableTest updates. But in the SSRS filter I didn't see updated value. When I execute a query in the […] [How to change schema of all tables, views and stored procedures in MSSQL]( - Hello Community, I trying to use the following sql code to change the Schema for all my tables from dbo to Config SELECT 'ALTER SCHEMA Config TRANSFER [' + SysSchemas.Name + '].[' + DbObjects.Name + '];' FROM sys.Objects DbObjects INNER JOIN sys.Schemas SysSchemas ON DbObjects.schema_id = SysSchemas.schema_id WHERE SysSchemas.Name = 'dbo' AND (DbObjects.Type IN ('U', […] SQL 2012 - General [How to Display FeatureName of Comptitor First then Npx Feature Name second Based]( - How to Display Feature Name of Comptitor First then NXP Feature Name second Based On Display Order? I work on SQL SERVER 2012 I face issue I can't arrange feature on same display order to start by comptitor feature name then nxp no issue on display order 1 and 2 because it is correct issue […] SQL Server 2019 - Development [Debugger permission issue]( - We have a dev that used to use SSMS in order to debug code that was related to SQL. I guess MS has taken that piece out of SSMS and now they are supposed to do this with Visual Studio. Problem is, now we can't seem to get that to work right. Each time they […] Reporting Services [SSRS inserting blank page]( - I have a main report calling three subreports. The subreports are being grouped together by the CourseID field. Page 1 shows course 1 for the first two reports. The third subpreport is for comments. If course 1 has comments, they will show up on page 2. If there were no comments, page 2 will be […] Analysis Services [Changing SSAS TCP port directly in config file]( - Goodday, I have a problem with changing the TCP port for SQL Server Analysis Services by using the "msmdredir.ini" file I know how to change it by using Microsoft SQL Server Management Studio, but I want to change it directly in the "C:\Program Files (x86)\Microsoft SQL Server\90\Shared\ASConfig\msmdredir.ini" file, so that I can automate the installation/configuration. […] Integration Services [SSIS Package to load contents of CSVs to SQL Table]( - I have 14 .csv files from which I need to extract 1) a date found only in the file name itself e.g. "ConvoEVQ2021-03-22" (i.e. I need to harvest the date part of the file name and place it in all the date fields of each SQL record associated with that .csv), 2) an individual's full […] [Last day of the previous month - Format MM/DD/YYYY]( - I use the below to get Last day of Previous Month, but I want the date format to be MM/DD/YYYY. We are sending that in the output file. REPLACE(LEFT((DT_WSTR,20)DATEADD("d",-1,DATEADD("m",DATEDIFF("m",(DT_DATE)"1901-01-01",GETDATE()),(DT_DATE)"1901-01-01")),10),"-","/")  Thanks! Job Postings [Data Warehouse and Analytics Lead]( - Hello Community! Located in beautiful Monterey, CA, the Dept of Defense's Defense Language Institute Foreign Language Center is looking to build our first Data Warehouse and grow our Data Analytics Program. We need a technical lead! It'll be small at first, leveraging our current Microsoft SQL Server technologies and our Microsoft Office 365 and Power […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -