If you're looking for Interview Questions on SSIS for Experienced or Freshers, you are at the right place. There are a lot of opportunities from many …
SSIS packages can be stored on either the Windows file system or the SSIS catalog. For pre-2012 SQL Server instances and SSIS 2014 projects using the …
Auditing SSIS packages in the previous version of SQL Server is a pain. If you don’t have a third-party tool like BI xPress from Pragmatic Works, y…
As mentioned before, DTExecUI.exe is a 32-bit application. Therefore, whenever you execute a package from DTExecUI.exe, it will execute in 32-bit mo…
Now you have your packages deployed and are ready to set up a schedule to run them on a regular basis. The typical tool used is the SQL Server Agent. …
SSIS Package: The security of SSIS packages has changed because of the architecture changes in Integration Services. In the previous version of SSIS …
The SSIS command-line tools are still available, but you should be using the T-SQL functions to execute and manage your packages. However, DTExec and …
This section covers older configurations. You will need this information if you decide to run your packages with the package model and use configurati…
This is another section that applies only to the package deployment model. If you can, you should be changing your SSIS methods to take advantage of t…
Error rows have been briefly touched upon in several Topics, including The Data Flow Topic, Accessing Heterogeneous Data Topic and Understanding and T…
The SSIS development team has exposed a robust architecture to manage SSIS through managed code. The managed code in this case refers to the use of th…
You can deploy packages using the project deployment model or the package deployment model. The project deployment model, enables you to use the param…
What’s In This Topic? Using the SSIS catalog Choosing and using the different deployment models Using T-SQL with SSIS Managing security in SSIS …
SSIS includes two special tasks that enable you to query system information and monitor system events: the WMI Data Reader Task and the WMI Event Watc…
Logging is an important part of any data process because it gives administrators and developers insight into what transpired during a process, with th…
The Pipeline Component Methods Components are normally described as having two distinct phases: design time and runtime. The design-time phase refers…
Prior to SQL Server 2012, a package configuration was the optimal method to store configurable values that could be used in your package without alter…
The Components Now you can move on to actually building the components. These components are simple and demonstrate the most commonly used methods wh…
SQL SERVER DATA TOOLS The SQL Server Data Tools (SSDT) is where you’ll spend most of your time as an SSIS developer. It is where you create and deplo…
Editions of SQL Server The available features in SSIS and SQL Server vary according to what edition of SQL Server you’re using. Of course, the more h…
The SSIS Tools As with any Microsoft product, SQL Server ships with a myriad of wizards and tools to make your life easier and reduce your time to ma…
SSIS ARCHITECTURE Microsoft has truly established SSIS as a major player in the extraction, transformation, and loading (ETL) market. Not only is the…
SQL Server Integration Services (SSIS) is the anchor in a growing suite of products that make up the Microsoft SQL Server Business Intelligence (BI) p…
This SSIS tutorial gives you an overview and talks about the fundamentals of SSIS. SSIS Tutorial for Beginners In this SSIS tutorial, we will start …
Now you can practice what you have learned in Data Flow Topic, pulling together some of the transformations and connections to create a small ETL proc…
The Solution Explorer Window The Solution Explorer window is where you can find all your created SSIS packages, project connection managers, project …
Log providers are used to define the destination for the log information that is generated when a package executes. For instance, if you require a rec…
The SSIS Package Designer The SSIS Package Designer contains the design panes that you use to create an SSIS package. This tool contains all the item…
There are a few key performance counters to watch when you’re trying to monitor the performance of your SSIS package. These counters greatly help you …
Many programmers use breakpoints to debug programs, viewing the value of variables and following the flow of the logic as they step through the source…
SSIS Script Task SCRIPT TASK (.NET)The Script Task enables you to access the Microsoft Visual Studio Tools for Applications (VSTA) environment to wri…
Each task and container raises events as it runs, such as an OnError event, among several others that are discussed shortly. SSIS enables you to trap …
ANALYSIS SERVICES TASKS The Analysis Services tasks are provided in the SSIS environment to deal with generating and updating cubes and working with …
Data Flow Task The most important task in SSIS is the Data Flow Task. The SSIS Data Flow Task can be selected directly from the SSDT Toolbox, and the…
Data Preparation Tasks Before processing data from other systems, you sometimes have to first retrieve it or validate the content to determine your l…
RDBMS SERVER TASKS These tasks could also be considered data preparation tasks, as they are responsible for bringing data sources into the ETL proces…
Execute SQL Task The Execute SQL Task is one of the most widely used tasks in SSIS for interacting with an RDBMS Data Source. The Execute SQL Task is…
Precedence constraints are the green, red, and grey connectors in the Control Flow that link the tasks together and can be used to manage the workflow…
WORKFLOW TASKS So far, we’ve been focused on tasks that are occurring within the immediate realm of ETL processing. You’ve looked at tasks for creati…
SMO ADMINISTRATION TASKS SQL Server Management Objects The last section of this chapter is reserved for a set of tasks that are convenient for copyi…
The Data Flow In the previous post, you were introduced to the Control Flow tab through tasks. In this chapter, you’ll continue along those lines wit…
SOURCES A source in the SSIS Data Flow is where you specify the location of your source data. Most sources will point to a Connection Manager in SSIS…
Software Development Life Cycles Software Development Life Cycles (SDLCs) represent a systematic approach to each component of application developmen…
Destinations Inside the Data Flow, destinations accept the data from the Data Sources and from the transformations. The architecture can send the dat…
What’s In This Topic? 1. Understanding the different types of SDLCs2. Versioning and source control overview3. Working with Subversion (SVN)4. Workin…
Pipeline Performance Monitoring: Earlier in this Understanding and Tuning the Data Flow Engine Topic, you looked at the built-in pipeline logging fu…
SSIS Data Flow Design And Tuning Now that you know how the Data Flow engine works, it will be easier to understand Data Flow design principles and tu…
The SSIS Engine Before learning about buffers, asynchronous components, and execution trees, you might find it useful to consider this analogy — traf…
COMMON TRANSFORMATIONS Transformations or transforms are key components to the Data Flow that transform the data to the desired format as you move fr…
Error Outputs Error outputs can obviously be used to improve reliability, but they also have an important part to play in terms of scalability as wel…
Package Transaction This part of the Reliability and Scalability Topic describes how you can use transactions within your packages to handle data con…
The Lookup Transformation The Lookup Transformation in SSIS enables you to perform a similar relational inner and outer hash-joins. The main differen…
Restarting Packages Everyone has been there — one of your Data Transformation Services (DTS) packages failed overnight, and you now have to completel…
In SSIS Tasks, you read about tasks and how they interact in the Control Flow. Now we’re going to cover one of the special types of items in the Contr…
The language used to build expressions can be a bit disorienting at first. If you started out as a programmer, you will be comfortable switching betwe…
Date and Time Functions Date and time functions tend to cause confusion for new SSIS developers. In most instances, the different syntax is causing t…
ODBC stands for Open Database Connectivity and is a technology connection standard for passing data between systems that is widely used today for acce…
Flat files are one of the more common sources to work with because data in the flat files is easy to read and create by most RDBMS systems and ETL too…
Introduction Although XML is not a common source for large volumes of data, it is an integral technology standard in the realm of data. This section …
You use the conditional expression operator to build logical evaluation expressions in the format of an IF..THEN logical structure: Learn how to use SSIS, from…
String Functions Handling strings in SSIS expressions is different from dealing with string data in SQL Server. The previous section discussed some o…
Dealing with NULLs In SSIS, variables can’t be set to NULL. Instead, each variable data type maintains a default value in the absence of a value. For…
Boolean Expressions Boolean expressions evaluate to either true or false. In their simplest implementation, precedence constraints use Booleans expressions as …
Literals are hard coded information that you must provide when building expressions. SSIS expressions have three types of literals: numeric, string, a…
String Concatenation There are many uses for building strings within an expression. Strings are built to represent a SQL statement that can be executed against…
Because of SQL Server’s world-class reporting and business intelligence tools, more and more shops running Oracle rely on SQL Server for their reporti…
UNDERSTANDING DATA TYPES In SSIS, you must pay attention to data types, whether the data is coming from your Data Flow, is stored in variables, or is…
SSIS deals with Excel and Access data in a similar fashion because they use the same underlying provider technology for data access. For Microsoft Off…
SSIS includes multiple objects that can be used to create dynamic packages. Below Screen Shoot provides a graphical description of this model in SSIS.…
What’s In This Topic? Dealing with Excel and Access data Integrating with Oracle Working with XML files and web services Extracting from flat fi…
This section focuses on data loading. Many of the same techniques presented in the data extraction section apply here too, so the focus is on areas th…
To simplify the explanation of the Lookup Transformation’s operation in the next few sections, this section presents a typical ETL problem that is use…
Putting it all together is perhaps the easiest part of the ETL process because it involves simply using SSIS to coordinate the execution of the packag…
With the basic ETL out of the way, you will now jump into a more complex SSIS package that attempts to scrub data. You can start this scenario by down…
Processing SSAS objects in SSIS can be as easy as using the Analysis Services Processing Task. However, if your SSAS cubes require adding or processin…
Fact table loading is often simpler than dimension ETL, because a fact table usually involves just inserts and, occasionally, updates. When dealing wi…
WHAT’S IN THIS TOPIC ? Selecting your scripting language and getting started Adding assemblies to SSIS Script objects Understanding Script Task u…
Dimension transformation and loading is about tracking the current and sometime history of associated attributes in a dimension table. Below Screen sh…
Among the various applications of SQL Server Integration Services (SSIS), one of the more common is loading a data warehouse or data mart. SSIS provid…
USING THE SCRIPT TASK Now that you have a good overview of the scripting environment in SSIS, it’s time to dig into the Script Task and give it a spi…
SQL SERVER CHANGE DATA CAPTURE The advantage to the control table pattern you saw previously is it works across any database platform you may use. Th…
Introduction Even if a data warehouse solution starts off simple — using one or two sources — it can rapidly become more complex when the users begin…
INCREMENTAL LOAD So far, most of the data loading procedures that have been explained in this tutorial have done a full load or a truncate and load. …
Advanced Derived Column Use If you’ve used the data flow in SSIS for any amount of data transformation logic, you will no doubt have used the Derived…
Master Data Management Master data management (MDM) is the process an organization goes through to discover and define data with the ultimate goal of…
DQS CLEANSING Introduced in SQL Server 2012 was a component called Data Quality Services (DQS). This is not a feature of Integration Services, but it…
The two fuzzy transformations within SSIS, Fuzzy Lookup and Fuzzy Grouping, deal with associating data through data similarity, rather than exact data…
What’s In This Topic? Using the Derived Column Transformation for advanced data cleansing Applying the Fuzzy Lookup and Fuzzy Grouping transformations and u…
The Cache Connection Manager (CCM) and Cache Transform enable you to load the Lookup cache from any source. The Cache Connection Manager is the more c…
The Merge Join Transformation in SSIS enables you to perform an inner or outer join operation in a streaming fashion within the SSIS Data Flow. The Merge Join T…
You have now been all over the VSTA development environment and have been introduced to the different languages that move SSIS development into the ma…
The Script Component provides another area where programming logic can be applied in an SSIS package. This component, which can be used only in the Da…
Now that the key stages have been explained, you can examine each of them in detail. This guide makes very few assumptions about your current level of…
Building the Transformation Component In this section, you build the transformation that is going to take data from the upstream Source adapter. Afte…
You are no doubt already familiar with the term scaling out, and of course the concept can be applied to SSIS systems. Although there are no magical s…
One of the most exciting features of SSIS is the fact that you can use T-SQL to interact with your packages and projects natively (if you are using th…
SQL Server 2014 and SQL Server Integration Services (SSIS) integrate with source control products such as Subversion (SVN) and Visual Studio Team System. SVN is…
If you're looking for Interview Questions on SSIS for Experienced or Freshers, you are at the right place. There are a lot of opportunities from many …
SSIS packages can be stored on either the Windows file system or the SSIS catalog. For pre-2012 SQL Server instances and SSIS 2014 projects using the …
Auditing SSIS packages in the previous version of SQL Server is a pain. If you don’t have a third-party tool like BI xPress from Pragmatic Works, y…
As mentioned before, DTExecUI.exe is a 32-bit application. Therefore, whenever you execute a package from DTExecUI.exe, it will execute in 32-bit mo…
Now you have your packages deployed and are ready to set up a schedule to run them on a regular basis. The typical tool used is the SQL Server Agent. …
SSIS Package: The security of SSIS packages has changed because of the architecture changes in Integration Services. In the previous version of SSIS …
The SSIS command-line tools are still available, but you should be using the T-SQL functions to execute and manage your packages. However, DTExec and …
This section covers older configurations. You will need this information if you decide to run your packages with the package model and use configurati…
This is another section that applies only to the package deployment model. If you can, you should be changing your SSIS methods to take advantage of t…
Error rows have been briefly touched upon in several Topics, including The Data Flow Topic, Accessing Heterogeneous Data Topic and Understanding and T…
The SSIS development team has exposed a robust architecture to manage SSIS through managed code. The managed code in this case refers to the use of th…
You can deploy packages using the project deployment model or the package deployment model. The project deployment model, enables you to use the param…
What’s In This Topic? Using the SSIS catalog Choosing and using the different deployment models Using T-SQL with SSIS Managing security in SSIS …
SSIS includes two special tasks that enable you to query system information and monitor system events: the WMI Data Reader Task and the WMI Event Watc…
Logging is an important part of any data process because it gives administrators and developers insight into what transpired during a process, with th…
The Pipeline Component Methods Components are normally described as having two distinct phases: design time and runtime. The design-time phase refers…
Prior to SQL Server 2012, a package configuration was the optimal method to store configurable values that could be used in your package without alter…
The Components Now you can move on to actually building the components. These components are simple and demonstrate the most commonly used methods wh…
SQL SERVER DATA TOOLS The SQL Server Data Tools (SSDT) is where you’ll spend most of your time as an SSIS developer. It is where you create and deplo…
Editions of SQL Server The available features in SSIS and SQL Server vary according to what edition of SQL Server you’re using. Of course, the more h…
The SSIS Tools As with any Microsoft product, SQL Server ships with a myriad of wizards and tools to make your life easier and reduce your time to ma…
SSIS ARCHITECTURE Microsoft has truly established SSIS as a major player in the extraction, transformation, and loading (ETL) market. Not only is the…
SQL Server Integration Services (SSIS) is the anchor in a growing suite of products that make up the Microsoft SQL Server Business Intelligence (BI) p…
This SSIS tutorial gives you an overview and talks about the fundamentals of SSIS. SSIS Tutorial for Beginners In this SSIS tutorial, we will start …
Now you can practice what you have learned in Data Flow Topic, pulling together some of the transformations and connections to create a small ETL proc…
The Solution Explorer Window The Solution Explorer window is where you can find all your created SSIS packages, project connection managers, project …
Log providers are used to define the destination for the log information that is generated when a package executes. For instance, if you require a rec…
The SSIS Package Designer The SSIS Package Designer contains the design panes that you use to create an SSIS package. This tool contains all the item…
There are a few key performance counters to watch when you’re trying to monitor the performance of your SSIS package. These counters greatly help you …
Many programmers use breakpoints to debug programs, viewing the value of variables and following the flow of the logic as they step through the source…
SSIS Script Task SCRIPT TASK (.NET)The Script Task enables you to access the Microsoft Visual Studio Tools for Applications (VSTA) environment to wri…
Each task and container raises events as it runs, such as an OnError event, among several others that are discussed shortly. SSIS enables you to trap …
ANALYSIS SERVICES TASKS The Analysis Services tasks are provided in the SSIS environment to deal with generating and updating cubes and working with …
Data Flow Task The most important task in SSIS is the Data Flow Task. The SSIS Data Flow Task can be selected directly from the SSDT Toolbox, and the…
Data Preparation Tasks Before processing data from other systems, you sometimes have to first retrieve it or validate the content to determine your l…
RDBMS SERVER TASKS These tasks could also be considered data preparation tasks, as they are responsible for bringing data sources into the ETL proces…
Execute SQL Task The Execute SQL Task is one of the most widely used tasks in SSIS for interacting with an RDBMS Data Source. The Execute SQL Task is…
Precedence constraints are the green, red, and grey connectors in the Control Flow that link the tasks together and can be used to manage the workflow…
WORKFLOW TASKS So far, we’ve been focused on tasks that are occurring within the immediate realm of ETL processing. You’ve looked at tasks for creati…
SMO ADMINISTRATION TASKS SQL Server Management Objects The last section of this chapter is reserved for a set of tasks that are convenient for copyi…
The Data Flow In the previous post, you were introduced to the Control Flow tab through tasks. In this chapter, you’ll continue along those lines wit…
SOURCES A source in the SSIS Data Flow is where you specify the location of your source data. Most sources will point to a Connection Manager in SSIS…
Software Development Life Cycles Software Development Life Cycles (SDLCs) represent a systematic approach to each component of application developmen…
Destinations Inside the Data Flow, destinations accept the data from the Data Sources and from the transformations. The architecture can send the dat…
What’s In This Topic? 1. Understanding the different types of SDLCs2. Versioning and source control overview3. Working with Subversion (SVN)4. Workin…
Pipeline Performance Monitoring: Earlier in this Understanding and Tuning the Data Flow Engine Topic, you looked at the built-in pipeline logging fu…
SSIS Data Flow Design And Tuning Now that you know how the Data Flow engine works, it will be easier to understand Data Flow design principles and tu…
The SSIS Engine Before learning about buffers, asynchronous components, and execution trees, you might find it useful to consider this analogy — traf…
COMMON TRANSFORMATIONS Transformations or transforms are key components to the Data Flow that transform the data to the desired format as you move fr…
Error Outputs Error outputs can obviously be used to improve reliability, but they also have an important part to play in terms of scalability as wel…
Package Transaction This part of the Reliability and Scalability Topic describes how you can use transactions within your packages to handle data con…
The Lookup Transformation The Lookup Transformation in SSIS enables you to perform a similar relational inner and outer hash-joins. The main differen…
Restarting Packages Everyone has been there — one of your Data Transformation Services (DTS) packages failed overnight, and you now have to completel…
In SSIS Tasks, you read about tasks and how they interact in the Control Flow. Now we’re going to cover one of the special types of items in the Contr…
The language used to build expressions can be a bit disorienting at first. If you started out as a programmer, you will be comfortable switching betwe…
Date and Time Functions Date and time functions tend to cause confusion for new SSIS developers. In most instances, the different syntax is causing t…
ODBC stands for Open Database Connectivity and is a technology connection standard for passing data between systems that is widely used today for acce…
Flat files are one of the more common sources to work with because data in the flat files is easy to read and create by most RDBMS systems and ETL too…
Introduction Although XML is not a common source for large volumes of data, it is an integral technology standard in the realm of data. This section …
You use the conditional expression operator to build logical evaluation expressions in the format of an IF..THEN logical structure: Learn how to use SSIS, from…
String Functions Handling strings in SSIS expressions is different from dealing with string data in SQL Server. The previous section discussed some o…
Dealing with NULLs In SSIS, variables can’t be set to NULL. Instead, each variable data type maintains a default value in the absence of a value. For…
Boolean Expressions Boolean expressions evaluate to either true or false. In their simplest implementation, precedence constraints use Booleans expressions as …
Literals are hard coded information that you must provide when building expressions. SSIS expressions have three types of literals: numeric, string, a…
String Concatenation There are many uses for building strings within an expression. Strings are built to represent a SQL statement that can be executed against…
Because of SQL Server’s world-class reporting and business intelligence tools, more and more shops running Oracle rely on SQL Server for their reporti…
UNDERSTANDING DATA TYPES In SSIS, you must pay attention to data types, whether the data is coming from your Data Flow, is stored in variables, or is…
SSIS deals with Excel and Access data in a similar fashion because they use the same underlying provider technology for data access. For Microsoft Off…
SSIS includes multiple objects that can be used to create dynamic packages. Below Screen Shoot provides a graphical description of this model in SSIS.…
What’s In This Topic? Dealing with Excel and Access data Integrating with Oracle Working with XML files and web services Extracting from flat fi…
This section focuses on data loading. Many of the same techniques presented in the data extraction section apply here too, so the focus is on areas th…
To simplify the explanation of the Lookup Transformation’s operation in the next few sections, this section presents a typical ETL problem that is use…
Putting it all together is perhaps the easiest part of the ETL process because it involves simply using SSIS to coordinate the execution of the packag…
With the basic ETL out of the way, you will now jump into a more complex SSIS package that attempts to scrub data. You can start this scenario by down…
Processing SSAS objects in SSIS can be as easy as using the Analysis Services Processing Task. However, if your SSAS cubes require adding or processin…
Fact table loading is often simpler than dimension ETL, because a fact table usually involves just inserts and, occasionally, updates. When dealing wi…
WHAT’S IN THIS TOPIC ? Selecting your scripting language and getting started Adding assemblies to SSIS Script objects Understanding Script Task u…
Dimension transformation and loading is about tracking the current and sometime history of associated attributes in a dimension table. Below Screen sh…
Among the various applications of SQL Server Integration Services (SSIS), one of the more common is loading a data warehouse or data mart. SSIS provid…
USING THE SCRIPT TASK Now that you have a good overview of the scripting environment in SSIS, it’s time to dig into the Script Task and give it a spi…
SQL SERVER CHANGE DATA CAPTURE The advantage to the control table pattern you saw previously is it works across any database platform you may use. Th…
Introduction Even if a data warehouse solution starts off simple — using one or two sources — it can rapidly become more complex when the users begin…
INCREMENTAL LOAD So far, most of the data loading procedures that have been explained in this tutorial have done a full load or a truncate and load. …
Advanced Derived Column Use If you’ve used the data flow in SSIS for any amount of data transformation logic, you will no doubt have used the Derived…
Master Data Management Master data management (MDM) is the process an organization goes through to discover and define data with the ultimate goal of…
DQS CLEANSING Introduced in SQL Server 2012 was a component called Data Quality Services (DQS). This is not a feature of Integration Services, but it…
The two fuzzy transformations within SSIS, Fuzzy Lookup and Fuzzy Grouping, deal with associating data through data similarity, rather than exact data…
What’s In This Topic? Using the Derived Column Transformation for advanced data cleansing Applying the Fuzzy Lookup and Fuzzy Grouping transformations and u…
The Cache Connection Manager (CCM) and Cache Transform enable you to load the Lookup cache from any source. The Cache Connection Manager is the more c…
The Merge Join Transformation in SSIS enables you to perform an inner or outer join operation in a streaming fashion within the SSIS Data Flow. The Merge Join T…
You have now been all over the VSTA development environment and have been introduced to the different languages that move SSIS development into the ma…
The Script Component provides another area where programming logic can be applied in an SSIS package. This component, which can be used only in the Da…
Now that the key stages have been explained, you can examine each of them in detail. This guide makes very few assumptions about your current level of…
Building the Transformation Component In this section, you build the transformation that is going to take data from the upstream Source adapter. Afte…
You are no doubt already familiar with the term scaling out, and of course the concept can be applied to SSIS systems. Although there are no magical s…
One of the most exciting features of SSIS is the fact that you can use T-SQL to interact with your packages and projects natively (if you are using th…
SQL Server 2014 and SQL Server Integration Services (SSIS) integrate with source control products such as Subversion (SVN) and Visual Studio Team System. SVN is…