SSIS 2012 2008 Interview Questions with Answers

1. In this SSIS 2012 demo i will explain how to create an SSIS integration project in SQL Server Integration services project in BIDS 2012 or 2008
2. SSIS 2012 or SSIS 2008 is a powerful tool that is used for Data Migration and Data Integration and is an important part of Microsoft Business Intelligence (MSBI)
3. The tools that we have used are SQL Server 2008 or 2012 ,Business intelligence development studio 2008 or 2012

1. In this i will explain the importance of bulk insert task in ssis 2008 or ssis 2012 and i will also give some examples
2. The Bulk Insert task can only transfer data from a text file into a SQL Server table. 
3. Bulk Insert task supports only Object Linking and Embedding, Database (OLE DB) connections for the destination database.
4. While the Data Flow task can provide this capability, the Bulk Insert task is the most efficient method for this type of transfer, because it cannot perform transformations or error logging on the data while it is moving from the source file to the table.
5. If the destination table or view already contains data, the new data is appended to the existing data when the Bulk Insert task runs. 
6. If you want to replace the data, run an Execute SQL task that runs a DELETE or TRUNCATE statement before you run the Bulk Insert task.
7.  The Bulk Insert task is particularly useful in situations where data needs to be retrieved from a system that cannot be connected to SSIS. Text files can act as a great intermediary data source and the Bulk Insert task will minimise the time required to import the data into a SQL Server table.

1. This category includes a Name and Description property.

1.  This category includes a File property to define the source text file connection. 
2. The connection category also contains a Connection and Destination Table property to define the destination SQL Server database and table. 
3.  The text file RowDelimiter and ColumnDelimiter property can be used to define the text file format or if the Format property is set to Use File, a FormatFile can be used to define custom mappings between the text file and SQL Server table. 
4. If you have a format file that was created by the bcp utility, you can specify its path in the Bulk Insert task. 
5. The Bulk Insert task supports both XML and non-XML format files.

1.  On the Options page, the Codepage and DataFileType property is used to define the type of text file. 
2. The BatchSize determine how many rows are inserted into the table as a batch. 
3. A setting of zero means all rows in the text file are inserted as one batch.
4. If a batch size is set, each batch represents a transaction that is committed when the batch finishes running. 
5. The FirstRow and LastRow properties determine the starting and ending row of the data in the text file. 
6. You can also use the MaxErrors property to determine the number of errors that are allowed before the Bulk Insert task fails.

Data Analysis Transformations in SSIS 2012 2008 

1. Pivot Transformation
2. Unpivot Transformation
3. Data Query Transformation

1. The Pivot transformation takes data from a normalised result set and presents the data in a cross tabulated or denormalised structure. 
2. For example, a normalised Orders data set that lists customer name, product and quantity purchased typically has multiple rows for any customer who purchased multiple products, with each row for that customer showing order details for a different product. 
3. By pivoting the data set on the product column, the Pivot transformation can output a data set with a single row per customer. 
4. That single row lists all the purchases by the customer, with the product names shown as column names, and the quantity shown as a value in the product column.
5.  Because not every customer purchases every product, many columns may contain null values.

• Component Properties tab. The Component Properties tab allows you to specify a Name and Description for the OLE DB Command Task. You can also specify a locale with the LocaleID property and whether the data flow is validated at run time or design time by using the ValidateExternalMetadata property.
• Input Columns tab. The Input Columns tab consists of the Available Input Columns property that enables you to select the columns that the Pivot transformation operation will affect. When a column is selected, it appears in the Input Columns list. The Output alias allows you to define the name of the output column. The Usage Type property defines if the data imported is READONLY data or READWRITE data.
• Input and Output Properties tab. The Input and Output Properties tab allows you to set the properties of the input and the output columns. The most important property here is the PivotUsage property. This determines what role the input column will play in creating the pivot table and can be configured with the following values:
• 0. The column is not pivoted, and the values are passed through to the transformation output.
• 1. The column is part of the set key that identifies one or more rows as part of one set.
• 2. The column is a pivot column. At least one column is created from each column value. This data must be sorted input column.
• 3. The values from this column are placed in columns that are created because of the pivot.

Unpivot transformation

