What is SSIS?

SSIS stands for SQL Server Integration Services. This is an ETL (Extract, Transform, Load) tool. This is a new Data transformation standard for SQL Server 2005 and has replaced the old SQL Server data transformation services. This has many features compared to DTS. This is well designed for BI (Business Intelligence) purpose.

What is DTS?

DTS is nothing but Data Transformation service which is available in SQL server 2000. DTS is a set of tools you can use to import, export, and transform heterogeneous data between one or more data sources, such as Microsoft SQL Server, Microsoft Excel, or Microsoft Access. Connectivity is provided through OLE DB, an open-standard for data access. ODBC (Open Database Connectivity) data sources are supported through the OLE DB Provider for ODBC.

You create a DTS solution as one or more packages. Each package may contain an organized set of tasks that define work to be performed, transformations on data and objects, work flow constraints that define task execution, and connections to data sources and destinations. DTS packages also provide services, such as logging package execution details, controlling transactions, and handling global variables.

These tools are available for creating and executing DTS packages:

• The Import/Export Wizard is for building relatively simple DTS packages, and supports data migration and simple transformations.
• The DTS Designer graphically implements the DTS object model, allowing you to create DTS packages with a wide range of functionality.
• DTSRun is a command-prompt utility used to execute existing DTS packages.
• DTSRunUI is a graphical interface to DTSRun, which also allows the passing of global variables and the generation of command lines.
• SQLAgent is not a DTS application; however, it is used by DTS to schedule package execution.
• Using the DTS object model, you also can create and run packages programmatically, build custom tasks, and build custom transformations.

Can we use the old DTS packages in SSIS?

DTS packages from SQL Server 2000 can be used with SQL Server 2005, however a separate tool must be installed to encompass that functionality.  Also, in the SQL Server Management Studio, any DTS related information can be found in the Object Browser under Management -> Legacy.
The two main differences you need to be aware of with SSIS is that ActiveX has been re-written and Dynamic Properties is no longer supported.  If you do a direct upgrade of your DTS package from SQL Server 2000, any Dynamic Properties tasks will be ported over as stubs (non-working programs) and your ActiveX scripts will not work and need to be re-written.

What is the difference between DTS and SSIS?

SSIS is huge and it offers more than DTS.Internal architect of SSIS is entirely different than DTS and mainly designed to achieve high degree of parallelism and to improve performance. In Microsoft Business Intelligence, Integration services re coupled with Analysis and reporting services. It could process thet data for Dimension processing and Partition processing.

In SQL Server 7.0 and 2000, DTS provided only a limited set of tasks and transformations. It was a challenge for the developer to construct packages with complex and repeating workflows, and to apply different types of column-level transformations to data. The graphical designer that was used to create packages combined the workflow and data flow on a single design surface and offered limited control flow options.

In SQL Server 2005, Microsoft has designed and built a completely new ETL product—SQL Server 2005 Integration Services (SSIS). Integration Services solves many of the difficulties and limitations of DTS. SSIS enhancements include a new extensible architecture, a new package designer, and a multitude of new tasks, looping structures, and transformations and also improvements in package deployment, management, and performance.

Integration Services includes graphical tools and wizards for building and debugging packages; tasks for performing workflow functions such as SQL command execution, FTP operations, and e-mail messaging; data sources and destinations for extracting and loading data; transformations for cleaning, aggregating, merging, and copying data; a management service for administering Integration Services; and application programming interfaces (APIs) for programming the Integration Services object model.

The following are the notable differences

DTS: Message Boxes in ActiveX scripts
SSIS: Message Boxes In Script Tasks

DTS: Dynamic Properties + ActiveX scripts
SSIS: Configurations + PropertyExpressions

DTS: No deployment Wizard
SSIS: Deployment wizards are there

DTS: Limited Set of Transformations
SSIS: Huge number of Transformations are available

DTS: No BI Functionality
SSIS: Complete BI Functionality

DTS: Not Integrated with .Net
SSIS: Integrated with .Net

What is SSIS Package?

A package is nothing but a collection of work items (or) tasks that execute in an orderly fashion. The SSIS runtime engine and its complementary programs actually run your SSIS packages. The engine saves the layout of your packages and manages the logging, debugging, configuration, connections, and transactions. Additionally, it manages handling your events when one is raised in your package.
The runtime executables provide the following functionality to a package.

Containers: Provide structure and scope to your package
Tasks: Provide the functionality to your package
Event Handlers: Respond to raised events in your package
Precedence Constraints: Provide ordinal relationship between various items in your package.

Explanin the Architecture of the SSIS?

The SSIS architecture consists of four main components:

The SSIS Service
The SSIS runtime engine and the runtime executables
The SSIS data flow engine and the data flow components
The SSIS clients

The SSIS Service handles the operational aspects of SSIS. It is a Windows service that is installed when you install the SSIS component of SQL Server 2005, and it tracks the execution of packages and helps with the storage of the packages.

What are the Designer Panes available in BIDS for SSIS package?

The Package Designer contains four tabs:
Control Flow
Data Flow
Event Handlers
Package Explorer
One additional tab, Progress, also appears when you execute packages.

What is Precedence Constraints in SSIS?

Precedence constraints direct the tasks to execute in a given order. They direct the workflow of your SSIS package based on given conditions. Constraint values are the type of precedence constraint.
There are three types of constraint values:

Success: A task that’s chained to another task with this constraint will execute only if the prior task completes successfully.
Completion: A task that’s chained to another task with this constraint will execute if the prior task completes. Whether the prior task succeeds or fails is inconsequential.
Failure: A task that’s chained to another task with this constraint will execute only if the prior task fails to complete. This type of constraint is usually used to notify an operator of a failed event or write bad records to an exception queue.

What is conditional expressions?

An expression allows you to evaluate whether certain conditions have been met before the task is executed and the path followed. The constraint evaluates only the success or failure of the previous task to determine whether the next step will be executed.

The SSIS developer can set the conditions by using evaluation operators. Once you create a precedence constraint, you can set the EvalOp property to any one of the following options:

Constraint: This is the default setting and specifies that only the constraint will be followed in the workflow.
Expression: This option gives you the ability to write an expression (much like VB.NET) that allows you to control the workflow based on conditions that you specify.
ExpressionAndConstraint: Specifies that both the expression and the constraint must be met before proceeding.
ExpressionOrConstraint: Specifies that either the expression or the constraint can be met before proceeding.

What is Container? what are the Containers are available?

Containers are objects that help SSIS provide structure to one or more tasks. They can help you loop through a set of tasks until a criterion has been met or can help you group a set of tasks logically. Containers can also be nested, containing other containers. Containers are set in the Control Flow tab in the Package Designer.
There are four types of containers in the Control Flow tab:
Task Host
Sequence
For Loop
Foreach containers.

What is Source and What are the Sources are available in SSIS Data Flow task?

A source is where you specify the location of your source data.
1. OLE DB Source
2. Excel Source
3. Flat file Source
4. Raw file Source
5. XML Source
6. Data Reader Source

What is Destination and What are the Destinations are available in SSIS Data Flow task?

Inside the data flow, destinations accept the data from the data sources and from the transformations.
1. Data Reader Destination
2. Excel Destination
3. Flat file Destination
4. OLE DB Destination
5. Raw file Destination
6. Recordset destination
7. SQL Server and Mobile destination

What is Transformation and What are the transforamtions are available in SSIS Data Flow task?

Transformations are key components to the data flow that transform the data to a desired format as you move from step to step.
1. Aggregate
2. Audit
3. Character Map
4. Conditional Split
5. Copy Column
6. Data Conversion
7. Data Mining Query
8. derived Column
9. Export Column
10. Fuzzy Grouping and Lookup
11. Import  Column
12. Lookup
13. Merge
14. Merge Join
15. Multicast
16. OLE DB Command
17. Percentage and Row Sampling
18. Pivot and Unpivot
19. Row Count
20. Script Component
21. Slowly Changing Dimension
22. Sort
23. Term Extraction and Lookup
24. Union All

What is Connection Manager?

there is a Connection Managers tab at the bottom of your Package Designer pane. This tab contains a list of data connections that both control flow and data flow tasks can use. Whether the connection is an FTP address or a connection to an Analysis Services server, you’ll see a reference to it here. These connections can be referenced as either source or targets in any of the operations and can connect to relational or Analysis Services databases, flat files, or other data sources.

Most sources will point to the Connection Manager in SSIS. By pointing to the Connection Manager, you can reuse connections throughout your package, because you need only change the connection in one place.Like sources, destinations are managed through the Connection Manager.

What is UDL?

UDL is nothing but Universal Data link files which will allow us to make our DTS package portable. The UDLs can be useful for representing connections to SQL server; and when moving that DTS package from one Environment to another or one server to another, simply edit the UDL file so that it points to the new SQL server. This allows us to move packages freely with out us having to edit the packages. We just need to edit the UDL files. To get this setup working, you simply need to store your UDL files in standard folders on your server.

Is UDL available in SSIS?

NO

What is Package Portability? How can we achieve that?

Package Portability is nothing but, make sure the package should work if we move the package from one location to another location or one server to another server. If we moved the package the connection sources, destinations may change.So we need to again change the package configuration settings for the deployment purpose. To avoid all these confusions, we can use the Package Configuration files for the configuration settings. Now if we move the package from one place to another place we don’t need to modify the package, simply we can edit the configuration package.

How can we create the package configuration file in SSIS package?

SSIS provides the following package configuration types.

1. XML Configuration File -  An XML file which containing the configuration information.
2. Environment Variable -  An environment variable contains the configuration information.
3. Registry Entry - A registry entry contains  the configuration information.
4. Parent Package Variable – A variable in the package contains the configuration. This configuration type is typically used to update properties in child packages.
5. SQL server - A table in a SQL server database contains the configuration.

I am giving a small example for creating a XML Configuration File.

Creating XML Configuration file.
1. In the “Control Flow” tab, right click on the design surface and select the “Package configurations” from the pop-up menu.

2. Tick the check box “Enable package configurations”

3. Click the “Add…” button, to open the “Package Configuration Wizard”
4. In the “Configuration Type” combo box select “XML Configuration File”
5. Click “Browse…” to select a location for the XML configuration file
6. Specify a file name in the “Select Configuration File Location” dialog box and click Save.

7. Click “Next” in the package configuration wizard
8. In the “Objects” tree structure, tick the objects for which you want to generate the configuration file, and click “Next”
9. Provide a name for this configuration and click “Finish”.

Once a configuration file is generated for a connection, it can be reused in all other SSIS packages. This XML configuration file can be edited in Notepad or any XML editor, to change the server or database names. Once the configuration file is changed to point to a new server/database, the change will reflect in all SSIS packages that are using this configuration file.

The configuration will be as below..

<DTSConfiguration>
	<Configuration ConfiguredType="Property" Path="\Package.Connections[SERVERNAMEdbname].Properties[ConnectionString]" ValueType="String">
  		<ConfiguredValue>Data Source=[SERVERNAME];Initial Catalog=[DATABASENAME];Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;</ConfiguredValue>
  	</Configuration>
</DTSConfiguration>

How to logging in SSIS?

1. On the SSIS menu, click Logging.
2. In the Configure SSIS Logs dialog box, in the Containers pane, make sure that the topmost object, which represents the Lesson 4 package, is selected.
3. On the Providers and Logs tab, in the Provider type box, select SSIS log provider for Text files, and then click Add.Integration Services adds a new Text File log provider to the package with the default name SSIS log provider for text files. You can now configure the new log provider.
4. In the Name column, type Log File Name.
5. Optionally, modify the Description.
6. In the Configuration column, click <New Connection> to specify the destination to which the log information is written.In the File Connection Manager Editor dialog box, for Usage type, select Create file, and then click Browse. By default, the Select File dialog box opens the project folder, but you can save log information to any location.
7. In the Select File dialog box, in the File name box type TutorialLog.log, and click Open.
8. Click OK to close the File Connection Manager Editor dialog box.
9. In the Containers pane, expand all nodes of the package container hierarchy, and then clear all check boxes, including the Extract Sample Currency Data check box. Now select the check box for Extract Sample Currency Data to get only the events for this node.
   Note:If the state of the Extract Sample Currency Data check box is dimmed instead of selected, the task uses the log settings of the parent container and you cannot enable the log events that are specific to the task.
10. On the Details tab, in the Events column, select the PipelineExecutionPlan and PipelineExecutionTrees events.
11. Click Advanced to review the details that the log provider will write to the log for each event. By default, all information categories are automatically selected for the events you specify.
12. Click Basic to hide the information categories.
13. On the Provider and Logs tab, in the Name column, select Lesson 4 Log File. Once you have created a log provider for your package, you can optionally deselect it to temporarily turn off logging, without having to delete and re-create a log provider.
14. Click OK.