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 the flow in which tasks will execute.
2. Drag and drop drop data flow task from SSIS toolbox to central panel (control flow tab) like below,
3. You can double click on Data Flow Task to rename it. I am keeping it as default "Data Flow Task". Now right click on Data Flow Task => Edit, or you can simply select the "Data Flow Task" and click on "Data Flow" tab, it will open screen where you can edit your "Data Flow Task".
4. Now drag and drop OLE DB Source, OLE DB Destination and Data Conversion task from SSIS toolbox to designer space as shown below,
5. Select OLE DB Source and drag and drop the blue/green arrow to connect Data Conversion task. And similarly drag and drop/green blue arrow (not the red one) from Data conversion task to OLE DB Destination. Like this,
6. Now, you need to create OLE DB connection for source and target. For this go to Solution explorer panel on right hand side => Connection manager => New connection manager. Select OLE DB and click ADD.
If you have already created OLE DB data connection earlier on your machine it will show up here, otherwise you can click on NEW and create new one.
Just enter your database name and test connection. I assume you have AdventureWorks database running on your machine if not please refer this post.
I have already installed SQL Server 2014 and SQL Express so you can see 2 instances of SQL Server service running on my machine.
Now, for example I have chosen AdventureWorks2014 database.
7. Now go back to Data Flow screen and right click on OLE DB Source task => Edit. Choose a sample table from drop down list, [Production].[Product].
Now go to Columns tab, remove selected columns and select these five columns - Name, ListPrice, Size, Weight and SellStartDate and click OK. It's just for example purpose.
8. Now go to Data Flow and right click on Data Conversion task => Edit. Select SellStartDate column and change its data type from [DT_DBTIMESTAMP] to [DT_DBDATE], keep alias name same and click OK. Just a minor datatype conversion to showcase this example.
9. Now, right click on OLE DB destination editor => Edit => New
SSIS by default creates the "create table" statement for you with input columns.
CREATE TABLE [OLE DB Destination] (
[OLE DB Source.SellStartDate] datetime,
[Data Conversion.SellStartDate] date
Edit the table name and remove [OLE DB Source.SellStartDate] and hit OK,
CREATE TABLE [OLE DB Destination_Products] (
[Data Conversion.SellStartDate] date
Mappings should look like this, just click OK.
10. Now right click on the blue/green arrow between Data conversion task and OLE DB Destination task and enable data viewer. This is not a mandatory step but just to see data preview after data conversion.
11. Now hit START button on top of your screen. This will start the package.
You can see SellStartDate has only date after conversion (no time field), all the tasks are green ticked that means they ran successfully and number of rows 1,008 processed. You can stop the flow or restart again from buttons highlighted on top of the screen.
That's it. This package creation example was showcased by Microsoft itself. I haven't modified anything to keep example simple and informative.
I hope you enjoyed the post. If you have any question please mention in the comments section below. Thank you.