1. The Unpivot transformation takes data from a denormalised or cross-tabulated result set and presents the data in a normalised structure. 
2. The Unpivot transformation can be configured with the following properties.
3. At the bottom of the Unpivot Transformation Editor dialog box is the Pivot key value column name. 
4. Here, you define a column heading for the column that will hold the pivoted data that is converted into normalised data such as Products or Fruits.
5. The Available Input Columns property enables you to select the input columns that the Unpivot transformation operation turns into rows. 
6. When a column is selected, it appears in the Input Columns list. Any columns that are not selected are passed through to the data flow output. The Destination Column allows you to define the name of the destination column in the normalised output.
7. In the Unpivot scenario, multiple input columns are usually mapped to one destination column. 
8. For example, the Available Input Columns may consist of column headings such as Apples, Pears and Peaches.
9.  All of these input columns are mapped to a destination column named Fruits that may be defined by the Pivot key value column name property.
10. The Pivot Key value property specifies the value that is used in the rows in the normalised result set and, by default, uses the same name as the input column but can be changed.

Fact Tables and Dimension Tables in Microsoft BI Datawarehouse

1. A data warehouse is a relational database that centrally stores a consistent view of the data from transactional systems in a denormalised format. 
2. This makes the retrieval of the data more efficient than it would be from a transactional system.
3. A data warehouse will consist of one or more data marts. 
4. A data mart typically represents an area of the business such as sales or finance departments. Within each data mart, there are two types of tables created
5. fact tables and dimension tables

Dimension Tables

1. Dimension tables contain columns that hold information that represents a business entity. 
2. Examples of dimension tables can include time, region and customers. The columns will contain information that is specific to the entity. In the case of the customers dimension table, columns can include Firstname and Lastname. 
3. You can define as many columns as you want to precisely define the customer entity. However, placing unnecessary columns increases the volume and time it would take to populate the dimension table with data by using SSIS.
4. Dimension tables should consist of two columns that are important in loading the data warehouse. The first column is typically a dimension key known as a surrogate key. 
5. This is usually a primary key column that uses an identity constraint to automatically populate the column value. It is also used to provide a relationship to the fact table. The second column is referred to as an application key column. This column is used to hold the value of the record in the original transactional system.
6. You may also wish to add columns to the dimension table that can be used to track changes to the records within the dimension table. As information within the table changes, you can add a ModifiedDate column that can be used to track the date when changes occurred to a record. To keep historical records of changed data, you could include StartDate and EndDate columns that help you to determine the duration that a record was in a particular state.
7. Dimension tables contain columns that hold information that represents a business entity. You can define many dimension tables. 
8. An example of a business entity that is represented as a dimension table could be customers. The columns will contain information that is specific to that entity. In the case of the customers dimension table, columns can include Firstname and Lastname. Dimension tables will also contain a primary key column that uniquely identifies the records in the dimension table. The primary key also defines a one-to-many relationship with an associated column or columns within a fact table.
9. Also note that dimension tables can be shared between different data marts. A common example of this is a Time dimension table. This could be used by different data marts to provide consistent information about time across multiple data marts. These types of dimension tables are known as conformed dimensions. In order to manage the movement of data from source systems to the data warehouse, staging tables may also be created in the data warehouse. This allows tables to act as intermediary tables for cleansing data before loading the cleansed data into the respective dimension tables.

Fact Tables

1. The fact table contains information about historical transactions and typically represents this information as integer data. 
2. For example, you can have three dimension tables named Customers, Region and Time. Each has a primary key column that relates to the fact table. In this example, Customer 6231 in the fact table is equal to Francois Ajenstat in the dimension table. Region 4 is equal to the value of West and the Time value of 3423 is equal to the 18th of August, 1975 in the Time dimension table. The additional columns will contain measures about the transaction such as order quantity and sales amount. Using integer data to represent historical transactions allow SQL Server Analysis Services to process the data much more efficiently
3. The fact table is at the heart of the data mart and typically consists of three groups of columns. The first is a primary key column to maintain the integrity of the fact table itself. The second group of columns are known as foreign keys.
4. Each of these columns relate to a dimension table primary key to provide a relationship between the fact table and the contextual information provided by the dimension table. Imagine that the image represents a Sales data mart, and that the three smaller tables represent dimension tables called CustomerDim, TimeDim and RegionDim. 
5. The fact table, which would be the big table in the image, would contain three foreign key columns that would relate directly to each dimension table.
6. The fact table also contains a third group of columns known as measures. This group of columns hold business metrics such as sales units, sales amounts and cost amounts. As a result, the fact table ideally consists of business information represented by integer values.
7. On occasions, a fourth group of columns may be seen within the fact table. This may be one or more columns that are referred to as degenerative dimensions. A degenerative dimension is information about a business entity that is stored within the fact table itself rather than held within a separate dimension table. Using the example of the CustomerDim, TimeDim and RegionDim, these three dimension tables provide context information about customers, time and regions. A central fact table holds foreign key references to the dimension tables and includes measures such as SalesUnits and OrderQuantity. We can include a SalesOrderNumber for each record of a sale. However, rather than creating a separate dimension table that would require a join to the dimension table just to return one piece of contextual information, it is more efficient to store this piece of information within the fact table itself.
8. Fact tables are created like any table that is created in SQL Server. You also define the primary and foreign key relationships within the database engine. Therefore, you are limited to the 8-KB limit on the amount of information that is stored in a row within a table

