Floating Along

Today's submitter John F. was migrating data from a Microsoft platform to a Microsoft platform, using Microsoft tools. Absolutely nothing could go wrong, right? Right? A few years ago, I was working on a migration. We had sold part of our business, and so we had to extract a whole bunch of customer documents and metadata to provide to the buyer. The documents were stored in SharePoint on-premises, so the first step was extracting the metadata and storing it in a SQL Server database. A colleague had used Microsoft's ETL tool SSIS to get the process started, and it generated a database schema. But after taking over, I wanted to change to PowerShell for greater control. For speed reasons, I decided to use System.Data.SqlClient.SqlBulkCopy , and getting that going required making sure my PowerShell script had all the correct data types. One of our fields was the customer number. Customer numbers were up to 10 digits, but the first two were usually 0. Now, I prefer storing customer numbers as text, but someone in the distant past thought, This is a number, and SharePoint has a Number field, so I will use that. Under the hood, Number fields in SharePoint are actually Doubles . Using a Double to store something exact like a customer number is not really ideal, but double-precision is absolutely enough to represent 10 digit numbers accurately. So what went wrong? Well, remember we used SSIS to create the original table schema in SQL Server. I then used this table schema to write my script. But it turns out that in SQL Server world, the double-precision type is called float . If you want single-precision, you have to say float(24) . I didn't know this, and so when I saw the SQL Server column as a float , I entered float as the corresponding .Net type in my script. Oops. So numbers came out of SharePoint as double . They were then converted to float before being inserted into SQL Server. Almost all records were fine, but large customer numbers had their last few digits changed. Testers didn't notice, but fortunately someone picked it up in the full load. We had to generate a list of changed numbers to patch the data after the fact. [Advertisement] Keep all your packages and Docker containers in one place, scan for vulnerabilities, and control who can access different feeds. ProGet installs in minutes and has a powerful free version with a lot of great features that you can upgrade when ready. Learn more.