The title of this blog post is a bit misleading, because what we actually want to do is to install the SSIS development functionality in Visual Studio 2022. SSAS and SSRS have been available for some time now in VS 2022, but the extension for SSIS has only just been released in preview.

For those of you that have been working on an older version of SSIS/SQL Server (2014-2016, something like that), the BI components (SSIS/SSAS/SSRS) are now extensions in Visual Studio. SQL Server Data Tools (SSDT) is no longer available as a separate download.

So you’ll need a full-blown version of Visual Studio (make sure you only install the workflows you actually need). The good news is that you can use the community edition of VS if you’re just using it for BI development. Anyway, install VS 2022 on your machine and download the SSIS extension here.

After the download (approx. 450MB), you can start the setup and it’s a real “next-next-finish” setup, as there’s nothing to configure.

You only have to choose into which installation of VS you want to install the extension, but normally there aren’t that many options to choose from.

Click Install and wait for a few minutes.

It’s possible you’ll have to restart the machine after the setup finishes:

I could open an existing project (saved as a SSIS 2019 project) without issue. However, when I changed the TargetServerVersion property of the project, I noticed there’s only 2019 and 2022 available.

This might be because the extension is still in preview, but I’m not sure. Another issue is that when I tried to deploy the project, I got build errors saying the protection level between the project and the package were not the same.

However, they are the same. Both the project and the packages are set to DontSaveSensitive. Opening a package, clicking on the protection level property and saving the package fixes the issue for that package. But you’d have to do this for every package in your project. Normally you can use the batch update property feature of BIDSHelper, as explained in this blog post by Cathrine Wilhelmsen. Unfortunately, this extension has not yet been updated to VS 2022.

Creating a new project is straight forward. Either choose the SSIS project type, or the SSIS Azure-Enabled project type. This last type gives you the option to debug your package on your Azure-SSIS IR in Azure Data Factory, instead of your local machine. For more info, check out the article Azure-Enabled Integration Services Projects in Visual Studio.

Since I mentioned the Azure-SSIS IR, there’s another issue. When you try to deploy to a catalog in Azure, you get the following error message:

But as I pointed out earlier, you cannot set the project to 2017. Whoops. If you’re still using VS 2019 to develop your Azure SSIS project, you might want to hold out on upgrading.

On the 5th of October 2021 I’ll be giving a webinar about improving data quality of contact data (names, addresses, emails etc) using the Melissa Data Tools. I’ll be doing half an hour of technical demos, while Jeremy of MSSQLTips.com will take for another half our of the business value of the Melissa software.

Data is the new oil, but just like oil, data needs to be refined as well. Data quality issues can be a serious burden on your business intelligence and data warehouse projects.

Unfortunately, SSIS doesn’t have many options out-of-the-box to deal with these issues, leaving you to program all the logic yourself.

In this webinar, we’ll show you how you can use the Data Quality components from Melissa in SSIS to drastically improve the quality of your data in an efficient manner.

Join us for this demo centric session to learn about:
– Drag and drop Data Quality features in SSIS
– Data Profiling to score and enrich your data
– Data Validation including contact data, emails, etc.
– Geocoding Information
– and more

You can register here.

On July the 22th at 9PM CET I will give the second part of the “Introduction to SSIS” webinar series on the MSSQLTips.com website. If you’ve never worked with SSIS before, it helps if you’ve seen part 1. The abstract:

In this webinar, we build upon the Getting Started with SSIS webinar. You already know how to read data from flat files and Excel, and you already know how to write to SQL Server. This time, we go one step further and introduce you to more advanced development concepts of SSIS.

We will cover these topics and more:
* using variables, parameters and expressions to make your packages more dynamic
* how to loop over items
* design pattern to load a slowly changing dimension of type 2
* expanding SSIS with .NET script tasks
* how to debug your package

In this webinar you’ll learn how to take your SSIS packages to the next level. We assume you have basic knowledge of developing SSIS packages in Visual Studio.

You can register here.

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 failed. Always a good start. The error message was useful, as always:

However, if I went to the columns tab of the source editor, all columns (and their metadata) were there. Hmmm, so SSIS could at least parse the query. Which, again, works just fine when executed directly on the Oracle database. When running the SSIS package, the error message changed to the following:

Source: “OraOLEDB” Hresult: 0x80040E14 Description: “ORA-00907: missing right parenthesis”.

Plot twist: there’s nothing wrong with the parenthesizes in the query. At this point I was ready to set my laptop on fire, but instead I decided to soldier on. After searching online and trying out some theories, it turns out a comment in the WHERE clause was the culprit. Something like this:

... 
WHERE 1 = 1
--AND mydatecolumn = '01jan2021'
...

For whatever reason, SSIS has issues parsing this. The explanation is that SSIS probably puts everything on one line, thus effectively commenting out the rest of the query. I removed the comment and lo and behold, the dataflow ran without issues. Another solution would be to use block comments using /* */ instead of line comments.

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 data. This ETL tool allows you to build data pipelines, extract data from various sources, transform it on-the-fly and write to many destinations. In this webinar, we give you a hands-on introduction to SSIS.

What to expect:

* how to load data from a flat file, an Excel file or a database
* how to transform data. E.g. add a calculation, do a lookup against a reference data set, filter data and so on
* how to write data efficiently to a destination, such as a SQL Server database

We will use demos to demonstrate how you can load data into a database and by the end of the webinar, you’ll have the knowledge needed to start your first SSIS project.

You can register here.

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 catalog in either an Azure SQL DB, or in a SQL Server Managed Instance.

But over time, features were added and now the package deployment model has been supported for quite some time as well. Even more, the “legacy SSIS package store” is also supported. For those who still remember this, it’s the SSIS service where you can log into with SSMS and see which packages are stored in the service (either the file system or the MSDB database) and which are currently running.

The following Microsoft blog post gives a good overview of the journey that was made, and it’s a definite must-read for anyone who wishes to migrate their SSIS solution: Blast to The Future: Accelerating Legacy SSIS Migrations into Azure Data Factory.

Something that surprised me in this blog post was this:

“…our on-premises telemetry shows that SSIS instances with Package Deployment Model continue to outnumber those with Project Deployment Model by two to one”

Microsoft (Sandy Winarko, Product Manager)

This means a lot of customers still use the package deployment model. Many presentations at conferences about SSIS (even mine) are always geared towards the project deployment model. This is something I will need to take into account next time I present about SSIS.

Anyway, I have done some fair amount of writing on the Azure-SSIS IR:

• Configure an Azure SQL Server Integration Services Integration Runtime
• Automate the Azure-SSIS Integration Runtime Start-up and Shutdown – Part 1
• Azure-SSIS Integration Runtime Start-up and Shutdown with Webhooks – Part 2
• Connect to On-premises Data in Azure Data Factory with the Self-hosted Integration Runtime – Part 1
• Connect to On-premises Data in Azure Data Factory with the Self-hosted Integration Runtime – Part 2
• Customized Setup for the Azure-SSIS Integration Runtime
• Execute SSIS Package in Azure with DTEXEC Utility
• Executing Integration Services Packages in the Azure-SSIS Integration Runtime
• Parallel package execution in Azure-SSIS Runtime
• SSIS Catalog Maintenance in the Azure Cloud
• Migrate a Package Deployment Integration Services Project to Azure
• Using Files Stored in Azure File Services with Integration Services – Part 1
• Using Files Stored in Azure File Services with SQL Server Integration Services – Part 2
• Azure-Enabled Integration Services Projects in Visual Studio

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 them out using the GUI.

Luckily, there’s a whole slew of stored procedures and views in the catalog that allow us to extract information and use that information to re-create the objects. Here are two scripts that I found useful.

The first one extracts all environment variables out of an environment (I currently take only the data type String into account to put quotes around, but this might be expanded):

SELECT
    v.[name]
   ,v.[type]
   ,v.[value]
   ,Script = 'EXEC [SSISDB].[catalog].[create_environment_variable]
       @variable_name=''' + CONVERT(NVARCHAR(250), v.name) + '''
      ,@sensitive=0
      ,@description=''''
      ,@environment_name=''myenv''
      ,@folder_name=''myfolder''
      ,@value='
             + IIF(v.type = 'String'
                ,N'N''' + CONVERT(NVARCHAR(500), v.value) + ''''
                ,CONVERT(NVARCHAR(500), v.value)
                )
             + '
      ,@data_type=N''' + v.type + ''';
'
FROM    [SSISDB].[catalog].[environments]          e
JOIN [SSISDB].[catalog].[folders]               f ON f.[folder_id]      = e.[folder_id]
JOIN [SSISDB].[catalog].[environment_variables] v ON e.[environment_id] = v.[environment_id]
WHERE   f.[name] = N'myfolder'
    AND e.[name] = N'myenv';

It also creates a script for each environment variable. Just copy paste the column contents and execute it on the server where you wish to create the variables. Once the environment variables are created, you need to create a reference between the environment and the SSIS project. Then, you need to link each parameter with its corresponding environment variable. The following script extracts the info and again creates a SQL statement to create this reference. It assumes though the parameters and the environment variables have the same name (which seems kind of a best practice, right?):

SELECT -- v.[name], v.[type], v.[value]
    Script = 'EXEC [SSISDB].[catalog].[set_object_parameter_value]
        @object_type=20
      , @parameter_name=N''' + CONVERT(NVARCHAR(500), v.name) + '''
      , @object_name=N''SSIS''
      , @folder_name=N''myfolder''
      , @project_name=''SSIS''
      , @value_type=R
      , @parameter_value=N''' + CONVERT(NVARCHAR(500), v.name) + ''';
'
FROM [SSISDB].[catalog].[environments]          e
JOIN [SSISDB].[catalog].[folders]               f ON f.[folder_id]      = e.[folder_id]
JOIN [SSISDB].[catalog].[environment_variables] v ON e.[environment_id] = v.[environment_id]
WHERE   f.[name] = N'myfolder'
    AND e.[name] = N'myenv';

Creating a folder and the environment are easy enough to do manually. If you want to script those out as well, this article can get you started.

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 not seem like much, but I had a project where there were many connections to different source databases. Sometimes, a couple of them were offline. Every time I opened up a package, the connections were validated. Because some of them were offline, I had to wait for the time-out and then the connection manager would be put offline. Opening up a package could easily take a minute or more. With this feature, packages now open up in a few seconds. It has already saved me a lot of frustration

So how does it work? You install the latest version of the extension. You open up Visual Studio, you go to Tools > Options. Go to the section Business Intelligence Designers, then Integration Services Designers and then General.

You’ll find an option under Skip package validation. It’s deselected by default.

Just enable it and enjoy the ride!

p.s.: the official blog post says it should also work with SSDT for VS2017 15.9.6, but I haven’t tried it out yet.
p.p.s: this is something different than the DelayValidation property you can set on various object inside a package. This property delays validation of the object at runtime, until the object itself is actually used. Useful when you use expressions on those objects. The setting described in this blog post is a design-time feature.

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 very promising. Many many reasons to (virtually) attend the conference.

My session is about migration SSIS to Azure, as in lifting-and-shifting an entire SSIS project to the Azure-SSIS Integration Runtime in Azure Data Factory. If you have existing SSIS projects and you’re thinking about starting in Azure, this is the session for you!

Don’t forget to register. Did I already mention it’s free?

UPDATE: here’s an official blog post going in more technical detail on how they achieved the improvements in startup time

If you want to run SSIS packages in Azure Data Factory, you need the Azure SSIS Integration Runtime (quite the mouthful), which is basically a cluster of virtual machines handling the packages like an SSIS scale-out cluster. You can read more about it in the article Configure an Azure SQL Server Integration Services Integration Runtime.

Previously, it took about 20-30 minutes to start the runtime, which was less than ideal. If you wanted to run your ETL multiple times a day, you’d lose quite some time, unless you keep the runtime running the entire time, which costs money.

Luckily, the team behind the IR made some changes and the runtime now starts in about 4-5 minutes. Quite the improvement! Now it’s easier to have multiple batches in a day and still save money. Normally you don’t have to do anything, the change is automatic, but I did recently upgrade the virtual machines of the runtime to a newer version:

The startup time depends on the size of the cluster and on any custom setup you have configured.