Blog

SSIS Interview Questions And Answers For Experienced - If you're looking for SSIS Interview Questions, you are at right place. There are lot of opportunities fr…

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 package de…

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, you have to ma…

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 mode and poten…

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. Although y…

The security of SSIS packages has changed because of the architecture changes in Integration Services. In the previous version of SSIS you could deploy to the M…

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 DTUtil hav…

This section covers older configurations. You will need this information if you decide to run your packages with the package model and use configuration files o…

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 the project…

Error rows have been briefly touched upon in several Topics, including The Data Flow Topic, Accessing Heterogeneous Data Topic and Understanding and Tuning the …

The SSIS development team has exposed a robust architecture to manage SSIS through managed code. Managed code in this case refers to the use of the .NET Framewo…

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 the project…

What’s In This Topic ? Managing SSIS in SQL Server 2014 The Managed Object Model code library Using the Project class Using the Application and Package cl…

You can deploy packages using the project deployment model or the package deployment model. The project deployment model, enables you to use the parameters and …

There are three steps to adding a user interface to any component, and each will be examined in detail. However, it is essential that you build the actual compo…

What’s In This Topic? Outlining key steps to create a UI for your component Using the IDtsComponentUI interface Creating your first custom user UI Now th…

If you already built components in SQL Server 2008 and you want to use them in SQL Server 2014, you have to update the references and recompile the components. …

In this section, you install the components you have created into the SSIS design environment so you can use them to build packages. You then learn how to debug…

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 Schedulin…

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 Watcher Task. …

The Application object is the core class that exposes methods used to connect to and interface with an SSIS service instance for pre-SQL 2012 instances of SQL S…

Logging is an important part of any data process, because it gives administrators and developers insight into what transpired during a process, with the followi…

Programming and Extending SSIS Once you start implementing a real-world integration solution, you may have requirements that the built-in functionality in SSIS…

The Pipeline Component Methods Components are normally described as having two distinct phases: design time and runtime. The design-time phase refers to the me…

Prior to SQL Server 2012 a package configuration was the optimal method to store configurable values that could be used in your package without altering the pac…

The Components Now you can move on to actually building the components. These components are simple and demonstrate the most commonly used methods when buildin…

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 deploy your SSI…

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 high-end th…

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 market. In t…

SSIS ARCHITECTURE Microsoft has truly established SSIS as a major player in the extraction, transformation, and loading (ETL) market. Not only is the SSIS tech…

Welcome to SQL Server Integration Services SQL Server Integration Services (SSIS) is the anchor in a growing suite of products that make up the Microsoft SQL S…

This tutorial gives you an overview and talks about the fundamentals of SSIS. WHAT IS SSIS?  SQL Server Integration Services (SSIS) is the anchor in a growing…

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 process. This …

THE SOLUTION EXPLORER WINDOW The Solution Explorer window is where you can find all your created SSIS packages, project connection managers, project parameters…

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 record of the…

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 items you need…

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 troublesho…

SSIS TASKS SSIS tasks are the foundation of the Control Flow in SSIS. When you are on the Control Flow design surface in SSDT, the SSIS Toolbox is populated wi…

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 code. SSI…

SSIS SCRIPT TASK SCRIPT TASK (.NET) The Script Task enables you to access the Microsoft Visual Studio Tools for Applications (VSTA) environment to write and ex…

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 and handle…

ANALYSIS SERVICES TASKS The Analysis Services tasks are provided in the SSIS environment to deal with generating and updating cubes and working with data minin…

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 then the sour…

DATA PREPARATION TASKS Before processing data from other systems, you sometimes have to first retrieve it or validate the content to determine your level of co…

RDBMS SERVER TASKS These tasks could also be considered data preparation tasks, as they are responsible for bringing data sources into the ETL processes, but w…

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 used for …

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 of a pack…

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 creating control…

What’s In This Topic? Working with precedence constraints Creating failure flows with expressions Defining SSIS events Handling events in a package Troub…

SMO ADMINISTRATION TASKS SQL Server Management Objects The last section of this chapter is reserved for a set of tasks that are convenient for copying or movi…

THE DATA FLOW In the Previuos post you were introduced to the Control Flow tab through tasks. In this chapter, you’ll continue along those lines with an explor…

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. By point…

Software Development Life Cycles (SDLCs) represent a systematic approach to each component of application development — from the initial idea to a functioning p…

DESTINATIONS Inside the Data Flow, destinations accept the data from the Data Sources and from the transformations. The architecture can send the data to nearl…

What’s In This Topic ? 1. Understanding the different types of SDLCs 2. Versioning and source control overview 3. Working with Subversion (SVN) 4. Working with…

Earlier in this Understanding and Tuning the Data Flow Engine Topic, you looked at the built-in pipeline logging functionality and the active time reports and h…

Now that you know how the Data Flow engine works, it will be easier to understand Data Flow design principles and tuning practices.  Designing a data-processin…

The SSIS Engine Before learning about buffers, asynchronous components, and execution trees, you might find it useful to consider this analogy — traffic manage…

