I had the extreme pleasure recently to extract some data out of an Oracle database. I wrote the source query in Toad, where it executed successfully. I created my SSIS package, added the dataflow and an OLE DB Source component. I copy pasted the query into the editor and tested it using the preview. Which […]
Category: SQL Server
Unable to validate source query in Tabular Editor
When you have a very large fact table in Analysis Services Tabular, you most likely have it partitioned. Suppose you are making some changes to the underlying source of the table and you hit “refresh table metadata” to add some new columns for example. What happens behind the scenes is that Tabular Editor will run […]
Upcoming Webinar- Introduction to SSIS
At February 25th March the 4th 2021 I’ll be giving a webinar on the MSSQLTips.com website. The topic is Integration Services. This webinar is meant as an introduction for beginners. The abstract: For years, Microsoft SQL Server Integration Services (SSIS) has been the tool in the data platform stack to integrate, move and transform your […]
Migrating SSIS to Azure – an Overview
For quite some time now, there’s been the possibility to lift-and-shift your on-premises SSIS project to Azure Data Factory. There, they run in an Integration Runtime, a cluster of virtual machines that will execute your SSIS packages. In the beginning, you only had the option to use the project deployment model and host your SSIS […]
Publish Database Fails – Script File could not be found
A small blog post for anyone who stumbles across this issue (and for my future self, should I forget the solution). I was working on some tables in SSDT and I tried to publish changes to the database on my local machine. I got a build error because I created a clustered index on the […]
Exporting Environment Variables out of the SSIS Catalog
Sometimes when you’ve created a SSIS catalog, along with folders, environments and environment variables, you want this content on another server. Unfortunately, you can only script out these objects in SSMS at the moment you’re creating them (thus right before you clikc ‘OK’ to confirm). Once the objects are created, you can no longer script […]
Converting a Datetime to UTC
I was in a need of converting some datetime values (of which I know the actual timezone) to UTC dates. A quick Google search showed me that most results on the first page were simply wrong. Most of them used this trick: Seems nifty, but they forgot about daylight savings time. If I run the […]
Optimize for Unknown for Inline Table-Valued Functions
I had a curious performance issue today. An inline table-valued function (iTVF) was performing poorly for some parameter sets, and quite fast for other parameter values. In short, this one is fast: This one is slow: Turns out SQL Server used a plan with a hash join in the fast query, and a nested loop […]
Skip Validation of SSIS Packages
There’s been an update of the SSIS extension for Visual Studio 2019. There are some bug fixes and deployments are blocked to Azure-SSIS if the target server version is not SQL Server 2017, but the real killer feature is the ability to now skip validation of an SSIS package when you open it. This might […]
Speaking at DataMinds Connect 2020
I’m delighted to announce I’ll be speaking again at DataMinds Connect this year, one of the greatest and best Microsoft Data Platform conferences on the European mainland. Due to the pandemic, this year edition is virtual, but this also means: more sessions! And a deep-dive track! And the event itself is free! The agenda is […]