A brief history of SSIS evolution (2021-11-12)

SQLServerCentral Newsletter for November 12, 2021 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [SQL Access to Redis Data]( - [A brief history of SSIS evolution]( - [DBAs at work #1: The IT services provider DBA]( - [Performance of querying blob storage with SQL]( - [From the SQL Server Central Blogs - #PowershellBasics: Select the columns in the output.]( - [From the SQL Server Central Blogs - Microsoft Ignite Announcements Nov 2021]( Question of the Day - [Preventing Changes After the Tail-Log Backup]( The Voice of the DBA  Daily Coping Tip Be creative – cook, draw, write, paint, make, or inspire. 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. Is the Database a Series of Microservices? Not too long ago I was explaining to a group of developers a few reasons why database development and deployment is more challenging than application work. As I was talking about the different objects and the dependencies between them, an analogy occurred to me. The database is like a series of micro-services. We have these objects that are compiled and built separately, but with dependencies between them. A view depends on tables. Stored procedures and functions might depend on tables or the reverse might be true. Each essentially has an API, which is the structure of the table or other object. Each could be developed and deployed independently, and there could be conflicts that might be noticed at compile time due to deferred name resolution. Just like micro-services. In the case of application micro-services, each has an API and exists independently of others. They can be deployed, upgraded, and altered separately. If there isn't coordination with other micro-services, there could be problems with how the entire application works. Relational database development in teams is always challenging, precisely because each item can be independently build and compiled. Unlike a C# or Java compiler, all of your code isn't evaluated at once. While you can use a technique such as schema binding to enforce some checks across objects, these are crude mechanisms that interfere with a lot of development changes, especially those where we are trying to approach zero downtime deployments. There is also power in this independence, allowing us to make small changes over time. If we structure a series of deployments well, we can achieve very close to zero downtime changes, though usually at the expense of extra space and processing for a period of time. As I think about each table, view, procedure, and function being a separate service, I can more easily understand the dependencies that exist between them, the need for close communication with other teams. Developers also start to realize that just as they need to coordinate changes across teams when their service API changes, they can apply the same idea to the database. They can also make some changes without worry, as we can ensure backwards compatibility in many cases. A lot of the challenges in database development feel less daunting when we re-frame them. Once we stop viewing the database as a single thing and realize that each object is independent and has a contract, implicit or explicit, with other objects, we can more easily plan on how to make changes in a way that minimizes the impact on other teams. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [SQL Access to Redis Data]( JerodJ from SQLServerCentral Traditionally, importing key-value data stores such as Redis into BI, reporting, and ETL tools is problematic, if not impossible. With the CData Drivers, there are several different ways to building a traditional database model to easily work with Redis data in the BI, reporting, ETL, and custom applications of your choice. In this article, we discuss […] [SQLServerCentral Article]( [A brief history of SSIS evolution]( Daniel Calbimonte from SQLServerCentral Learn about the history and different versions of Integration Services (SSIS) in SQL Server. [External Article]( [DBAs at work #1: The IT services provider DBA]( Additional Articles from Redgate Our series of ‘DBAs at work’ blog posts feature conversations with IT leaders and experts about the challenges of managing and monitoring their server estates. Episode 1 features Dennis Heitmann, Database Administrator at Atruvia, which provides IT services for banking clients in Germany. [External Article]( [Performance of querying blob storage with SQL]( Additional Articles from SimpleTalk It’s possible to query blob storage using SQL, but what about performance? In this article, Dennes Torres compares several query methods to see which one performs the best. [Blog Post]( From the SQL Server Central Blogs - [#PowershellBasics: Select the columns in the output.]( Kenneth.Fisher from SQLStudies After my post last week on sorting the list, I now have a list of files, sorted in the right ... Continue reading [Blog Post]( From the SQL Server Central Blogs - [Microsoft Ignite Announcements Nov 2021]( James Serra from James Serra's Blog Microsoft Ignite has always announced many new products and new product features, and this year was no exception. Many exciting announcements, and below I list the major data platform and...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Preventing Changes After the Tail-Log Backup I have a database that I am moving to a new instance. I have restored a full and numerous log backups to the new instance already. Now I want to take a final tail-log backup of the database, but prevent anyone from changing any data in this copy of the database. What can I do? 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) SqlClient for .NET Protocols What can I do to control the order in which protocols are tried for the .NET SqlClient? Answer: You cannot change the order Explanation: The order cannot be changed. It is TCP, then named pipes. Ref: Client Protocols Properties - [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 2016 - Administration [TLS 1.2 communication problem]( - I'm trying to get 2 servers to communicate using TLS 1.2. On the calling web sever I have disabled TLS 1.0 and TLS 1.1 - leaving TLS 1.2 enabled. On the SQL Server TLS 1.0, 1.1 and 1.2 are all enabled. I have installed MSOLEDBSQL driver on both servers. The database server is running SQL […] [Automate loading data to SQL Server 2016 from Oracle DB]( - Hi SQL Server Mentors, We have a table in SQL Server 2016 that is frequently feeded up with data from an Oracle DB table based on a Query. I do it manually thru SQL Server import and export wizard. However I need to automate it as a batch process or stored procedure. Please advise me […] SQL Server 2016 - Development and T-SQL [How To Re-Insert a Row with Identity Value]( - I have accidentally deleted a record which originally looked like this:  But now looks like the following: As you can see row, 515 13 DriverId has been deleted. I want to re-insert the row with ColumnID 515, however because ColumnID is an Identity value i.e. Auto-Increments I can only enter a row with ColumnID […] Administration - SQL Server 2014 [security related question]( - Hi All, Have a question regarding database roles in SQL Server. While assigning the database role for a database user, we have roles like db_datareader & db_writer roles. Similarly, we have db_denydatareader & db_denydatawriter roles. My question is, what is the use of these 2 deny roles. If I don't want to a user to […] Development - SQL Server 2014 [Bulk Insert - Only Half the Records]( - I am having a hard time with this. I am trying to load a *.txt file using BULK INSERT, but no matter what I do, it seems to only load about 50% of the records. Our *.txt file has two rows of "header" data, and one row of "footer / trailer" data. Unfortunately, this is […] SQL 2012 - General [How to get Feature Name and Feature Value separated by $ for table AllData ?]( - I work on SQL server 2012 I face issue I can't get Feature Name and Feature Value for Table All Data From table Part Attributes Feature Name and Feature Value exist on table Part Attributes full sql structure and query found here UPDATE Codes SET Proceed=0 DECLARE @Code VARCHAR(20) DECLARE @ZPID INT DECLARE @Sql […] SQL Server 2012 - T-SQL [Query runs slow sometimes and sometimes runs superfast (2 minutes vs 2 seconds)]( - This query runs very slow at times and very fast at time. What could cause this behavior? i checked the slowness is coming from auditLog table. Its a big table as it stores audit records. But still cant figure out why its very slow at times.  select * from ( SELECT coitem.[ship_site] ,custaddr.[name] ,coitem.[co_num] […] SQL Server 2019 - Administration [Permissions Error Running DTEXEC on SQL Agent job]( - For "reasons," our corporate office is requiring us to move all our SSIS packages to the SAN share on our servers and call the package with a Command Operating System task. It's incredibly frustrating but we've done it before on SQL 2005, so it's not unknown to us. Unfortunately, SSIS now requires administrator access to […] [Monitoring system]( - We are setting up Idera SQL DM system to monitor our SQL Server's. So we have availability group setup for the repositories at database level. Looking for option to see if anyone has done application HA? If yes, could you please shed some light on the application clustering for Idera SQLDM tool. Thanks in Advance! SQL Server 2019 - Development [How to retrieve a number in range that matches another number]( - Ive got a lookup table with a set of values . this is as follows DROP TABLE IF EXISTS [dbo].[SIData_NTILE_Test_20211012_6months_MainLevelData_TableRecordCounts] CREATE TABLE [dbo].[SIData_NTILE_Test_20211012_6months_MainLevelData_TableRecordCounts]( [ID] [int] NULL, [NumbeOfRecords] [decimal](15, 6) NULL, [NumberOfGroups] [int] NULL, [TimeInterval] [decimal](15, 6) NULL ) ON [PRIMARY] GO INSERT [dbo].[SIData_NTILE_Test_20211012_6months_MainLevelData_TableRecordCounts] ([ID], [NumbeOfRecords], [NumberOfGroups], [TimeInterval]) VALUES (1, CAST(1555201.000000 AS Decimal(15, 6)), 1944, CAST(8000.000000 […] [SQL Server Job Agent - The workgroup file is missing]( - Hi firstly im sorry if this is in the wrong place, i couldnt find a ssis forum. I have been working with a mariadb, trying to get the data into sql server. A very long way around it maybe but i found one solution to create a linked table in access and then import the […] Azure Data Factory [Pass a list in a stored procedure in ADF]( - Hi there i m stuck in how I can pass a list of parameters using the copy data on Adf is it a limitation with the tool ? does anyone know ? my sp is running as exec sp1 @var1 when u declare and save the values in that variable it runs fine any pointers […] SSRS 2016 [SSRS: Possible Transition to PowerBI?]( - Hello, All: I was wondering if anybody has made a full transition from SSRS (2016) to PowerBI? Are there any difficulties involved with converting SSRS reports to PowerBI? Are there tools that can already do this, or will it be a slog of rebuilding them Report-By-Agonizing-Report (with apologies to Jeff Moden)? Thank you in Advance! […] General [How to learn SQL?]( - Hi everyone, I would like to learn MySQL, and can anyone share some tutorials with me? I have been googling but I decided to join a community and ask the experts for the advice Integration Services [Help Converting Query For Hierarchical Data]( - I have a working query within MySQL based on (Managing Hierarchical Data in MySQL - . The problem I have is that I need to implement the same functionality on another piece of software which uses MSSQL. The two issues I am facing is that it doesn't seem possible to group/order within subqueries in MSSQL, as it […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -