SSIS Tutorial: Working with Connection Managers & Building Data Flows

http://www.accelebrate.com/sql_training/ssis_tutorial.htm

 

SSIS Tutorial: Working with Connection Managers

SSIS uses connection managers to integrate different data sources into packages. SSIS includes a wide variety of different connection managers that allow you to move data around from place to place. Table 16-1 lists the available connection managers.

Connection Manager	Handles
ADO Connection Manager	Connecting to ADO objects such as a Recordset.
ADO.NET Connection Manager	Connecting to data sources through an ADO.NET provider.
Analysis Services Connection Manager	Connecting to an Analysis Services database or cube.
Excel Connection Manager	Connecting to an Excel worksheet.
File Connection Manager	Connecting to a file or folder.
Flat File Connection Manager	Connecting to delimited or fixed width flat files.
FTP Connection Manager	Connecting to an FTP data source.
HTTP Connection Manager	Connecting to an HTTP data source.
MSMQ Connection Manager	Connecting to a Microsoft Message Queue.
Multiple Files Connection Manager	Connecting to a set of files, such as all text files on a particular hard drive.
Multiple Flat Files Connection Manager	Connecting to a set of flat files.
ODBC Connection Manager	Connecting to an ODBC data source.
OLE DB Connection Manager	Connecting to an OLE DB data source.
SMO Connection Manager	Connecting to a server via SMO.
SMTP Connection Manager	Connecting to a Simple Mail Transfer Protocol server.
SQL Server Mobile Connection Manager	Connecting to a SQL Server Mobile database.
WMI Connection Manager	Connecting to Windows Management Instrumentation data.

Table 16-1: Available Connection Managers

 

 

SSIS Tutorial: Building Data Flows

The Data Flow tab of the Package Designer is where you specify the details of any Data Flow tasks that you've added on the Control Flow tab. Data Flows are made up of various objects that you drag and drop from the Toolbox:

• Data Flow Sources are ways that data gets into the system. Table 16-5 lists the available data flow sources.
• Data Flow Transformations let you alter and manipulate the data in various ways. Table 16-6 lists the available data flow transformations.
• Data Flow Destinations are the places that you can send the transformed data. Table 16-7 lists the available data flow destinations.

Source	Use
DataReader	Extracts data from a database using a .NET DataReader
Excel	Extracts data from an Excel workbook
Flat File	Extracts data from a flat file
OLE DB	Extracts data from a database using an OLE DB provider
Raw File	Extracts data from a raw file
XML	Extracts data from an XML file

Table 16-5: Data flow sources

Transformation	Effect
Aggregate	Aggregates and groups values in a dataset
Audit	Adds audit information to a dataset
Character Map	Applies string operations to character data
Conditional Split	Evaluates and splits up rows in a dataset
Copy Column	Copies a column of data
Data Conversion	Converts data to a different datatype
Data Mining Query	Runs a data mining query
Derived Column	Calculates a new column from existing data
Export Column	Exports data from a column to a file
Fuzzy Grouping	Groups rows that contain similar values
Fuzzy Lookup	Looks up values using fuzzy matching
Import Column	Imports data from a file to a column
Lookup	Looks up values in a reference dataset
Merge	Merges two sorted datasets
Merge Join	Merges data from two datasets by using a join
Multicast	Creates copies of a dataset
OLE DB Command	Executes a SQL command on each row in a dataset
Percentage Sampling	Extracts a subset of rows from a dataset
Pivot	Builds a pivot table from a dataset
Row Count	Counts the rows of a dataset
Row Sampling	Extracts a sample of rows from a dataset
Script Component	Executes a custom script
Slowly Changing Dimension	Updates a slowly changing dimension in a cube
Sort	Sorts data
Term Extraction	Extracts data from a column
Term Lookup	Looks up the frequency of a term in a column
Union All	Merges multiple datasets
Unpivot	Normalizes a pivot table

Table 16-6: Data Flow Transformations

Destination	Use
Data Mining Model Training	Sends data to an Analysis Services data mining model
DataReader	Sends data to an in-memory ADO.NET DataReader
Dimension Processing	Processes a cube dimension
Excel	Sends data to an Excel worksheet
Flat File	Sends data to a flat file
OLE DB	Sends data to an OLE DB database
Partition Processing	Processes an Analysis Services partition
Raw File	Sends data to a raw file
Recordset	Sends data to an in-memory ADO Recordset
SQL Server	Sends data to a SQL Server database
SQL Server Mobile	Sends data to a SQL Server Mobile database

Table 16-7: Data Flow Destinations