How would you create a SSIS package based on below scenario?

Question

Want to insert a text file into database table, but during the upload want to change a column called as months - January, Feb, etc to a code, - 1,2,3.. .This code can be read from another database table called months. After the conversion of the data , upload the file. If there are any errors, write to error table. Then for all errors, read errors from database, create a file, and mail it to the supervisor.
How would you accomplish this task in SSIS?

Answer 1

Hi ,

have a data months names in the input file as source and while loading change the column in the source file and have another table named as months in the same data base or another .take derived column as transformation in that write a case statement for months after that take look up transformation in this take months table from another data base as look up table and do the data linking and pass the data to your destination table .