Create SSIS package in Visual Studio 2017
Updated: Feb 23
In this tutorial, you will learn how to create an 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 used to create SSIS/SSRS/SSAS solutions.
For Visual Studio 2018+
Visual Studio 2018 or higher is included with the SSDT BI template so you don't need to install SSDT separately. You just need to check the box "Data Storage and Processing" in the workload section while installing Visual Studio 2018 or later.
Visual Studio 2017 Installation
Download the older version of Visual Studio from this link. Scroll down on that link to see the older versions. You need to create a Microsoft account (if you don't have one) and have a free Visual Studio subscription. Below is the product description which you need to download.
Download the latest "Community" version (15.9 and not 15.0) to install SSDT BI as highlighted below. VS installation might take 20 to 45 mins depending on your system configuration.
If you already have VS 2017 (version 15.0) then you have to upgrade VS 2017 to the latest version. You can go to the 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 the future, in that case, you can simply Google "SSDT release 15.9" and visit the Microsoft VS 2017 SSDT installation page.
Related: How to file H4 EAD?
Check all the services (SSAS/SSIS/SSRS) as shown below and select Visual Studio Community 2017 from the drop-down list.
Click Install. The download and installation process will take around 30 minutes depending on your system configuration.
Restart your computer once the installation is completed.
Installation Check
Once installation is done, open Visual Studio 2017 and go to the menu option File > New Project.
Look up 🔎 Business Intelligence, if you can find Integration Services, Analysis Services, and Reporting Services on the left side of the panel, then the installation is fine.
Create SSIS package
To create the 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 set up 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 to as SSDT to develop ETL solutions which could be the combination of one or more packages.
Solutions are saved with the .sln extension and packages are XML files saved with the .dtsx extension.
Packages are deployed in SQL Server MSDB database called SSISDB and managed in the Integration Services Catalog in SSMS (SQL Server Management Studio).
Creating Package
1. Go to File > New > Project, and name your project. Click OK.
It will open SSIS designer. On the left-hand panel, you will see the 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 the right panel, you will see Solution Explorer where you can find the connection manager.
Now, to create an SSIS package you need at least one control flow and a data flow task. A data flow task is simply a task that is used to Extract, Load, and Transform the data, and control flow is like the logical unit that controls the execution of tasks, like the flow in which tasks will execute.
2. Drag and drop the data flow task from the SSIS toolbox to the central panel (control flow tab) like the following:
3. You can double-click on Data Flow Task to rename it. I am keeping it as the default "Data Flow Task". Now right-click on Data Flow Task > Edit, or you can simply select the "Data Flow Task" and click on the "Data Flow" tab, it will open a 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 the SSIS toolbox to the designer space as shown below.
5. Select OLE DB Source and drag and drop the blue/green arrow to connect the Data Conversion task. Similarly, drag and drop/green blue arrow (not the red one) from the Data conversion task to OLE DB Destination.
6. Now, you need to create an OLE DB connection for the source and target. For this go to the Solution Explorer panel on the right-hand side > Connection Manager> New Connection Manager. Select OLE DB and click ADD.
If you have already created an OLE DB data connection earlier on your machine it will show up here, otherwise, you can click on NEW and create a new one.
Just enter your database name and test the connection. I assume you have the AdventureWorks database running on your machine if not please refer to 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 the AdventureWorks 2014 database.
7. Now go back to the Data Flow screen and right-click on OLE DB Source task > Edit. Choose a sample table from the drop-down list, [Production].[Product].
Now go to the Columns tab, remove selected columns, and select these five columns - Name, ListPrice, Size, Weight, and SellStartDate and click OK. It's just for example purposes.
8. Now go to Data Flow and right-click on Data Conversion task > Edit. Select the SellStartDate column and change its data type from [DT_DBTIMESTAMP] to [DT_DBDATE], keep the alias name the 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] (
[Name] nvarchar(50),
[ListPrice] money,
[Size] nvarchar(5),
[Weight] numeric(8,2),
[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] (
[Name] nvarchar(50),
[ListPrice] money,
[Size] nvarchar(5),
[Weight] numeric(8,2),
[Data Conversion.SellStartDate] date
)
Mappings should look like this, just click OK.
10. Now right-click on the blue/green arrow between the Data conversion task and the OLE DB Destination task and enable the data viewer. This is not a mandatory step but just to see the data preview after the data conversion.
11. Now hit the START button on top of your screen. This will start the package.
You can see SellStartDate has only the date after conversion (no time field), all the tasks are green ticked which means they ran successfully and the number of rows is 1,008 processed. You can stop the flow or restart again from the 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 examples simple and informative.
I hope you enjoyed the post. If you have any questions please mention them in the comments section below. Thank you.
