Updated: Sep 14, 2020
In this tutorial, you will learn how to create SSIS (SQL Server Integration Services) package in visual studio 2017 step by step. For this you need to install SQL Server Data Tools (SSDT) on your machine. SSDT (Business Intelligence template) is basically used to create SSIS/SSRS/SSAS solutions.
For Visual Studio 2018+
Visual Studio 2018 or higher is included with SSDT BI template so you don't need to install SSDT separately. You just need to check box "Data Storage and Processing" in workload section while installing Visual Studio 2018 or later.
Visual Studio 2017 Installation
Download older version of Visual Studio from this link. You need to create Microsoft account (if you don't have one) and have free Visual Studio subscription.
Below is the product description which you need to download.
Download latest "Community" version (15.9 and not 15.0) in order to install SSDT BI as highlighted below. VS installation might take 20 to 45 mins depending upon your system configuration.
If you already have VS 2017 (version 15.0) then you have to upgrade VS 2017 to latest version. You can go to Visual Studio Installed version and check for available updates.
SSDT BI Installation
You can download SSDT (version 15.9.1) from this link. This link might change with new versions coming in future, in that case you can simply Google "SSDT release 15.9" and visit Microsoft VS 2017 SSDT installation page.
Check all the services (SSAS/SSIS/SSRS) as shown below and select Visual Studio Community 2017 from the drop down list.
Click Install. Download and installation process will take around 30 minutes depending upon your system configuration.
Restart your computer once installation is done.
Once installation is done, open Visual Studio 2017 and go to menu option File --> New Project.
Look up for Business Intelligence, if you can find Integration Services, Analysis Services and Reporting Services on left side of panel, then installation is fine.
Create SSIS package
In order to create SSIS basic package, you need to
Create a project for the package.
Add a control flow and data flow to the package.
Add components to the data flow.
What is SSIS?
SSIS is an ETL tool for data warehousing that comes with Microsoft SQL Server. There is no extra cost for SSIS services. It lets you setup automated data load or extract processes to and/or from your SQL Server.
SSIS stands for SQL Server Integration Services and ETL stands for Extract-Transform-Load. It is comparable to other ETL tools like Informatica and IBM Datastage etc.
What does it do?
SSIS provides you platform referred as SSDT to develop ETL solution which could be the combination of one or more packages.
Solutions are saved with sln extension and packages are basically XML files saved with dtsx extension.
Packages are deployed in SQL Server msdb database called as SSISDB and managed in Integration Services Catalog in SSMS (SQL Server Management Studio).
1. Go to File => New => Project, name your project. Click OK.
It will open SSIS designer. On left hand panel you will see SSIS toolbox with all the tasks and at the center you will see various tabs to switch between control flow, data flow, parameters, event handler and package explorer. On right panel you will see solution explorer where you can find connection manager.
Now, in order to create a SSIS package you need at-least one control flow and a data flow task. Data flow task is simply a task which is used to Extract, Load and Transform the data and control flow is like the logical unit which controls the execution of tasks, like