Export and Import SSISDB in just a few clicks (2023-09-01)

SQLServerCentral Newsletter for September 1, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Export and Import SSISDB in just a few clicks]( - [Setting up Drill Through in the New Power BI Report User Interface]( - [Working around schema drift in SQL Server]( - [From the SQL Server Central Blogs - Book Review – Scalable Data Analytics with Azure Data Explorer]( - [From the SQL Server Central Blogs - Copilot Experiments: A little PowerShell help]( - [Big Data Analytics with Spark: A Practitioner's Guide to Using Spark for Large Scale Data Analysis]( Question of the Day - [Once a Month]( The Voice of the DBA  Looking Forward to the PASS Data Community Summit 2023 I was lucky enough to attend the very[first PASS Summit]( in 1999. It was a brand new event, and while not overly large, it was busy and crowded in the basement of a Chicago Loop hotel. I had the chance to meet [Kalen Delaney]( there and ask her a question. That was the highlight of the trip, but I also learned a lot about SQL Server 6.5 and 7 there, spending all the time I could in sessions and hanging out after each one listening to the questions others asked the speakers. Since then, I have been lucky enough to go to most of the [Summits](. The event has changed a bit, and I look forward to going to see friends each year and re-connect with them. I've also loved meeting new people, often those I've corresponded with online. Each event has been memorable and exhausting at the same time. It's a busy week, and I can sometimes feel overwhelmed. However, it's always felt like it was worth the trip. I saw [a video from Kendra Little]( where she talks about what she's looking forward to this year. She has a few things on her mind: connecting with people, learning from the people who write the software, learning from those that use the software, broadening her horizons, and being a part of the data community. My thoughts are similar. Last year I was looking forward to the event in person, but apprehensive with all the commitments I had for Redgate with speaking. This year I'm much less involved, working more on things before the Summit and I'm hoping I can enjoy the experience more with less work during the event. For many years I was always excited about the SQL Server Central party. That was a highlight for me and I was sad when the referrals that funded it went away. Perhaps I'll get find a way to get it back in the future. For now, I want to connect with more people. I want to do this casually in a few ways. From the #sqltrain on Sunday to a few quiet dinners with friends during the week to taking time at night to attend some of the events that various vendors will sponsor, that will be a great chance to bond with friends (new and old) in a social way. I also want to connect more professionally with people, which I'll do in the Community Zone, as Kendra suggests, but also around the convention center. I am planning on not having to rush to many things, which means I'll have more time in hallways and after sessions to talk tech with speakers and attendees. I'm always amazed by the ways some of you use databases and software, and I find myself learning creative solutions that I might suggest to others in the future. The last thing that I'm looking forward to is the chance to motivate a few more people to run SQL Saturday events in 2024. We've had some new events in 2023, a few more than 2022, but not nearly as many as we used to see each year. I'm hoping to connect with more community leaders and volunteers to try and get them to consider organizing an event in the future. The Summit is a great investment in your career as a data professional, and it can be a good investment for your employer. Make a case, show them you want to learn and grow, and that what you learn there will help you in your job. It's a park for those of you that bring value to your employer and the can help with retention. You'll gain knowledge and make contacts that help you on a daily basis. I hope to see you in Seattle this November. Note: [The price goes up Sept 20]( so get [registered]( before then. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Export and Import SSISDB in just a few clicks]( Kunal Rathi from SQLServerCentral In this article, I will describe how to export and import SSISDB using SSIS Catalog Migration Wizard in just a few clicks. [External Article]( [Setting up Drill Through in the New Power BI Report User Interface]( Additional Articles from MSSQLTips.com In this article, learn how to implement drill-through in Power BI reports using the new user interface. [External Article]( [Working around schema drift in SQL Server]( Additional Articles from SimpleTalk At Stack Overflow, our environment has multiple implementations of a largely – but not 100% – identical schema. (By “schema,” I mostly mean the set of tables within a database.) I sometimes need to retrieve or update data across a large number of sites. Let’s say, pulling or removing information from the Users table, and related tables, across all of our databases. [Blog Post]( From the SQL Server Central Blogs - [Book Review – Scalable Data Analytics with Azure Data Explorer]( Koen Verbeeck from Koen Verbeeck I recently finished the book Scalable Data Analytics with Azure Data Explorer (Modern ways to query, analyze, and perform real-time data analysis on large volumes of data), written by... [Blog Post]( From the SQL Server Central Blogs - [Copilot Experiments: A little PowerShell help]( Steve Jones - SSC Editor from The Voice of the DBA It’s been a little while since I’ve had time to relax a bit and try some AI help. This is another experiment I made. A user on SSC asked... [Big Data Analytics cover]( [Big Data Analytics with Spark: A Practitioner's Guide to Using Spark for Large Scale Data Analysis]( Steve Jones - SSC Editor from SQLServerCentral Big Data Analytics with Spark is a step-by-step guide for learning Spark, which is an open-source fast and general-purpose cluster computing framework for large-scale data analysis. You will learn how to use Spark for different types of big data analytics projects, including batch, interactive, graph, and stream data analysis as well as machine learning. In addition, this book will help you become a much sought-after Spark expert.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Once a Month I have a procedure that I want to run once a month, on the first Tuesday of the month. What is the easiest way to get this done? 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) Deprecated Restore Options Which of these options is deprecated for the RESTORE command? Answer: both mediapassword and password Explanation: Both of these options are deprecated. The RESTORE arguments note this and the security section explains why. You ought to start refactoring processes that use these. Ref: - RESTORE (arguments) - [ - Restore (security) - [ [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 [Select blocking a Truncate stamemet]( - Hi All, I am seeing a SELECT blocking a TRUNCATE statement. THE SELECT is running over 4 hours and it is waitype is AYSN_NETWORK_IO. Isolation level is default read committed isolation level. Why or when does a SELECT takes "Sch-S” lock? I see this lock being acquired using sp_whoisactive. Truncate is waiting for="Sch-M" lock. How […] SQL Server 2017 - Development [Modifying the SSRS Data Source for particular reports]( - i'm trying to either write a sql script or power shell script to change certain reports Data Sources without having to do this manually via SSRS Administration - SQL Server 2014 [Server memory question]( - I would like to know please how much memory I should add to my server here is what I have 51452 in MB and we be adding more data and databases,please recond how better to maintain SQL Server 2019 - Administration [Securing access to SQL Instance]( - Hi, I have a need to harden-down users access to a SQL Server instance. I have a number of SQL authenticated accounts that need to be able to create and curate database objects on a dedicated schema. At the moment they have membership of the ddl_admin role on the database, however this is proving insufficient […] [Can I set the publisher database into read-only mode]( - Hi - Can you set the db into read-only mode if it’s part of replication. As part of migration, I need to set the DBs into read-only mode before I take final backup. Can I do this if the replication is still active to another server? [ALTER or DROP TABLE could not clean up root row within 10 seconds]( - We keep getting these errors quite frequently. How do we troubleshoot and fix these errors ? Are they benign in nature or need to be fixed. Please advise. [msdb job history permission error]( - SELECT * FROM msdb.dbo.sysjobhistory error: The SELECT permission was denied on the object 'sysjobhistory', database 'msdb', schema 'dbo'.  the user is a member of the following msdb role: SQLAgentReaderRole SQLAgentUserRole SQLAgentOperatorRole  What other permissions do i need? SQL Server 2019 - Development [if condition select into temp table]( - Hi, I have a query where I search and provide the result. select * into #a from( Select ID, 1 as counter from TableA where ID=@id union all select ID, 2 as counter from TableB where ID=@id)a declare @tocheck integer set @tocheck =(select tocheck from #a) if @tocheck=1 DROP TABLE IF EXISTS #partA begin insert […] [TRY-CATCH blocks ignore converting errors in ORDER BY statement]( - BEGIN TRY SELECT * FROM ( SELECT 'int' AS [text] ) AS tmp ORDER BY CONVERT (INT, tmp.[text]) PRINT '1' END TRY BEGIN CATCH PRINT '2' END CATCH Just had an interesting case today where it seems as if when you unsuccessfully cast text as an INT, then this looks to be a severity level […] [Security: Dynamic Order By clause using sp_executeSql]( - This is a contrived example - but illustrates what I'm trying to fix I have a code where the client can change the sort order via a parameter. DECLARE @pageTop INT = 1 DECLARE @pageBottom INT = 10 DECLARE @OrderBy NVARCHAR(500) = 'object_id desc'; DECLARE @SQL NVARCHAR(MAX) = ' SELECT * ,ROW_NUMBER() OVER(ORDER BY ' […] Amazon AWS and other cloud vendors [Is AWS the right choice for a dirt-simple API for myself only?]( - I’ve written a little Python script to post to multiple social medias at once, and I want somewhere to host it as an API so I don’t have to set up a static IP, keep a raspberry pi going etc. Essentially I want to be able to send a post request to this and have […] Integration Services [Merging Data Sets in similar way as VLookup]( - I want to automate my manual Vlookup using Excel to SQL/SSIS. Table: is used with  The columns that are the same colour are used for Vlookup. Currently. I am having trouble loading data from tables with non primary/secondary keys to tables with primary and secondary keys (tables:). Error I am getting: SSIS package "C:\Users\Anthony.DESKTOP-ES5HL78\source\repos\WeatherDataETL_\WeatherDataETL\WeatherDataETL\Package3.dtsx" […] SQL Server 2022 - Administration [Just updated server to 2022 - CXSYNC_PORT waits - HELP!]( - Hi Updated from SQL2014 to SQL2022 today. Seeing a ton of CXSYNC_PORT waits. Its as if something is not configured correctly, What can I check? This in an on-premise server. SQL22 ENTERPRISE Microsoft SQL Server 2022 (RTM-CU1) (KB5022375) SQL Server 2022 - Development [how new window clause works in following case.]( - hi, can i get one example of following from this link "If the is contained in a that is part of which is a simple table query, then the scope of the new window_name also includes the , if any, of that ."  yours sincerley [Multicolumn indexes and fragmentation]( - Is there anything special you should think of concerning index fragmentation and multicolumn indexes (sorry for possible bad terminology)? I am not sure how to think here, if I need to think about it all. Currently I've got a index that looks like this: int, int, bit, datetime. I think it's easier to understand when […]   [RSS Feed]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -