Project Deployment Model in SSIS

Project Deployment Model in SSIS

This post helps you to understand the “Project deployment model in SSIS”. We have already created a SSIS package and environment is ready to deploy. Here are the ETL requirement and package details. We are going to deploy the package into SQL Server 2016 instance and use the same package to run for QA, TEST and PROD environment.

Environment Setup

Sample ETL Package

Source – Flat Files

We have created three different folders and customer flat files for DEV, QA and PROD as below:

Project Deployment Model in SSIS

Continue reading

Posted in MSBI, SSIS | Tagged , , , , , | 6 Comments

Designing a simple SSIS package using SQL Server 2016

Designing a simple SSIS package using SQL Server 2016

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

Designing a simple SSIS package using SQL Server 2016

Continue reading

Posted in MSBI, SQL Development, SSIS | Tagged , , , , , , | 1 Comment

SQL DBA Interview Preparation

 SQL DBA Interview PreparationSQL DBA Interview Preparation

This post can help you in SQL DBA Interview Preparation. We blogged various pages and posts and SQL Server DBA Interview Preparation, this post is helpful to locate / link all the content that is useful for SQL Server DBA interview preparation. Please reports us if you find any broken links or pages:

SQL DBA Interview Preparation

SQL DBA Interview Preparation SQL DBA Interview Preparation SQL DBA Interview Preparation SQL DBA Interview Preparation SQL DBA Interview PreparationSQL DBA Interview PreparationSQL DBA Interview Preparation SQL DBA Interview Preparation SQL DBA Interview Preparation SQL DBA Interview Preparation SQL DBA Interview Preparation SQL DBA Interview Preparation

SQL DBA Interview Preparation SQL DBA Interview Preparation SQL DBA Interview Preparation SQL DBA Interview Preparation SQL DBA Interview Preparation SQL DBA Interview Preparation SQL DBA Interview Preparation SQL DBA Interview Preparation SQL DBA Interview Preparation SQL DBA Interview Preparation SQL DBA Interview Preparation SQL DBA Interview Preparation

SQL DBA Interview PreparationSQL DBA Interview PreparationSQL DBA Interview PreparationSQL DBA Interview PreparationSQL DBA Interview Preparation

SQL DBA Interview Preparation

SQL DBA Interview Preparation SQL DBA Interview Preparation SQL DBA Interview Preparation SQL DBA Interview Preparation SQL DBA Interview Preparation

 

SQL DBA Interview Preparation

SQL DBA Interview Preparation SQL DBA Interview Preparation SQL DBA Interview Preparation SQL DBA Interview Preparation SQL DBA Interview Preparation SQL DBA Interview Preparation SQL DBA Interview Preparation SQL DBA Interview Preparation

 Summary:

This post is intended to present all available articles, interview preparation stuff and interview experiences in one place which can help blog readers to quickly go through the content.  If you feel anything missing or breaking please feel free to comment on below comments section. Thanks in advance.

1000+ Professional SQL Server Interview Questions and Answers 

Posted in Interview Q&A, SQL Development, SQL Server DBA | Tagged , , , , , , , , , , , , , | 4 Comments