Hello, Good morning, good afternoon, good evening at what ever time you are reading this article welcome to learning tutorial of MSBI.
Apart from articles on MSBI we also have MSBI tutorial if you want to go deeper in MSBI request you to watch our MSBI videos where we have elaborated each topic with an real time example.
In our previous article we have covered conditional split component here in this post we are gonna discuss about Data Conversion transformation in SSIS with an example.
Data Conversion is a component of SSIS to convert data-type from one type to another type.Data Conversion means conversion of data-type of a column to another data-type before it reaches the destination of data-warehouse. If we want to convert any data-type of data which comes from source then Data Conversion is the best for such scenarios before loading it to destination.
Data comes from various sources in different formats, We will have different types of sources like csv, excel,notepad,sql all the formats we first take in ETL process i.e. Extracts, Transform and Loading. So when we extracts this data it is obvious that the data types used will not be the same, we may come to know that some data is in wrong format. Lets say a numeric data is shown as string type. This happens in SSIS many times during ETL process when we extracts data from txt file or CSV file.
So to correct this problem in SSIS we need to do Data-Conversion so that data loads properly to destination data-warehouse in the proper format.
To understand more better let's do an example on the same. Here we will unde our data-source as CSV file from where we will extract a data then before loading it Data-Warehouse i.e. SQL Server we will do the Data Conversion.
If you are new to #MSBI, request you to read our previous article on msbi installation, MSBI ETL process or you can our MSBI video tutorials.
Step 1
In this step we have created a simple CSV file as shown in below image.
Step 2
Now we will create a SSIS project
Step 3
Now just drag and drop Data Flow task in the control tab
Step 4
Double click on Data Flow task. It will take you to "Data Flow" tab
Step 5
Double click on Data Flow task. It will take you to "Data Flow" tab. Then in the "Data Flow" tab just drag and drop Flat File Source. This is because our Data Source is a CSV file as shown in below image
Step 6
Configure Flat File Source. To configure Flat file just right -> Edit -> Click on New Button -> a form modal will open -> click on browse button and load CSV file. You can check below image
Step 7
Our Flat File is ready, now we will check extracted data data-type. So to do that -> Just see below there is a Flat file connection manager as shown in below
Just double click on Flat file connection manager a new form modal open as shown in below image
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 CSVCustomerID or CSVCustomerAmount on the right hand side if you see there is a column called data-type in that it is showing that CSVCustomerID or CSVCustomerAmount data-type is string-type. i.e. string [DT_STR] as shown below.
Step 8
In this step we will create a simple sql database table as our data warehouse with fields like CustomerID, CustomerName, CustomerAmount and ProductQuantity as shown in below image.
In our SQL Datawarehouse CustomerID and CustomerAmount is of type i.e "INT" and "Money" respectively and our CSV File CSVCustomerID and CSVCustomerAmount showing data-type in string (DT_STR] format.
Both are incompatible means we cannot insert string data to int or money. We need to convert CSV File data types for making compatible and inserting properly.
Step 9
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.
Step 10
Just right click on Data Conversion -> Go to edit and change the data-types as per destination requirement i.e. Here we will make CSVCustomerID and CSVCustomerProductQuantity to four byte signed integer[DT_I4] and curency CSVCustomerProductQuantity to [DT_CY] as shown in below image. There are many other ssis-datatypes you can update as per requirement.
Once you have update data-types just click on OK button.
Step 11
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 "ADO.NET Destination" component from SSIS toolbox.
Drag and drop "ADO.NET Destination" component to Data Flow tab as shown in below image.
Just right on "ADO.NET Destination" component and configure the connection and mapping as shown in below image.
Step 12
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.
As you can see we have loaded data from CSV file -> then converted string to appropriate data-types - > to SQL Datawarehouse.
Hope you have enjoyed and understood how to implement Data Conversion in SSIS.
If you guys have any question or doubts feel free to ping us on below mentioned details.
Order only MSBI self-study learning video materials which are available with customized package costing 999 INR/15$. Click this link here to see details and order it.
