SSIS Import with IF / THEN Expression (Or an T-SQL Script)
I am currently working on a project and I need to create a temporary solution (for about 6 month) until our consultation group is finished with another project and impliments the needed code fix on the back-end system.
Here is what I am trying to accomplish.
I have a Text file that is dropped into an FTP site. I want to grab the Text file, import it into a folder then run an SSIS process to correct a column.
I need to have the SSIS process look at the column labeled [jobkeyid], if the [jobkeyid] is 65000100 or 65000101 then I want to temporarily capture the [userid], Then I want to look in the [managerid] column, if the temporarily captured [userid] is found then I need to swap it with the [approverid].
once this is done then I will replace this file in the FTP so that a separate system can parse the data for a permissions approval table in an online training system we are launching.
Here is a view of the txt file (put into Excel to make it easier to understand).
I would like to do this in SSIS but if there is a clean T-SQL script that I could implement that would also be good as this is just a temporary solution.
Ah the good old "temporary solution" - if I had a dollar for every ...
You can use an upstream Data Flow Task to load the managerid and approverid into a Lookup Cache. By default it will ignore duplicates and use the first row for each key (managerid).
Then in the main Data Flow Task, I would use a conditional split to separate the jobkeyid 65000100 or 65000101, then add a Lookup (using the cache loaded above) to get the new userid. Then I would add a Derived Column transform to overwrite the userid.
Then a Union transformation would pull all the rows back together before delivering them to the output file.
For the FTP tasks I would call WinSCP - this is more reliable and has more functionality than the generic SSIS FTP task.