Modular ETL Architecture Design in SSIS (2019-10-21)

 SQLServerCentral Newsletter for October 21, 2019 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Modular ETL Architecture Design in SSIS]( - [Reporting Services Basics: Adding Groups to Reports]( - [From the SQL Server Central Blogs - Database Fundamentals #24: Filtering Data]( - [From the SQL Server Central Blogs - Launching SQL Compare with Context]( Question of the Day - [Backing up SSAS Databases]( [Redgate University]( The Voice of the DBA  Losing The Security Game It's sad some weeks to see reports of security issues at large companies. It's also discouraging some times when clients or friends will talk about security issues they've had in their organization. What's mostly disappointing is how easy many of these issues would have been to prevent with a little effort. Joey D'Antoni made some fun of this with his [Data Breach Game](/media/ECG/redmondmag/Images/2019/10/1009red_databreach1b.ashx). It's a bingo card you can print out and use the next time you hear about an issue. My guess is most of us could win this in about a week with the general state of security in most places. Some of you might win this in a day with inside knowledge. This is poking a little fun at the poor security practices of many places. There's a wider [article about 9 poor security practices]( you can read, with some notes about what you should be doing instead. When you read it, you'll wonder why hasn't someone just made these simple changes and dramatically improved security? I have asked myself that many times when I've seen some environments. Ultimately, no one wants bad security, but we (as a group) often make poor choices because we're in a hurry. We can, and should to better. All of the items on this list can be avoided, and should be. Even the complexities of SQL Injection can be fixed with a little code refactoring. No time or that's too hard? You should be building software in a Compliant Database DevOps manner. I like the list, though I wish ElasticSearch where on there in number 6 with MongoDB. Too many breaches this year from people dropping that server on their network without a password because they need full text searching of data. Don't make that mistake. Always, always, always set a password on data resources. Developer or partner complaints aren't worth the risk of losing data from an unsecured server. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums]( [Redgate University](   Featured Contents [Modular ETL Architecture Design in SSIS]( aveek22 from SQLServerCentral Overview of ETL Architecture In a data warehouse, one of the main parts of the entire system is the ETL process. ETL is the system that reads data from the source system, transforms the data according to the business logic, and finally loads it into the warehouse. While fetching data from the sources can seem […] [Reporting Services Basics: Adding Groups to Reports]( Additional Articles from SimpleTalk Most SSRS reports are organized into grouping levels where it’s possible to add totals for each group. In this article, Kathi Kellenberger continues her SSRS series by walking through how to create both a Matrix and Table report. From the SQL Server Central Blogs - [Database Fundamentals #24: Filtering Data]( Grant Fritchey from The Scary DBA If you’ve been reading these Database Fundamentals posts, you’ve already seen the WHERE clause because of your use of it when manipulating data with DELETE and UPDATE statements. It’s... From the SQL Server Central Blogs - [Launching SQL Compare with Context]( Steve Jones - SSC Editor from The Voice of the DBA Years ago Redgate Software started some work to link our tools together. I remember early efforts, about which I had dubious thoughts as to the value. I hadn’t looked...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Backing up SSAS Databases I have an SQL Server 2017 Analysis Services (SSAS) database that I wish to back up. To speed things up, I'd like to stripe the backup across multiple files. Is this allowed? 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) Stopping a Container I have a SQL Server 2017 container (named sqltest) that was created with the docker run command. I connect to the container and add a login. I then run this: docker stop sqltest That afternoon, I come back to my workstation and run docker start sqltest Which of these describes the status of my login? Answer: The login is still there Explanation: Pausing or stopping the container does not kill or remove it. The login is still there when the container is started or unpaused. If the container were killed, the login is lost. If a new container started from the same image, the login would not be there. Ref: Quickstart SQL Server in a Docker Container - [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 [Extended Events- how to log to an SQL table?]( - I want to enable an Extended events session. However, I don't want it to go to a file on the HDD, I 'd really like to send it all to an SQL table so I can do grouping, analysis, reporting, etc... How can I get that data into an SQL table for longer term storage […] [Error after migrating SSISDB to a newer version of SQL server.]( - I tried to move SSIS database catalog on SQL server 2014 to a new server SQL 2017. I created the catalog first on new server, and then restored the database SSISDB from old server to new server. And restored the master key. Then I run the following stored procedure and got an error: Use SSISDB […] SQL Server 2017 - Development [getdate() to east time]( - hello someone aware how to convert getdate() to at time zone EASTERN TIME can you help me please SQL Server 2016 - Administration [Very large t-log with backups]( - I have a database that's 125GB, but the t-log is 190GB. I do full backups each day and t-log backups every 15 minutes. I've verified the log backups have been successful for the past week. Generally when I see a large log like this it is because backups are failing. The log isn't growing out […] [Optimizer switching sql execution plans]( - Hi, I have noticed that Optimizer is switching to good and bad plans randomly after IndexRebuild and Update stats job. For example, for 1 week it is using good plan where procedure is taking 1 sec and after that it is switching to bad plan and procedure is taking 50 sec. Is this behavior related […] [Issues trying to restore a backup file from SQL Server 2016 to SQL Server 2012]( - Hello, I'm attempting to use a specific backup file from a week ago that was taken from a SQL Server 2016 instance and restore it as a new database onto a different instance, which uses SQL Server 2008 R2. When I try to script this, I get the following error: "The media family on device […] [Always-On AG Cluster: Suspect database on secondary node, backup failing]( - Setup: Three node AO AG cluster, Node1 and Node2 synchrounous in DC1, and Node3 ansynchronous in DC2. Hi guys, we had an incident some time ago, the root cause seems to be that because of some disk issues, one db went offline/suspect on the Async node in DC2, this caused a t-log backup to fail, […] SQL Server 2016 - Development and T-SQL [how to print NULL for datetime column]( - i have a variable called date @Date DATETIME = NULL, i want to capture this variable into error log information set @log =   @Log + ' ,Date: ' + @Date but this is not capturing anything. i have tried convert(varchar(20),ISNULL(@Date,GETDATE()),110) it worked ; but i don't want to capture getdate. i want to print NULL.  [Range of values not from a table]( - Hi, I want a range of values between a start number and end number say 10000 to 50000 using SQL, but these are not stored anywhere on a table. The idea is to then compare each value with the user-ID of a database table to see which ID is missing and which exists. e.g. In […] [Max window function with order by]( - Hello Everyone Thought I knew how Window functions worked in SQL Server but that was before I ran below script This script is part of a CTE which is then fed into other CTEs downstream - but I am having hard time understanding how the MAX function here is deriving maximum date for each row […] [empty sys.dm_exec_query_stats]( - Hello  I am having an issue with the query stats dmv If I run a statement or execute an SP I would expect it to generate a row in sys.dm_exec_query_stats Sometimes it does but more often than not it doesn't I'm also finding that when it does, if I query sys.dm_exec_query_stats a couple of […] [SQL Query with CTE or other...!]( - Any one can help me please with this issue? I really appreciate this help. The number of Answer is dynamic. SQL Server 2012 - T-SQL [Script multiple queries]( - Is it possible to script multiple queries? I need to transfer about 30 queries from one server to another w/o copying the entire database. I was hoping i wouldn't have to script out each query individually in SSMS. Reporting Services [Failed Executions]( - Curious what everyone else is doing... I've always had a daily report showing me report subscriptions that failed. I've had a task to better monitor all report interactions (subscriptions, cache refresh and interactive) sitting in my queue, but I put it off because of other higher priority tasks. Right now I'm in the process of […] Integration Services [SSIS task and transformation types in SSISDB]( - Hello  Does SSISDB hold the type of task anywhere It would make querying for slow tasks or data transformations much easier Also, look at history to see if specific transformation timescales have changed etc.  Thanks    [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -