SSIS Terminologies quick review .

Data Flow Task: used to move the data from source to destination and to perform intermediate operations

Transformations: These are intermediate Operations performed between source and destination

Ex: Concatenation, Addition, Sorting, Merging etc

Sort: used to sort the data in the specified order i.e. ascending or descending. It has some flexibility to do sorting on multiple columns.

There is a feature called “remove rows with duplicates “helps us to display unique rows in sorting by eliminating duplicates.

Derived Column:

It performs operations row by row. It does different calculations, aggregations, concatinations, convertions etc for the columns in the rows.

Variable: It is the value which is changeable within the package

Variable holds memory and stores values in it.

User Defined Variables:

Holds system information. These variables store under system name space

Ex: system:: <Variable Name>

System::<variable name>

User Defined variables: created by the user only store under user name space

User::<variablename>

Package Level: within the package any where we can use the variable

Tasks Level: within the task only we can use the variable.

Data Conversions:

Data Conversions are done in 3 ways

1.       By using data conversion transformations

2.       By using type cast operations in expressions

<Type Cast Operator> (column -  Name)

(DT_I4)(PartyIncome)

(DT_DBDATE)(‘2010-10-10’)

3.       Directly doing at flat file itself

RC on Flat File Source à Show advanced editor à Input and Out Put properties à Flat File Source out Put à o/p columns à Party Income à Data Type Properties  à Data Type à four – byte signed integer (DT_I4)

Note: If we are using same column nof time in middle instead of converting multiple times directly do at flat file source itself.

Aggregate:

                 It performs aggregate operations such as Average, min, sum, count, distinct etc

FlatFileSource à Data Conversion  à Aggregate    à FlatFileDestination

Multi Cast:

It creates multiple copies of same source so that instead of doing multiple operations on the same source in multiple packages we can do in a single package by using multi cast.

It improves the performance because it reads the data only one time from the source.

Performance point of view this component is very important.

Merge:

It merges multiple input data sources. Here the restriction is source should be in sorted order so that the output also be in the sorted order.

Note: it will accepts only 2 inputs.

Conditional Split:

It splits the data based on the condition. There are 2 types of output comes from this transformation

a.       Condition matched out put

b.      Condition un-matched output or default output.

Union All:

It merges multiple input sources i.e. 2 / more

No need to take the input in the sorted order so that the output will also have Un sorted data.

Limitation: In put source structures should be same (i.e No.of Columns , Order of data type of columns)

Note: It doesn’t bother about column – names.

Merge Join: It performs merge operation along with joins. Generally it supports only Inner, Left and Full Joins.

Look Up: It looks up the required value on target and fetches relevant results (Exact Result)

Adv:

1.       To fetch relevant data

2.       While working with SCD’s

3.       To have exact match with destination and to improve query retrieval fast (it uses cache)

Fuzzy Look Up:

Used to get the result from the destination based on the similarity but not exact match

For Loop Container:

 It executes the underlying task to the specified no of times.

For Each Loop Container:

Used to load group of similar objects whose count is “unknown “

Note: For Loop having a condition so that we know the count where as for each loop doesn’t have.

Loading similar files from a folder to a table etc.

It uses enumerator that supports are

                                For Each File enumerator

                                                Item

                                                ADO

                               

Note : Enumerator values are not changeable with in a Package.

File System task:

It performs file and folder operations such as copying, moving, deleting, creating …etc

Bulk Insert Task:

It loads bulk data with max speed into tables.

We cannot perform any intermediate operations with bulk insert task

Note: It loads files only i.e before loading into the table, table should already create.

Send Mail task:

                 Used to send e-mains to corresponding recipients. It requires SMTP server.

Maintenance Clean up task: it removes file left over from a maintenance plan

Notify Operator task: it sends an e-mail ms to any SQL server Agent.

Rebuild Index task: used to reorganizes data on the index by rebuilding the indexes. It improves the performance of index scan and seeks

Reorganize Index Seek: it defragments and compacts clustered and non-clustered indexes on table and views.

Shrink Data Base Task:

It reduces the disk space consumed by data base and log files by removing empty data and log prices.

Update Statistics Task: it updates statistics of the object if there are already collected.

Character Map: It applies string operations such as lower to Upper vice versa etc.

Copy Column: It creates multiple copies of the columns

Import Columns: it imports into columns from a file

Ex: Loading images from file to table rows.

Fuzzy Grouping:

                 It groups similar rows

OLEDB Command: it executes an SQL Command for each row in a data set

Row Count: It counts no of rows in a data set

Term Extraction: It extracts terms from a data to column

Term Look Up:

                It counts the frequencies that terms in a reference table appears in a data set.

Check Point:

Check point provides better restorability of package

Check point configuration helps us to resume from the last task in the package i.e if multiple tasks are there in a package  if there is any failure in any task it stores the fail task point in the check point file , once we restart the package  then check point helps to start from last point specified in check point file . once package is succeed then it deletes the check point file.

Error out Put Options:

In case of error or truncated values coming from source we can use either of the below options

Ignore Failure: in case of errors or truncations it ignores failure

Redirect Row: In case of error (or) truncation it redirects the row to the other destination

Fail Component: In case of error (or) truncation it simply fails the component