Introduction to SSIS 2012 For Beginners 

1. In this article i will give a basic idea about ssis 2012 and the different scenarios when to use ssis
2. SSIS represents the foundation of a BI solution. 
3. While the impact and importance of this tool is not readily seen by the end users, it is critical for providing the data that would be used to populate a data warehouse that would be eventually used by Analysis Services or Reporting Services. 
4. SSIS can also be used to perform unplanned data transfers to meet business needs as they arise, some of which can be handled by the Import and Export wizard. 
5. The Import and Export wizard is usually the first tool that database professionals use to interact with SSIS. However, SSIS itself is an extremely flexible tool that can go beyond the capabilities that the Import and Export wizard has.
6. SQL Server Integration Services (SSIS) is an Extract, Transform and Load tool (ETL) that can be used to move and transform data within an organization. 
7. SQL Server Integration Services holds the entire logic of ETL operations within a package. 
8. A package represents a single unit of work. Within the package, SSIS uses two engines to control the workflow of the SSIS package; the control flow engine and the data flow engine.
9. The control flow engine contains tasks that direct how the package should operate as a whole by using precedence constraints. 
10. The tasks that are available in the control flow can be broadly categorised into data-dependent tasks and data-independent tasks.
11. Some data-independent tasks include the Send Mail task, File System task and Execute Process task.
12. These types of tasks do not work with data directly, but can be added to a package to complement other tasks that may load the data. For example, you may use a Send Mail task to run when another task in the control flow fails and you need to inform the administrator by e-mail of the error.
13. Data-dependant tasks include the Bulk Insert task and the Data Flow task. 
14. These types of tasks are typically responsible for the movement of data between source and destination servers. All tasks within the control flow engine have their own properties window that requires configuration; however, the Data Flow task is unique.
15. It is the only task that has its own designer interface. The Data Flow task exposes the data flow engine. Within this, you can focus specifically on how the data movement occurs. 
16. This can involve defining data flow sources and destinations. You can also configure and finely tune the type of transformations that will take place within the Data Flow task.
17. Collectively, the control flow and the data flow engine work at the heart of SSIS to provide the ability to move and transform data. 
18. Additional components of SSIS include event handlers that expose the same tasks that are available within the control flow engine. The difference is that the tasks in the event handler will only run on a specific event. 
19. This allows you to create robust packages. Variables can be used to add dynamic capabilities to packages, passing values between tasks and other packages for custom data loads.
20. Package configurations can be used to ease the deployment of packages into a production environment by populating package settings at run time. 
21. This is useful when you may not exactly be aware of the setup of your production environments

1. There are core components of SSIS that enables you to create an ETL solution that can be used to facilitate a part of a BI solution. 
2. In this article i will be explaining package ,variables and package configurations 

Package in SSIS 2012 or 2008

1. A package is the highest object in SSIS and represents a single unit of work.
2. A package can host multiple packages within it to control the flow of multiple packages. 
3. Typically, it contains control flow and data flow elements to manage data movement and transformations.
4. You can create packages by using Business Intelligence Development Studio or the Import and Export wizard

1. Variables can be used to provide additional functionality to SSIS packages. 
2. SSIS provides a host of system variables. These system variables can be used for customisation of a package. 
3. An example could include using the system variables to populate package information to a table using custom logging.
4. SSIS also allows you to create user-defined variables.
5. The names of user-defined and system variables are case-sensitive. These variables can be used to hold a value that may be used elsewhere in a package. 
6. For example you may use a GETNOW() function to populate a variable. This variable may then be used in the WHERE clause of an Execute SQL Task to return results back where the ModifiedDate column of a table is equal to the value in the variable.
7. In this respect, variables can prove valuable in providing package logic.

