How to deploy and execute an SSIS package from the SSISDB catalog (2021-03-19)

 SQLServerCentral Newsletter for March 19, 2021 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [How to deploy and execute an SSIS package from the SSISDB catalog]( - [Documenting a SQL Server Data Catalog in HTML and Git Markdown]( - [Azure AD Authentication for Azure SQL Databases]( - [From the SQL Server Central Blogs - Rebuilding SQL Saturday–Picking a Board of Directors]( - [From the SQL Server Central Blogs - My new Home Studio Setup]( Question of the Day - [Checking out a remote branch]( The Voice of the DBA  Daily Coping Tip Listen deeply to someone and really hear what they are saying 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. Tool Limits In many jobs, there are a variety of tools that people use on a regular basis. Chefs buy knives, mechanics buy tools, doctors buy stethoscopes, etc. While many of us in technology don't always have to buy tools, some of us certainly do. When there is value and it makes sense, many of us have purchased IDEs or add-ins that make our work easier. Our tools are different than physical ones. We need a place to install them, but we often have employer-owned workstations, which complicates matters with licensing and rights to install. Bringing tools with you, or taking them with you, is often a strange situation, especially as our tools often need "privileges" to connect to other systems, which may be a problem for many employers. Many employers do provide some tools, and we can certainly build others. We know how to write scripts, utilities, code generators, and more. We may get an IDE or some other commercial tool, which often ensures that all team members work in the same manner. My employer, Redgate Software, builds tools like this for database developers, and we strive to ensure that our tools make your job easier. Forgetting the hassles of purchase and installs, today I'm wondering what you think about the tool you do use. What tools do you use, and what are the limitations or hassles you find? Are there issues with your tools that inhibit productivity? These might be ones someone in your organization has written, or those they've purchased. Or maybe you've extended some tools to work in a different way and you're proud of your work. Let us know about how tools might dramatically help or inhibit your efficiency. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [How to deploy and execute an SSIS package from the SSISDB catalog]( Stan Kulp-439977 from SQLServerCentral.com Beginning with SQL Server 2012, SQL Server Integration Services packages can be deployed and executed from a SQL Server database named SSISDB, which serves as a repository for SSIS packages. [Documenting a SQL Server Data Catalog in HTML and Git Markdown]( Additional Articles from Redgate Dave Poole explains the need for high quality database documentation and then demonstrates how to document the SQL Server database for a data catalog, in both HTML and Git Markdown, using SQL Doc, SQL Data Catalog, PowerShell, and a few helper scripts to ensure consistency and correctness. [Azure AD Authentication for Azure SQL Databases]( Additional Articles from MSSQLTips.com In this article we walk through how to connect to Azure SQL Database using Azure Active Directory authentication along with multi-factor authentication. From the SQL Server Central Blogs - [Rebuilding SQL Saturday–Picking a Board of Directors]( Steve Jones - SSC Editor from The Voice of the DBA With Redgate planning to donating the SQL Saturday brand, trademarks, and domain to a non-profit foundation, there is a need to build a group of individuals to voluntarily manage the... From the SQL Server Central Blogs - [My new Home Studio Setup]( Klaus Aschenbrenner from Klaus Aschenbrenner (Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Checking out a remote branch I have cloned a repo from someone else, but I don't see one of the branches that is shown on GitHub. I want to work on this branch. How do I check this out and get the code from the branch, mybranch? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by The Dixie Flatline) How Many Rows Inserted? Without running the code below, how many rows will be in the #table at the end of this batch? IF OBJECT_ID(N'tempdb..#table') is not null DROP TABLE #table declare @TC int CREATE TABLE #table (SomeData varchar(20) primary key) SET IMPLICIT_TRANSACTIONS ON SELECT 'After Set Implicit, @@Trancount='+str(@@TranCount) BEGIN TRAN SELECT 'After Begin Tran, @@Trancount='+str(@@TranCount) INSERT INTO #table SELECT 'X' IF @@TRANCOUNT > 0 COMMIT TRANSACTION; set @TC = @@TRANCOUNT SELECT 'After COMMIT @@Trancount='+str(@TC) union all SELECT 'After COMMIT #table has'+str(count(*))+' rows.' FROM #table if @@TranCount > 0 ROLLBACK set @TC = @@TRANCOUNT SELECT 'After ROLLBACK @@Trancount='+str(@TC) union all SELECT 'After ROLLBACK #table has'+str(count(*))+' rows.' FROM #table Answer: 0 Explanation: In this code, the SET IMPLICIT TRANSACTIONS ON sets a @@trancount of 1. I am not sure why this occurs, but it means that after the COMMIT, there is still a transaction that is affected by the rollback. At the end, there are 0 rows in the table.  [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 [Database list and the last access date]( - I've SQL like this, SELECT name as [Database Name], [Last Access Date] =(select MAX(temp.lastaccess) from ( select lastaccess = max(last_user_seek) where max(last_user_seek)is not null union all select lastaccess = max(last_user_scan) where max(last_user_scan)is not null union all select lastaccess = max(last_user_lookup) where max(last_user_lookup) is not null union all select lastaccess =max(last_user_update) where max(last_user_update) is not null) […] [cost licence calculated]( - Good morning all , How is the cost of the SQL CAL license calculated (Per number of Users) this is the SQL 2019 Standard Edition and according to you which is the cheapest CAL or Core license Thank you for your feedback SQL Server 2016 - Administration [How to check if a column exists in SQL Server table?]( - I need to add a specific column if it doesn't exist. I have something like the following, but it always returns false: IF EXISTS(SELECT *  FROM INFORMATION_SCHEMA.COLUMNS  WHERE TABLE_NAME = 'myTableName'  AND COLUMN_NAME = 'myColumnName') How do I check if a column exists in the SQL Server database table? SQL Server 2016 - Development and T-SQL [BCP - Export Query to Tab Delimited Text File]( - Dear Group: I am using SSMS and trying to export a query to a text file using T-SQL (I know there are options in the tool itself to do this, but I need this code for a stored procedure). I found examples but none of them seem to work and each gives a different error […] [Does SQL 2016 allow for column encryption?]( - I have a feeling I've asked this question before, but I can't find my having asked it previously. I'm sorry if I've already asked this question. We have a need to encrypt some data in the columns that they're stored in. However, we've no idea how that is done, both to store the data encrypted […] SQL 2012 - General [How to compare development and production server database tables schema]( - Hello Everyone, We have converted a existing web application application from Asp.net Web forms to Asp.net MVC. The existing web-application hosted in dedicated server and the new one we are moving to Ms Azure cloud. On the development server we have Microsoft SQL Server 2012 - 11.0.2100.60 and in Azure cloud environment we have Microsoft […] SQL Server 2019 - Administration [Connection issue from SSRS to SSAS data source]( - I have a data source defined on our SSRS server and it connects successfully when I am in IE but from Chrome or Edge, it fails. I ran Server Profiler on the SSAS server and see that when I Test Connection with IE, my user account logs in but when I do the same in […] [SSMS Encryption]( - Hello, Please forgive the elementary nature of this post -- I've been tasked with encrypting certain fields in our Azure SQL Server DB -- I have never done this and so far I have done the following: -Right-clicking on the column I want encrypted, and select 'Encrypt Column' -After following the prompts, SSMS created a […] SQL Server 2019 - Development [String Manipulation Work?]( - Hello Geniuses, I have a string manipulation task that I am not sure is even possible...  Here is the data CREATE TABLE #TEMP ( Id INT IDENTITY(1,1), Sales Money, StateCode VARCHAR(2) ) INSERT INTO #TEMP (Sales, StateCode) Select '1200', 'MA' UNION ALL Select '200', 'MA' UNION ALL Select '120', 'GA' UNION ALL Select '190', […] [tune a table valued function that is using recursive CTE]( - We have a function that calculates student truancy records by counting their absence in a moving 30 days window. It uses a recursive CTE to count any 30 days passed. Then we use other table to join this table valued function, and it was extremely slow as records increased. I tuned it to select the […] [SchemaBinding and Temporal Tables]( - I have schema bound a view to a temporal (system-versioned) table and receive no errors in doing do. However, I am able to delete/modify columns from the temporal table, without issue. When I do a query against the view, it fails with a binding error. Has anyone else tried this? Should it work? BTW...on the […] [T-SQL SubQuery Help Please]( - Hi, I know this will be rather obvious to many, but I am struggling writing a sub-query at the moment. I am trying to return just the 1 row for each BadgeNo attendee based on the latest date updated joining it with a conf_code in table B. (Illustrative data below) Table A    […] SSRS 2012 [How to set up a zoom in the browser]( - Hi all, I have a problem with zoom option in SSRS. I published my report on the SSRS Report Server. After that I open it in GoogleChrome and when I go to the browser setting and change the zoom (for example 100 to 120) - my parameters are shifted. When I zoom in screen and […] SSDT [Build SQL Server Data Project on Ubuntu]( - Hi everyone, we are currently designing CI/CD Pipelines and the build server we have available at the moment is a Ubuntu machine. I investigated a lot and it seems that this combination is just a bad idea and I really need a windows build server. Basically the CI pipeline is just a task to pull […] Hardware [Block Size on MSSQL on Linux]( - Do I need to set my block size of drives on linux at 64k allocation size like I do on windowsOS?   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -