Description:-
I am sure by the name it clear
that this control will be use when we require data type conversion of input
columns then we need this control. This is exactly same like CONVERT or CAST in
our programming.
Although, here it would be important
to share SSIS also have data type which are similar to other programming or
database’s data types Like DT_NUMERIC is
equivalent to numeric data type, DT_I2 is
equivalent to smallint, DT_str is equivalent to VARCHAR and so on.
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. 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.
Here in the flat file I have CourseID
is numeric, Title is in varchar and Credits is in numeric with decimal 4 points. We want to convert the numeric field
in integer and want result in a flat file. So let’s start.
Drag drop a Data flow Task control and double click it.
Now add flat file source from SSIS tool bar.
Now next step is to configure this flat file source. So to do this just
right click and use EDIT option. You will get a screen where you need to
provide the file which we want to access as
shown in below figure.
If you see on left hand side
there is a small menu -> Just click on "Advance" -> a new
prompt will open as shown in below image.
Choose CourseID, Title or
Credits on the right hand side if you see there is a column called data-type in
that it is showing that CourseID, Title or Credits data-type is string-type.
i.e. string [DT_STR] as shown below.
Once the file is configured you can choose the columns as
well which you want to process or want in destination by click column’s option
in as shown in below figure.
In this step we will create a
simple Sql database table as our data warehouse with fields like ID, CourseID,
Title and Credits.
CREATE TABLE [dbo].[Course](
[CourseID]
[int] NOT NULL,
[Title]
[nvarchar](max) NULL,
[Credits]
[int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
To transform data-type according to destination. We need to add up a
SSIS component i.e. Data Conversion in between of Source and Destination. So
just drag and drop Data Conversion component to the Data Flow tab as shown in
below image.
Just right click on Data
Conversion -> Go to edit and change the data-types as per destination
requirement i.e. Here we will make CourseID and Credits to four byte signed
integer[DT_I4] and Title to Unicode string [DT_WSTR] as shown in above image.
There are many other SSIS-datatypes you can update as per requirement.
In this step we need to add Destination
component and here in this example our destination is SQL Database so for that
we will select "OLE DB Destination" component from SSIS toolbox.
Drag and drop "OLE DB
Destination" component to Data Flow tab and configure and mapping it with
the data source table.
Final step, Save everything once and just click on start
or press F5 key to run the process.
If you saw on above image we
have ran the process successfully. Let's check our database if data loaded
properly or not.
Thanks for comments.....