• Environmental variables
• Registry
• SQL Server
• XML configuration file
• Parent package variable

1. In this article i will be explaining you the control flow components in ssis 2012 
2. Control flow is responsible for managing how the entire package operates
3. There are three core components that can be used in the control flow to manage a package execution:

1. Tasks are commonly used to add functionality to a package. 
2. This functionality is wide and varied and provides the ability to perform the work in the package. 
3. The categories of tasks that are performed are as follows:

• Data tasks work with data.
• File and network tasks interact with the file system and FTP sites.
• Scripting tasks add custom transformations and logic to SSIS packages.
• Database object transfer tasks manage the movement of SQL Server objects.
• Package execution task control the execution of SSIS and Data Transformation Services (DTS) packages.
• Analysis Services tasks interact with Analysis Services.
• Maintenance tasks perform common SQL administrative functions.
• Windows Management Instrumentation (WMI) tasks interact with WMI.

1. In this article i will explain the event handlers in SSIS 2012 
2. Event handlers are a component that execute tasks based on an event that occurs at the package, container or task level at run time. 
3. The tasks that can be performed within an event handler are the same tasks that are available within the control flow. 
4. The events that can be defined include:

• OnError. This event is raised by an executable when an error occurs.
• OnExecStatusChanged. This event is raised by an executable when its execution status changes.
• OnInformation. This event is raised during the validation and execution of an executable to report information. This event conveys information only, no errors or warnings.
• OnPostExecute. This event is raised by an executable immediately after it has finished running.
• OnPostValidate. This event is raised by an executable when its validation is finished.
• OnPreExecute. This event is raised by an executable immediately before it runs.
• OnPreValidate. This event is raised by an executable when its validation starts.
• OnProgress. This event is raised by an executable when measurable progress is made by the executable.
• OnQueryCancel. This event is raised by an executable to determine whether it should stop running.
• OnTaskFailed. This event is raised by a task when it fails.
• OnVariableValueChanged. This event is raised by an executable when the value of a variable changes. The event is raised by the executable on which the variable is defined.
• OnWarning. This event is raised by an executable when a warning occurs.

• Clean up temporary data storage when a package or task finishes running.
• Retrieve system information to assess resource availability before a package runs.
• Refresh data in a table when a lookup in a reference table fails.
• Send an e-mail message when an error or a warning occurs or when a task fails.

SSAS 2012 2008 Introduction to SQL Server Analysis Services for beginners and scenarios to use SSAS 

1. SSAS 2012 is the technology that is provided by SQL Server to create cubes. 
2. Organizations use cubes to manage large volumes of data so that complex queries can be retrieved in a more efficient manner than if the queries were to be executed against a relational database. 
3. With cubes, there is also the ability to preaggregate data and store them in the cube for faster retrieval of summary information.
4. The most underused aspect of SSAS is data mining that enables a business to perform trend analysis and identify relationships between data that may not be immediately apparent to end users when looking at the data. 
5. Analysis Services is a fantastic tool that can host the data in a central point as information
6. SQL Server Analysis Services provides the ability to efficiently query multidimensional data. It also provides the capability to use data mining to extrapolate trends and patterns within data. 
7. There are core components that you should be aware of to create a cube.
8. At its core, data sources must be defined to provide connection information to the source data for the cube. You can then define data source views to define the specific tables on which the cube is based. This will typically involve dimension tables and fact tables from a data warehouse.
9. You can then define dimensions within the cube against dimension tables within the data warehouse. The dimensions will provide contextual information and act as the axis for the cube. For improved readability, you can also define hierarchies to allow the user to drill down into the dimension data.
10. On completing the creation of the dimensions, we can then run the cube wizard to create the cube. The wizard allows you to create a cube using existing dimensions or from data from a table. 
11. You can also create an empty cube if you do not have the data available. Once the cube is created, you can configure cube partitions. This enables you to spread the data over multiple physical disks to improve the performance of cube processing and querying data in the cube. 
12. SQL Server Analysis Services also provides additional OLAP objects to extend the functionality of the cube, including calculated members, KPIs, actions and translations.
13. For data mining, you can use new or existing data sources and data source views to point to the source data. The source data for data mining can be an existing cube or data from a table or a view. 
14. You can then create a data mining structure that integrates the source data with a data mining algorithm, known as a data mining model in order to find patterns in the data using the data mining model. On completing this, you have the ability to view and validate the results that are returned back to the data mining viewer.

SSAS 2012 2008 SQL Server Analysis Services Components

1. There are core components that are used to create an Analysis Services solution within Business Intelligence Development Studio. 
2. Understanding these components will help you to start multidimensional and data mining solutions within your BI solution.

1. Data sources
2. Data source views
3. Cubes
4. Dimensions
5. Measures
6. Slice and dice
7. Data mining

1. Data sources provide the connection information for the source system on which the Analysis Services solution is based.
2. Typical configuration for data sources involve specifying the name of the server on which the data is stored. You also specify the credentials required to connect to the server and the name of the database to access. You should also consider that additional settings may be required between different data sources.
3. The data sources that are supported by SSAS  2012 2008 include:

• SQL Server 7.0 using the SQL OLE DB Provider or the .NET native OLE DB provider (x86, x64 and ia64).
• SQL Server 2000 using the SQL OLE DB Provider or the .NET native OLE DB provider (x86, x64 and ia64).
• SQL Server 2005 using the SQL Server Native Client OLE DB provider or the SqlClient.NET managed provider (x86, x64 and ia64).
• SQL Server 2008 using the SQL Server Native Client OLE DB provider or the SqlClient.NET managed provider (x86, x64 and ia64).
• Oracle 9.0 using the Microsoft OLE DB Provider for Oracle or the .NET native OLE DB provider (x86 only).
• IBM DB2 8.1 using Microsoft OLE DB Provider for DB2 (x86, x64, ia64)—only available for Microsoft SQL Server 2005 Enterprise Edition or Microsoft SQL Server 2005 Developer Edition.
• Access with Microsoft Jet 4.0 OLE DB provider (x86 only).
• Teradata v2R6 with OLE DB 1.3 provider from NCR (x86 only).
• Teradata managed provider (x86, x64 and ia64).  

Data source views in SSAS 2012

1. Data source views are objects based on a data source that provide an abstraction of a subset of tables, columns and relationships from the data source that you require as part of your Analysis Services solution.
2. This abstraction layer holds metadata about the objects and allows you to create an Analysis Services solution without a permanent connection to the data source.
3. To run the cube wizard or the data mining wizard, a data source view must be created.
4. Annotations can be added to provide friendly names for the tables and columns to improve the readability for the user.
5. The type of information that is defined within a data source view includes:

• A data source view name.
• A definition of any subset of the schema retrieved from one or more data sources including:
• Tables
• Columns
• Relationships

1. An OLAP database can hold one or more cubes. A cube is a fundamental unit of multidimensional analysis.
2. A cube consists of dimensions and measures. 
3. The dimensions form the axis of the cube and provide contextual information for the data that resides in the cube.
4. This information is known as measures.

1. Dimensions provide the contextual information for the data that resides in a cube. 
2. The dimensions in a cube typically map to a dimension table within the data warehouse.
3. As dimensions are used to describe a business entity, dimensions consist of attributes that provide specific information about the business entity. 
4. These attributes typically map to a specific column within the dimension table in the data warehouse.
5. To improve readability, hierarchies can be introduced within the dimension. 
6. For example, within a time dimension, a hierarchy named CalendarYear can be created containing three levels—Year, Quarter and Month. 
7. These levels allow the user to easily drill down into specific time periods

1. Measures provide the numeric information that resides inside the cube. 
2. They typically map to the measures columns in the fact table of a data warehouse.
3. As well as including the measures information from the fact table, SSAS also creates preaggregated versions of the measures.
4. For example, a fact table may consist of a measure named OrderQuantity. As well as including the detail information that is provided by the fact table, Analysis Service creates preaggregated OrderQuantity totals for yearly, quarterly or monthly totals. By creating this preaggregated data, queries for this information is retrieved more efficiently.

1. Slicing and dicing is the technique that can be used to interrogate the data from within a cube.
2. This technique is implemented in products such as Excel 2007/10/13. This allows you to use the pivot table feature to slice and dice the data to retrieve specific data.
3. Business Intelligence Development Studio provides a browser window that allows you to slice and dice the data to test the results that is returned before the cube is deployed into a production environment.
4. Furthermore, you can also employ this technique within Reporting Services as the basis for providing data for the reports that are deployed through SSRS.

1. Data mining is a feature in SSAS that enables you to extrapolate trends and patterns in the data.
2. You can create data mining structures to explore the data against algorithms that are provided
3. There is also a Data Mining Add-in for Excel 2007 2010 2013 that allows you to explore the results that are returned by a data mining structure. You can also use the add-in to create your own data structures