We observe this information in “Progress tab” or “Log Providers”
(a) Progress tab information: It describes how the package validated and executed step by step from starting to ending. Generally it displays
The no. of rows operated
Source and destination connections
The amount of time taken b/w one statement to another statement etc
(b) Log Providers: discussed later in this book
It is the value which is changeable within the package. There are two types of variables they are
1) System defined variables: These variables hold system information.
These variables store under SYSTEM
Syntax:SYSTEM: : < variable Name>
Ex: SYSTEM : : PACKAGE NAME
SYSTEM: : Execution ID
2) User Defined Variable: These variables are created by the user only. These variables store under user name space
Syntax: USER : : i
USER : : Name Var
USER : : Temp_date ..etc
Navigation:SSIS Menuàvariablesà in the variables window click the top most left corner option to create new variable.
Name Scope Data Type Value
l Package Int 32 1
Variable scope: The extent we use the variable is called the scope of variable. There are 2 scopes
a) Package Level: Within the package anywhere we can use this variable
b) Task Level: Within the task only we can use the variable.
FREE Demo Class From Industry Experts - Enroll Now.!!MSBI training
Working with Data Flow Task
To move the data from source to destination and to perform intermediate operations this task is mandatory. Frequently used sources are flat file source and OLE DB source and frequently used destinations are flat file destination and OLE DB destination.
Real time modes of flat file sources:
123 VINAY 123 VINAY
456 SIVAM 25 KISHORE
Fixed width Delimited (separated or Variable text)
ID –> 3 chars ID à Max 10 char
Name –> 5 char Name à Max 30 char
Row Delimiter:- ‘|’(pipe)
Column Delimiter:- ,(comma)
Row Delimiter:- CRLF(enter char) [carriage return line or forward]
Column Delimiter:- No
Eg:- Moving data from file to file (comma delimited)
Sol:- 1. Take data flow task on control flow
2. Go to data flow task à drag and drop flat file source & destination and do the below settings.
INPUT: Party_3RC –> notepad
File name: Party_3RC
No of rows: 13
–> Connection Manager Name is reusable in the packages.
These are intermediate operations performed between source and destination.
Eg: concatenation, addition, sorting, merging etc.
There are different data flow transformations provided for different operations.
It sorts the data in the specified order (ascending or descending)
It has some flexibility to do sorting on multiple columns (by giving sort order)
There is a feature called “remove rows with duplicates” which helps us to display unique rows in sorting by eliminating duplicates.
Eg: display the data by location in ascending order, with in the location names in descending order.
–> Rc –> EDIT Available input columns –> select columns
Derived column transformation:
It performs operation row by row. It does different calculations, aggregation, concatenation, transformations, conversions etc for the columns in the rows.
a) Display name and location by concatenation.
b) Display income, if it is null 99999.
c) Display income increment by 12%.
d) Display a new field with current date as business date.
e) Display default company code as 21000.
I. When we retrieve the data from flat file, all the columns belong to string data type (DT-STR).
II. When we retrieve the data from excel sheet, all the file it’s belong to (DT-WSTR).
Data conversions are done in three ways.
a) By using data conversion transformation.
b) By using type cast operator in expression.
c) Directly doing at flat file source itself.
Rt click on flat file source –> show advanced editor –> input and output properties –> flat file source O/P –> output columns –> party income –> data type properties –> data type →four-byte signed integer(DT-I4)
It performs aggregate operations such as average, sum, count, min, max, count distinct, group by
If the field or expression is of numeric data type we can perform the above all the operations.
If the field is string or data, we perform limited operations group, count, distinct count etc.
Eg: display location wise income sum and average.
Flat file source addition properties:
àRetain null values from the source as null values. It helps us to treat nulls coming from the source as nulls only. (if we uncheck this option null treated as zero for integers, space for string)
Error output options:
In case of error or truncated values coming from source we can use either of the below options.
a) Ignore failure → in case of error or truncations it ignores the failure.
b) Redirect row → in case of error or truncation it redirect the row to the another destination.
c) Fail component → in case of error or truncation it simply fails the component.
Flat file source àrtclickàeditàerror O/P.
Flat file destination files format:
Delimited àthe columns are delimited by commas, expect the last one which is delimited by the new line character.
Fixed widthàthe columns are defined by fixed widths.
Fixed width with row delimiters àthe columns are defined by fixed width. An extra column, delimited by the new line characters is added to define row delimiters.
Ragged right àthe columns are defined by fixed width, expect the last one which is delimited by the new line character.
OLEDB Source properties:
OLEDB Source àrtclickàeditàdata
Table or view
Table name or view name variable
SQL command from variable
Note: we use variables for dynamic source retrieval.
It creates multiple copies of some source. So that instead of doing multiple operations on the same source in multiple package we can do in a single package by using multicast.
→ It improves the performance because it reads the data only one time from the source.
Package-1 package-2 package-3
EMP EMP EMP
Three buffers for source
Multiple read on source
Advantages of single package split
Source read only one time
Single time buffer occupation
It Merges multiple input data source here the restriction is the sources should be the sorted order so that the output will also be in the sorted order.
There are two ways to implement
If the source are not in sorted order do the below process
Conditional split transformation:
a) It splits the data based on the condition.
b) There are two types of output comes from this transformation.
Conditions matched output.
Conditions unmatched output or default output.
Eg: move HYD, Bang, data to separate files and unmatched data to another file.
→ Conditional split → rt clickà
ORDER OUTPUTNAME CONDITION
1) HYD-DATA [PARTYLOC]=”HYD”
2) BLORE-DATA [PARTYLOC]=”BLORE”
Default output name → Unmatched-data → Ok
Union all transformation:
It merges multiple input sources (two or more)
No need to take the input in the sorted order, so that the output will also have unsorted data.
1) Input sources structures should be same. [No.of columns, order of data type of columns]
OLEDB Source Editor Properties
Data access mode:
Table or view → to retrieve the data from table or view
Table name or view name variable → table name or view name takes from the table
SQL command → we write a customized query to retrieve the data from the object, so that required rows and column retrieved and occupies less buffer every time. Eg: select Party ID, Party Name from Party where Party code in (20, 40, 60)
SQL command from variable → we pass SQL command from a variable.
Note: this variable generally recommended at the time of dynamic retrieval of data.
OLEDB Destination Additional properties
Data access mode:
i) Table or view
ii) Table or view-fast load → it loads the data very fast compared to normal view or table loading.
During the fast load there are couples of options we must select according to the situation.