Sort Transformation

The Sort transformation sorts input data in ascending or descending order and copies the sorted data to the transformation output. We can apply sort on multiple columns of the input data. 

Take data flow task

Double click on data flow task.

In Data flow I am taking Excel data flow source.

This is my excel sheet which I want to sort.

Make the excel connection. After that I have take data conversion transformation.

Now I am taking sort transformation.

Double click on the Sort Transformation. Select the fields and also select the fields of the fields which you want to sort. Here I am sorting according to Employee ID. Sorting mode is ascending.

Click ok.

Sorting may be 1. Ascending order or 2. Descending order.

Comparison Flags: -

Ø  Ignore case: - Determines whether uppercase and lowercase letters are considered to be the same value.

Ø  Ignore kana type: - Determines whether kana type from the Japanese characters are ignored.

Ø  Ignore character width: - Determines whether single-byte and double-byte representations of the same character as identical.

Ø  Ignore non spacing characters: - Determines whether spacing characters and diacritics are considered identical.  For instance, whether “å” is equal to “a”.

Ø  Ignore symbols: Determines whether white: - space characters, punctuation, currency symbols, and mathematical symbols are considered in the sort.

Ø  Sort punctuation as symbols: - Determines whether the comparison sorts all punctuation symbols, except the hyphen and apostrophe, before the alphanumeric characters. For instance, if this option is set, “.ABC” sorts before “ABC”.

Now we take Oledb destination

Also I have take data viewer between sort and Oledb destination.

Now build the solution and execute the package.

Now see the sorted data according to the Employee ID.

Package Executed successfully.

Multicast Transformation

Multicast transformation is useful when we need to make many copies of same data or we need to move the same data to different destination. It has one input and many outputs. The Multicast transformation distributes its input to one or more outputs. Suppose I have one excel as source and want to migrate data into difference database like (Dev, QA, and Prod) for such type of scenario we need to use Multicast Transformation.

In Control flow Item take data flow task.

Double click on the data flow task.

In data Flow take excel as data flow source. Make the connection.

Now I am taking data conversion transformation.

Convert the columns  as per destination data type.

Click ok.

Now I am taking multi cast transformation.

After that I have three Oledb destinations.

Map the source and destination columns.

After completing the mapping build the package and execute the package.

Package Executed successfully.

Now see the result in database.

SSISTest DB

BEE DB result

EMP DB

Data Conversion Transformation

The Data Conversion transformation converts the data in an input column to a different data type and then copies it to a new output column. Data Conversion is used to change the data type of a column. For example, a package can extract data from multiple sources, and then use this transformation to convert columns to the data type required by the destination data store. You can apply multiple conversions to a single input column.

Create new integration project.

In control flow item select Data flow task.

Double click on the data flow task.

Here I am going to migrate data from excel to sql server. I means source as excel and destination is sql server. If source columns data types are not matched with target columns, some cases ETL will throw validation/failure error. Example: Source column is having “Varchar” data type and target is having “Integer” data type. In this case ETL will throw validation/failure error.

In data flow I am taking data flow source as Excel source. After that I make the connect.

Now we need to take data conversion transformation.

Double click on Data Conversion

Select the column name which one you want to change the data type.

A: - It will show the all available columns which is in source. Select the columns name which one you want to convert the data type.

B: - It will show the selected input columns name.

C: - It will show the Alias of the output column name.

D: - Here you can select the data type of the output column.

E: - you can define the length of the column.

 F: - After complete click ok.

Now take Oledb Destination.

Create the connection. While mapping the columns select the converted columns name.

Click ok.

Now Build the project and execute the package.

Package Executed successfully.

SSIS data type to SQL Server data type

SSIS data type to SQL Server data type

SSIS Data Type	SQL Server Data Type
DT_BOOL	bit
DT_BYTES	binary, varbinary, timestamp
DT_CY	smallmoney, money
DT_DBDATE	date (Transact-SQL)
DT_DBTIME2	time (Transact-SQL)(p)
DT_DBTIMESTAMP	datetime (Transact-SQL)
DT_DBTIMESTAMP2	datetime2 (Transact-SQL)
DT_DBTIMESTAMPOFFSET	datetimeoffset (Transact-SQL) (p)
DT_GUID	uniqueidentifier
DT_I2	smallint
DT_I4	int
DT_I8	bigint
DT_NUMERIC	decimal, numeric
DT_R4	real
DT_R8	float
DT_STR	char, varchar
DT_UI1	tinyint
DT_WSTR	char, varchar, nchar, nvarchar, sql_variant, xml
DT_IMAGE	image
DT_NTEXT	text, ntext
DT_TEXT	text