We are getting the text file in that we are getting the employee details in the file we have CID, employee name and month Id. Our requirement is to load this data to the database with the month mane.
1. January
2. February
3. March
4. April
5. May
6. June
7. July
8. August
9. September
10. October
11. November
12. December
Our text file is as below
Our expected output is like below
There are multiple ways to achieve this using SSIS transformation like
· Drive column transformation
· Using lookup transformation
· Cache Transformation
Here I am using Drive column transformation.
Taking the data flow task.
In Dataflow task I am taking Source as a flat file and creating and configuring the flat file connection.
Now I am taking Drive column transformation and creating a new column named Month
In expression we need to write the below expression.
(DT_WSTR,20)MonthID == "1" ? "January" : (DT_WSTR,20)MonthID == "2" ? "February" : (DT_WSTR,20)MonthID == "3" ? "March" : (DT_WSTR,20)MonthID == "4" ? "April" : (DT_WSTR,20)MonthID == "5" ? "May" : (DT_WSTR,20)MonthID == "6" ? "June" : (DT_WSTR,20)MonthID == "7" ? "July" : (DT_WSTR,20)MonthID == "8" ? "August" :(DT_WSTR,20)MonthID == "9" ? "September" : (DT_WSTR,20)MonthID == "10" ? "October" : (DT_WSTR,20)MonthID == "11" ? "November" : (DT_WSTR,20)MonthID == "12" ? "December" : "Not a valid Month"
|
Click Ok.
After that, we will take a conditional split to validate the Month Id. If the max month id is 12 if it is greater than we are storing these records in the table for troubleshooting.
Now come to configure error output.
And if an error occurred we are redirecting the rows in the other table.
Click Apply and then ok.
Now I am taking two oledb destinations in one table I am storing the good records and in another table, I am storing the bad records.
Now it is ready to run.
Before running this package Records in the table.
Now running this package.
Now check in the database.
Hope it will help you!
