SSIS: SQL Server 2008

SSMS – SQL Server Mananagement Studio – enables SSIS packages, deploy Analysis Services solutions, and manage Report services and report servers.

allows you to perform:

  • manaing and organising SSIS packages
  • running SSIS packages
  • generating command line scripts
  • monitoring the execution of SSIS packages, and
  • importing and exporting of SSIS packages

BIDS – Business Intelligence Development Studio, holds the various elements that define workflow such as connections, control flows, data flow, variables, event handlers, and configurations.

  • Creating SSIS packages
  • creating supporting files
  • creating installation programs for SSIS packages
  • configuring automatic SSIS package updates, and
  • debugging SSIS packages.

SSIS provides several tools that allow you to copy and move data and oand objects to and from various data sources and locations – this is referred to as ETL – Extract, Transform and Load.

Perform ETL operations on SQL Server using:

  • Import/Export Wizard
  • Integration Services Connections Project Wizard
  • Package Configuring Wizard
  • Package Installation Wizard

Once the package is created, you will need to execute it using;

  • DTExec – /SQL, /FILE, /DTS
  • DTExecUI – UI for DTS execution
  • DTUtil – provides manage SSIS packages

to perform a variety of business tasks, SSIS uses the following components:

  • tools to create and debug packages
  • tasks that are used to perform workflow functions such as executing a SQL statement and email messaging
  • data sources and destinations to store and retrieve data used in ETL operations
  • transformations that are essentially different ETL operations such as cleaning, aggregating, merging, and copying data
  • a management service that is used to administer the SSIS packages, and
  • application programming interfaces, commonly known as APIs, to program SSIS object model

the following tasks that SSIS packages can perform:

  • merging data from hetrogenous data stores
  • populating databases such as data warehouse and data marts
  • automating administrative functions and data loadting
  • cleaning and standardizing data to ensure data consistency, and
  • building BI into a data transformation process
  • including scripts in packaging
  • executing Analysis services processes from a package
  • configuring e-mail alerts
  • supporting data-mining and WMI (Windows Management Instrumentation
  • supporting the processing of XML files, and
  • communicating with message queues and FTP sites

Enhanced in SSIS:

  • support for DTS
  • optimized data flow engine
  • lookup transformations
  • data type conversions, and
  • enhanced suppport for T-SQL

New features in SSIS:

  • new ADO.Net components
  • new Data Profiling features
  • Integration Services Connections Project Wizard, and
  • Microsoft Visual Studio Tools for Applications (VSTA)
  • SSIS package upgrade wizard
  • new date and time data types
  • change data capture, CDC, and
  • new debug dump files

You can use Import/Export wizard in SS2008 in addition to the option of creating packages programmatically.

Common components of a package are:

  • control flow objects – define the sequence in which the package is processed
  • data flow objects – data flow objects define the flow of data. they consists of source adapters, transformations, and destination adapters.

SQL Server 2008 uses SSIS to provide services such as:

  • package storage
  • package execution
  • logging
  • debugging
  • event handling
  • package deployment
  • management of variables
  • transactions, and
  • connections to packageds and their contents

SS2k8 relies on configuration file to store information related to SSIS – MsDtsSrvr.ini.xml located in Program Files\Microsoft SQL Server\100\DTS\Binn or use the registry key to locate the file – HLM\SOfteare\Microsoft\Microsoft SQL Server\100\SSIS\ServiceConfigFile. Following info’ is stored in settings:

  • execution of packages,
  • display of root folders, and
  • storage location of packages.

In most cases, editing to this files is not required however but in case of pointing it to remote instance, you need to modify it to the desired location else you might see errors during Object Explorer access in SSMS and in case of multiple instances.

Advertisements

About Jaggi
love technology, always updated on the latest and current happenings, seminars, tech.Ed, virtual days! Be Yourself!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: