This posting will show you how to create an SSIS package using the legacy Package Deployment Model in Visual Studio 2017.
Please be sure before you do anything that you have SQL Server Data Tools for Visual Studio 2017 installed. You can find the SSDT download link below. Install it within the instance of your Visual Studio instead of creating a new one. With SSDT, you will be able to design and deploy any integration services package with the same ease as you would develop an application in Visual Studio.
- From the top menu, go to File, and select New and Project.
- Under New Project window, select Business Intelligence, Integration Services and Integration Services Project.Enter the name of your project (e.g., SSIS-ITNota) and select a folder where you want to save the project (e.g., C:\Users\ITNota) and click OK.
- As we are using a package deployment model, we need to convert the project before we’re doing anything else. Under Solution Explorer, right-click on the project and click on Convert to Package Deployment Model.
- You will see a dialog box with the message below. Just click OK to proceed.
This will change the project to use the package deployment model. You cannot perform this conversion if you are using any features unique to the project deployment model, such as parameters. Before conversion, all packages in this project will be checked for compatibility with the package deployment model. This may take several minutes if the project contains many packages. Click OK to proceed.
If you do the conversion in the beginning of your project (recommended), you should see your project and package passed the conversion. Click OK to complete the conversion.
You should see that after the project’s name there’s an extra note that indicates it’s a package deployment model after the conversion is complete.
- Here are the highlights of the differences between Project Deployment Model and Package Deployment Model side-by-side.
- Rename Packages.dtsx to something more meaningful for your project.
This should be enough to get you started with creating an SSIS package with Visual Studio 2017.
If you need to deploy your SSIS package to a different version of SQL Server, make sure you set the TargetServerVersion to the SQL Server version on your host server.
And check this post to avoid one of the most common mistake that developers make when deploying the package to production.
For a more elaborate explanation, Microsoft has designed a great Integration Services tutorials on how to create an ETL package and deploy it, which you can find at the bottom of this post.
How to Build SSIS Package for Different SQL Server Version with Visual Studio 2017 and SSDT
How to Add Custom Logging to SSIS Package
Integration Services Tutorials
Create a Project and Basic Package with SSIS
Deploy Integration Services (SSIS) Projects and Packages
Legacy Package Deployment (SSIS)
Gotcha in Executing SSIS from DTExec Command Line
How to Fix Failed to Decrypt Protected XML Node Error in SSIS