COMMON TRANSFORMATIONS Transformations or transforms are key components to the Data Flow that transform the data to a desired format as you move from step to s…

What’s In This Topic ? Understanding the Control Flow and Data Flow Learning the Data Flow architecture and transformation types Designing and tuning the D…

Error outputs can obviously be used to improve reliability, but they also have an important part to play in terms of scalability as well. From a reliability per…

What’s In This Topic ? Restarting packages Using package transactions for data consistency Using error outputs for reliability and scalability Scaling out…

This part of the Reliability and Scalability Topic describes how you can use transactions within your packages to handle data consistency. Two types of transact…

Though the methods and syntax you employ in the relational and SSIS worlds may differ, joining multiple row sets together using congruent keys achieves the same…

THE LOOKUP TRANSFORMATION The Lookup Transformation in SSIS enables you to perform the similar relational inner and outer hash-joins. The main difference is th…

Everyone has been there — one of your Data Transformation Services (DTS) packages failed overnight, and you now have to completely rerun the package. This is pa…

Joining Data What’s In ThisTopic? The Lookup Transformation Using the Merge Join Transformation Building a basic package Using the Lookup Transformation …

What’s In This Topic? Learning when to use containers Working with Sequence Containers Working with the For Loop Container Using a Foreach Loop Container …

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 between Visual …

Date and Time Functions Date and time functions tend to cause confusion for new SSIS developers. In most instances, the different syntax is causing the difficu…

Beyond the heterogeneous data already discussed, you may come across other non-SQL Server systems to which you need access. Examples of this include DB2 or Tera…

ODBC stands for Open Database Connectivity and is a technology connection standard for passing data between systems that is widely used today for access to RDBM…

  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 tools. The…

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 considers XML from a cou…

You use the conditional expression operator to build logical evaluation expressions in the format of an IF..THEN logical structure: Boolean_expression ? expres…

String Functions Handling strings in SSIS expressions is different from dealing with string data in SQL Server. The previous section discussed some of the diff…

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 strings, …

Boolean Expressions Boolean expressions evaluate to either true or false. In their simplest implementation, precedence constraints use Booleans expressions as …

Referencing Variables Referencing variables is easy using the Expression Builder. Drag and drop variables onto the Expression Builder to format the variable in…

Literals are hard coded information that you must provide when building expressions. SSIS expressions have three types of literals: numeric, string, and Boolean…

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 reporting needs. …

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 included …

SSIS deals with Excel and Access data in a similar fashion because they use the same underlying provider technology for data access. For Microsoft Office 2003 a…

SSIS includes multiple objects that can be used to create dynamic packages. Below Screen Shoot provides a graphical description of this model in SSIS. SSIS can …

What’s In This Topic? Dealing with Excel and Access data Integrating with Oracle Working with XML files and web services Extracting from flat files Integ…

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 that have no…

To simplify the explanation of the Lookup Transformation’s operation in the next few sections, this section presents a typical ETL problem that is used to demon…

BASIC TRANSFORMATION TUTORIAL As you can imagine, the primary reason people use SSIS is to read the data from a source and write it to a destination after it’s…

What’s In This Topic? Using the relational engine to facilitate SSIS data extraction Loading data with SSIS and the relational engine Understanding when to…

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 packages in the …

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 downloading th…

Processing SSAS objects in SSIS can be as easy as using the Analysis Services Processing Task. However, if your SSAS cubes require adding or processing specific…

Fact table loading is often simpler than dimension ETL, because a fact table usually involves just inserts and, occasionally, updates. When dealing with large v…

WHAT’S IN THIS TOPIC ? Selecting your scripting language and getting started Adding assemblies to SSIS Script objects Understanding Script Task usage Unde…

Dimension transformation and loading is about tracking the current and sometime history of associated attributes in a dimension table. Below Screen shot shows…

Among the various applications of SQL Server Integration Services (SSIS), one of the more common is loading a data warehouse or data mart. SSIS provides the ext…

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 spin. The Scr…

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. The negative…

Even if a data warehouse solution starts off simple — using one or two sources — it can rapidly become more complex when the users begin to realize the value of…

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. While this…

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 Column Tr…

Master Data Management Master data management (MDM) is the process an organization goes through to discover and define data with the ultimate goal of compiling…

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 is very m…

The two fuzzy transformations within SSIS, Fuzzy Lookup and Fuzzy Grouping, deal with associating data through data similarity, rather than exact data matching.…

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 critical of…

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…

The Lookup Transformation allows you to populate the cache using a separate pipeline in either the same or a different package. You can use source data from any…

You have now been all over the VSTA development environment and have been introduced to the different languages that move SSIS development into the managed code…

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 Data Flow po…

The simple component that was used in the preceding example lacks some of features you may require in your project. For example, components can use runtime conn…

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 developme…

sscscc …

Building the Transformation Component In this section, you build the transformation that is going to take data from the upstream Source adapter. After reversin…

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 switches he…

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 the project …

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…