SQL Server Integration Service (SSIS) provides an convenient and unified way to read data from different sources (extract), perform aggregations and transformation (transform), and then integrate data (load) for data warehousing and analytics purpose. When you need to process large amount of data (GBs or TBs), SSIS becomes the ideal approach for such workload.

One example usage is to migrate one database to another database with different schema on a different server. There are many other ways to do it, such as:

• Console Application
• PowerShell
• SQL Server command line tool — SqlPackage.exe
• SQL Server Management Studio (SSMS)— Generate Scripts with data

The downside of the above approaches can be error prone in terms of error handling, user friendly, or not being able to handle large amount of data. For example, Generate Scripts in SSMS will not work when the database size is larger than a few Gigabytes.

In this article, I will discuss how this can be done using Visual Studio 2019. You can also just clone the GitHub project and use it as your SSIS starter project. Here is the GitHub link.

Prerequisites

• Visual Studio 2019 already installed. Note Visual Studio 2017 works slightly different regarding SSIS and this article may not work exactly for Visual Studio 2017.
• SQL Server already installed

Preparation Part 1 — Install SQL Server Data Tools in Visual Studio

Microsoft has documentation on the installation process as well, but all you need is to launch Visual Studio Installer and install “Data storage and processing” toolsets in the Other Toolsets section.

#data-engineering #sql-server #data-science #visual-studio #etl