Importing a CSV into SQL Server Shouldn't Be This Hard (2021-07-14)

SQLServerCentral Newsletter for July 14, 2021 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Importing a CSV into SQL Server Shouldn't Be This Hard]( - [Mergers and acquisitions in Insurance]( - [Autoscaling in Microsoft Azure]( - [From the SQL Server Central Blogs - Basic Window Functions–#SQLNewBlogger]( - [From the SQL Server Central Blogs - Executing Azure Data Factory pipelines by Power App / Automate Flow]( Question of the Day - [Flipping the plot]( The Voice of the DBA  Daily Coping Tip Get outside and move to help clear your head 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. Improving DevOps Automation We use a lot of automation at Redgate. As a company that builds software, we want to ensure that all the changes from our teams get integrated and tested in a timely manner. We have multiple teams on some products, and while we do have regular meetings between them, it can be easy for a developer to miss some update or change that others are making and break something. Worse, they could cause a regression or security issue, which we work hard to avoid. Recently I saw a post from one of your lead software engineers about our build process. While we have lots of pipelines, the general process hasn't changed in many years. In some sense that's good, because we focus on building better software, not the process. We don't change just for the sake of change, or because a new team or division lead likes one tool over another. I've seen customers where they move from Jenkins to Azure DevOps because someone in charge "likes one better." Not a good use of time. However, you do need to evaluate whether your process is meeting your needs. In our case, we surveyed lots of developers to get their thoughts on pain points and issues. The build and release process was consistently listed as a pain point, with releases often requiring a dev to manage it from their workstation, preventing them from spending time building software for customers. That might be the big lesson I saw in the write-up. We realized that a non-negligible amount of time was being spent by developers on the process of moving bits rather than building the software. This led us to re-examine how things are done. In particular, we looked at the agent OS (Linux v Windows) and containers. Both of these are more viable technologies now than in years past, and they can reduce costs while smoothing the process. As a result, we are developing a general contract that helps us decide how to change our process. This is similar to an API, that doesn't specify the tool to use, but rather the inputs, outputs, and what the effect should be. From this, we'll start to help teams move forward is changing their process as we have time. The big takeaway right now is that this is pushing us to use containers, which simplify the steps and allow us to easily move from Azure to AWS to on-premises, or really any environment. We can switch build cross-platform, and more easily scale up or down as needed. It will take some time, and some of our software will be more challenging in containers. However, we are also seeing benefits from customers, and at some point, I expect we'll provide containers for certain functions that make it easier for the end-users of our software to also deploy and upgrade their tools. DevOps is an ongoing process. Not a set of tools that you change just because, or a way of building software that matches what another organization does. Instead, it's learning, experimenting, and evaluating how you can be more effective. Then adopting what you've learned and repeating the process. Keep improving, and you'll find that you can produce software quicker and at a higher level of quality while improving the skills of your engineers. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Importing a CSV into SQL Server Shouldn't Be This Hard]( Steve Jones - SSC Editor from SQLServerCentral A frustrating time importing CSV files leads Steve to test the different options. [Mergers and acquisitions in Insurance]( Additional Articles from Redgate The insurance sector has seen a marked rise in mergers and acquisitions over the past 12 months, and although these can be an excellent way to leap beyond organic growth, M&As can result in challenges with technology. In this blog, COEO's James Boother provides some tips on how, with good planning, technologies can be a real advantage in your next acquisition. [Autoscaling in Microsoft Azure]( Additional Articles from SimpleTalk The ability to scale up and down without maintaining extra hardware is one of the best cloud computing features. In this article, Mahendran Purushothaman explains autoscaling in Microsoft Azure. From the SQL Server Central Blogs - [Basic Window Functions–#SQLNewBlogger]( Steve Jones - SSC Editor from The Voice of the DBA Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. Window functions are a class of functions that... From the SQL Server Central Blogs - [Executing Azure Data Factory pipelines by Power App / Automate Flow]( Rayis Imayev from Data Adventures (2021-Jun-30) One of my university professors liked to tell us a quote, “The Sleep of Reason Produces Monsters”, in a way to help us, his students, to stay active in...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Flipping the plot I load the Motor Trend cars dataset in R Studio and then plot some data. > data(mtcars) > stripplot(cyl~mpg, mtcars) I get a plot like this: [R stripplot of data] I want to switch the data to this plot: [R data plot with axis reversed] Which code will give me this? 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) Starting with Full Text Search How do I enable Full Text Search in a SQL Server 2019 database? Answer: You don't enable FTS in a database Explanation: All SQL Server databases are full text enabled by default. Ref: Get Started with Full Text Search - [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 [Removing a SQL node from a cluster]( - First and foremost - I AM NOT A SYSTEMS ADMIN I'm a DBA. My windows admin and I built a sql cluster to do availability groups. It was both of our 1st time building for AG. It was a total success. Then we pointed our 3rd party software at it and it couldn't deal with […] SQL Server 2017 - Development [How to show the MIN Date Value in a query]( - After reading many posts, this is what I learned. SELECT MIN(StartDate) AS MinDate FROM Schedule WHERE WorkCenterID = 'LAY-W' AND ScheduleID = '5' GROUP BY StartDate It produces all three values. I just want the Minimum Date Value to be returned.  Thanks!  SQL Server 2016 - Development and T-SQL [Excessive run time]( - Hi  I have a number of queries that select data and put the data into a table. Data flow from one database and is loaded into another table in another database using a SSIS data flow task. I've noticed that on a good day, it works through them all fairly quickly (around 30-40 mins) […] Development - SQL Server 2014 [Using an Integer parameter in a WHERE clause]( - I have a simple query which has one parameter (@Amount); if I use it like the pasted code below, it works just fine: DECLARE @Amount int, @Client varchar(500) SET @Amount = '1170' SET @Client = 'Google' Select * from Invoices where Client like '%' + @Client + '%' and InvoiceAmount = @Amount But what if […] SQL 2012 - General [Rows to Column]( - Hai all I have one table one column. I have 10 rows that table . I want output, segregate one column to three column rows accordingly  Create table #test(Name varchar(20)) insert into #test values('one') insert into #test values('two') insert into #test values('three') insert into #test values('four') insert into #test values('five') insert into #test values('six') […] SQL Server 2019 - Administration [Move mdf and ldf on separate disks]( - Hello Forum, Inside my database server I have 3 harddisks. Sadly there is no more free space to mount additional disks. So I set up: * Disk 1: OS, SQL-Server executables incl. master, model, msdb and tempdb * Disk 2: mdf for User DB * Disk 3: ldf for User DB For testing I installed […] [How to integrate TFS into SSMS?]( - Hi all, In our old version of SQL Server and its SSMS we used to have Team Foundation Server integrated into SSMS. The setup was done through Tools/Options/Source Control/Plug-in Selection. But now we are migrating to SQL Server 2019 and new SSMS is missing this option. Or I am missing something? Thanks [Switching from Server/CAL to Core license. Do we need the core install?]( - We are changing our licensing model and with 2019 there are different installs for Core or Server/Cal I asked the MS rep if this is simply a key change or does it require a new install. I couldn't get a concrete answer out of him. Seems like as long as we pay for the core […] [Error when attempting to do initial backup for log shipping]( - We have several databases that we log ship from our primary production SQL instance to a reporting instance. We've been doing this for years now and it's been working almost flawlessly most of the time. However, we've come across an issue. Over the weekend, we had a power outage in our DEV environment. The host […] SQL Server 2019 - Development [Clustered vs Nonclustered Columnstore Update Performance]( - Hi Everyone, I'm experiencing horrendous update performance with Clustered Columnstore Indexes (CCI). Whilst our current Nonclustered Columnstore Index (NCCI) setup is not going to win any update-performance awards, it works sufficiently well. In my test setup, I can update 1 mil rows in about 90 seconds using NCCIs. Inserting 1 mil rows (both for CCI […] [Incremental/Delta Import into SQL]( - Hi Community, I need a solution for a problem. I have excel files which needs to be imported into a SQL Database. I tried the below method using SSMS. When SSMS is connected to the chosen instance of SQL Server, right-click on the desired database and navigate to Tasks > Import data option from the Tasks submenu: The problem is the […] [Import data from PostgreSQL to MS SQL Server]( - I am trying to import data from the PostgreSQL into the MS SQL Server database, using SQL Server Import and Export Wizard. During the column mapping I get the following error: The column attribute "COLUMN_SIZE" is not valid. The column attribute "DECIMAL_DIGITS" is not valid. The column attribute "COLUMN_SIZE" is not valid. I modified the […] [Comparing two sets of value sets]( - I have two columns that I need to check for a given set of values: SELECT * FROM test t WHERE t.col_a IN ('a','b','d','g','t','s') OR t.col_b IN ('a','b','d','g','t','s') What I'm wondering is if there is a shorthand method to determine if either col_a or col_b have a value in the set without repeating the set […] SSRS 2016 [SSRS MS Report Builder: How to repeat all item labels]( - I am working on creating a report like below where I would like there to be no empty spaces in rows - so a employee number should show on every row - even if it is repeating the same information. Here is my design view, I have not grouped the report by anything but still […] Integration Services [SSIS not calling powershell script]( - I have an SSIS package that I've had to alter to call a powershell script to do SFTP. We have other SSIS packages that do the exact same thing and they work fine. But for some reason, when trying to run this package in the VS 2010 shell on the Windows 2012 R2 server, 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -