Event handlers in SSIS?

Events of various types occur one after another from the time you start to execute a package till the time it completes successfully or otherwise. The OnError event, which is raised when an error occurs, is the default event in SSIS. In addition to the package, various components such as for loop, task host containers, etc. can raise events. Events can be raised by other executables such as For loop, and Task host containers. You can create a custom event handler that traps these errors for some useful purpose. The below are the available event handlers which are visible in the Package Design Event handlers Page.

Containers in a package follow a hierarchy. If the event in a container is not handled, it can escalate the handling of the event to a container higher up in the hierarchy, quite like events on a web page which also have a container hierarchy. If there are no event handlers configured at any of the containers, the event never gets handled.
Read the rest of this entry »

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.
Read the rest of this entry »