This post can help you to understand the “Designing a simple SSIS package using SQL Server 2016” and SSIS Project Deployment Model in SQL Server 2016. Project Deployment Model is introduced in SQL Server 2012 and provides more flexibility in deploying, monitoring and maintaining SSIS packages. Here in this post we’ll take a simple example and see how to deploy SSIS package and use a single SSIS package for multiple environments. For implementation we used:
- SQL Server Data Tools 2015
- SQL Server 2016 Database
- SQL Server Management Studio 2017
ETL Requirement:
We are going to design and deploy a SSIS package for a simple ETL requirement. We have a customer table and we’ll get the customer data feed as a flat file on daily basis. We just need to design a SSIS package that should fetch extract data from flat file and insert into customer table by following the below given conditions. To simplify the requirement this is not an incremental load instead we get only the new customer information. Here are the rules:
- SSIS package should be deployed on dedicated SSIS SQL Server 2016 instance
- We should be able to change the Flat File Path, SQL Server Instance Name, User Name, Password, and Database Name. It means we should be able to provide these values at runtime.
- The same package hosted on dedicated instance should be used for all 3 environments (Development, QA and Production)
- Customer table has to be truncated before loading data
ETL Solution:
We are going to design a simple ETL package and deploy using Project Deployment model. Here are the details:
| Source | Flat File – Customer Information | ||||
| Destination | SQL Server – 3 Environments | ||||
| Environment | Instance/ Server | Database | Table | Login | Password |
| Development | 127.0.0.1,1435 | ETL2017_DEV | Customer | ETL_PreProd | XXXXXXX |
| QA | 127.0.0.1,1435 | ETL2017_QA | Customer | ETL_PreProd | XXXXXXX |
| Production | 127.0.0.1,1436 | ETL2017_PROD | Customer | ETL_Prod | XXXXXXX |
Creating Integration Services Catalogs:
Before SQL Server 2012 we used to store SSIS packages either in MSDB or in physical file and parameters and configurations in XML file or in SQL Server table. But from 2012 game is changed and now we have “Integration Service Catalogs” introduced. By default there are no catalogues created and we have to create a new catalog and it automatically creates “SSISDB”. All SSIS packages and configurations, parameters are stored in SSISDB. Here is a good article that explains how to create a SSIS catalog. I have created SSIS catalog and added a new folder called XYZ_Product
