Migrating SSIS packages to Azure - Lift and Shift using Azure Data Factory (2021-03-05)

 SQLServerCentral Newsletter for April 5, 2021 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Migrating SSIS packages to Azure - Lift and Shift using Azure Data Factory]( - [Cherry-picking Migrations using Flyway]( - [Query Audit data in Azure SQL Database using Kusto Query Language (KQL)]( - [From the SQL Server Central Blogs - Server Review Essentials for Accidental and Junior DBAs—Client Onboarding (Part 4)]( - [From the SQL Server Central Blogs - Moving Into Consulting 101 - Part 4 - The Process of a Consultant]( Question of the Day - [Replacing Startup Options]( The Voice of the DBA  Daily Coping Tip Take an extra break or two today during work 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. Maintenance Windows Very early in my career I worked for a small company, and when we needed to upgrade or patch systems, we did it during the day, with employees accepting downtime. Of course, there were only 2 or 3 machines for the office of 10 at that time. Later I worked in a busy utility power plant and we had staff there 24 hours a day. Our maintenance window for the network was once a quarter, from Saturday midnight to 4 am. Going over that time required a variance from the plant manager, and we worked hard to avoid ever needing more downtime. Random reboots to fix things, in the late OS/2 and early Windows eras were grudgingly accepted, but not well tolerated. These days, individuals usually manage their own systems, but for many applications, clients want no downtime. IT departments have built many techniques to minimize downtime, but most systems I know do go down periodically for maintenance. Sometimes this is during business hours, sometimes at nights, and sometimes on weekends. I was thinking about this when I saw [maintenance windows for Azure SQL Managed Instance go into preview]( recently. Since this is a managed service, you can't choose anytime. Instead you can choose nights or weekends. Changes are blocked from 8 am to 5 pm, local time, but other than [24 hour advance notice](, you can't decide when updates occur. For those of us that build software, sometimes with workarounds, this is disconcerting, but I think this is the way of the new world. Many of the cloud vendors will dictate maintenance windows in some sense, and while some might allow you to delay upgrades, it will be for days, not months. Do you get to negotiate your maintenance windows? Or is your employer flexible for how you maintain your systems? Do you get regular times to patch or even deploy new software? Are there strict blockages during certain times of the year? I think it is important to make changes when there is limited impact to customers, but I also think we need to allow for some maintenance. I think it matters in not expecting staff to work extra hours when off hours work is performed, and give them some time back in their lives on other days. Many jobs do require work to be performed at night or weekends, so I don't know we'll ever get completely away from that, but I do think the more you adopt a DevOps flow, where you deploy small and often, the more likely you can do so during normal hours. That doesn't help with lots of maintenance, but I hope that vendors continue to come up with better ways of allowing us to maintain systems with minimal impact on customers. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Migrating SSIS packages to Azure - Lift and Shift using Azure Data Factory]( Randheer from SQLServerCentral Unlike an on premises SQL Server, Integration Services (SSIS) works very differently on Azure. If there are requirements to run our existing SSIS packages on Azure, then we need to understand our options. In this article we will talk about our options for migrating SSIS to Azure and what components are required to migrate SSIS packages. […] [Cherry-picking Migrations using Flyway]( Additional Articles from Redgate Describing the two main use cases for occasional "cherry picking" of Flyway database migration scripts: back-filling emergency production hotfixes to version control and managing parallel development streams. [Query Audit data in Azure SQL Database using Kusto Query Language (KQL)]( Additional Articles from MSSQLTips.com In this article we look at how you can user the Kusto Query Language (KQL) for querying Azure SQL Database audit data. From the SQL Server Central Blogs - [Server Review Essentials for Accidental and Junior DBAs—Client Onboarding (Part 4)]( Kevin3NF from Dallas DBAs Let’s take a look at the final few steps of your initial server intake process and get your workflow up and running as you push toward performing regular server... From the SQL Server Central Blogs - [Moving Into Consulting 101 - Part 4 - The Process of a Consultant]( Will Assaf from SQL Tact This is part four in a five part series this week, Moving into Consulting 101. Today's topics give you a numerical advantage in consulting. All have a common theme: don't wing...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Replacing Startup Options I have an old SQL Server 2012 instance that I am upgrading to SQL Server 2019. This is an in-place upgrade, and I see that there is a startup parameter: -h. This was used to reserve memory for Hot Add. What should I do with this parameter after the upgrade? 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) Where is the job id? I want to stop an asynchronous statistics update job with the KILL STATS JOB command. This needs a @jobid parameter. Where do I find that information? Answer: This is the job_id from sys.dm_exec_background_job_queue Explanation: This the job_id field returned by the sys.dm_exec_background_job_queue dynamic management view for the job. Ref: KILL STATS JOB - [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 [Dynamic parameter transfer in msdb.dbo.sp_send_dbmail]( - Hi all. I have a problem with email sending via SQL Server. I need to count the number of minutes in the previous month of the running year. The problem is that the e-mail should be sent every month and it should be determined dynamically (in April it should be for January, February, March). In […] SQL Server 2016 - Administration [SQL Server 2016 Encrypted Connections]( - Good day all, I am assisting our DBA team by conducting some investigations into making use of SQL Server encrypted connections for one of our older (VB6-based, yes I know!) applications. I have successfully applied a certificate to the SQL server, restarted the SQL Server service and all looks well. Running the following shows me […] [replication error]( - Job: expired subscriptoin clean up job failed due to the Could not find server ' VEN' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers. [SQLSTATE 42000] (Error 7202). The step failed. I checked in linked server and did not find […] SQL Server 2016 - Development and T-SQL [DATEDIFF() for times over a day long]( - I've got a T-SQL statement which I use for comparing the duration of an SSIS package against previous executions (to see if they're slowing down) but it doesn't work if the duration is longer than 24 hours. The problematic SQL is: -- THIS DOES NOT WORK DECLARE @today [varchar](12) = '44:10:27.766', -- OVER ONE DAY […] [Copy data from Source Sql Server to another destination Sql Server DB Table]( - Hi, I want to copy from source sql server table to destination sql server table. Requirement: Source Sql Server EU551 and DB Table tblEMP Destination Sql Server EU661 and DB Table tblEMP Both source and destination db table tblEMP have same schema. I want to check if source table has the data for current day […] Administration - SQL Server 2014 [Encrypted Database copying sometimes works]( - I have had a process to move a production database to a test server going for a few years now. The developers wanted a second copy of the same database created so we setup a new database and then restore the same backup to the new database. After the restore sometimes it works fine and […] Development - SQL Server 2014 [Problem when copying from a table into another table]( - Hi All.I have a wired situation here. I have 2 tables: table 1 with let say 5 records on it and an empty table2 (same schema), Trying to copy records from table1 into table2. When my script says(i am trying to make my script simple) with x as (select * from table1 ) insert into […] SQL 2012 - General [How to get spare no from categories 1 table where not exist per same spare no on]( - How to get spare no from categories 1 table where not exist per same spare no on table categories 2? so i need to make select statement query select spare no from table categories 1 that have different categories on table categories 2 per same spare no as example spare no 1350 have category 5902 […] [How to get Part Id that have part level 0 and not have map from ?]( - How to get Part Id that have part level 0 and not have map from ? I work on SQL server 2012 I face issue I can't get Parts that have map to and not have map from for part level 0 so Firstly I get parts that have part level 0 then secondly if […] SQL Server 2019 - Administration [SQL Server 2008 R2 Audit Log]( - In my sql server instance I renamed 'sa' account and disabled. I confirmed no sa account. I configured server side audit log and I can see some sa activities. How is it possible?  [There and back again: From a partition number to a filegroup and vice versa]( -  I've been trying to navigate the DMVs concerning partitions and filegroups and find my way between them. I might have a partition number and want the filegroup name(s) (and ultimately the files in the group). Or, I might have a filegroup or filename and want the partition(s) that it holds. Here's my test case: USE master; DROP DATABASE IF EXISTS TestParts; CREATE DATABASE TestParts ALTER DATABASE TestParts ADD FILEGROUP FG1 ALTER DATABASE TestParts ADD FILEGROUP FG2 ALTER DATABASE TestParts ADD FILEGROUP FG3 ALTER DATABASE TestParts ADD FILE (NAME=File1, FILENAME = 'C:\temp\File1.ndf', SIZE = 1MB) TO FILEGROUP FG1 ALTER DATABASE TestParts ADD FILE (NAME=File2, FILENAME = 'C:\temp\File2.ndf', SIZE = 1MB) TO FILEGROUP FG2 ALTER DATABASE TestParts ADD FILE (NAME=File3, FILENAME = 'C:\temp\File3.ndf', SIZE = 1MB) TO FILEGROUP FG3 Now, create a partitioned table: USE TestParts […] SQL Server 2019 - Development [filter by WHERE has no effect]( - SQL WHERE clause not working trying to filter by date "WHERE [dtmDocDate] >= '2020-09-01'" has no effect Issue is related to a fairly complex query that includes: DECLARE @orderByClause nvarchar(MAX) = '' DECLARE @whereClause nvarchar(MAX) DECLARE @tableHasIdentity bit DECLARE @sql nvarchar(MAX) DECLARE @columnList nvarchar(MAX) DECLARE @valueList nvarchar(MAX) DECLARE @Column_ID int DECLARE @Column_Name varchar(128) DECLARE @Data_Type […] [How to ALTER COLUMN without SELECT and UPDATE permissions]( - I try to ALTER a column by changing the datatype from integer to NUMERIC(35,10). IMPORTANT : The user that execute the DDL has db_ddladmin role and that's allright because we only want it to do DDL instructions and no be able to extract data for security reasons. With this query : ALTER TABLE tablename ALTER […] [must be drop table ?]( - Hello, i'm an Italian developer (excuse me for my english). I have a Store Procedure like this BEGIN TRY BEGIN TRANSACTION myTransaction DECLARE @TempMrc TABLE (idServizio int, idRgp int, Salita bit, Data Date) DECLARE @TempReport TABLE (Data Date, Salita smallint, Discesa smallint) DECLARE @crs CURSOR SET @crs = CURSOR LOCAL FAST_FORWARD READ_ONLY FOR .... OPEN […] Analysis Services [Azure Analysis Services Memory Usage]( - Hi there. I'm trying to identify what users or processes/queries are consuming the cube server memory. An example is seen here from the azure portal metrics graph. I know you can do the 'discover sessions' in management studio but all those metrics don't particularly help much. Does anyone know a good tool that can provide […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -