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.

Answers


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.


Need Your Help

Including id element, or variable, from another TWIG template

php twig templating template-inheritance

I have picked up the basics of using TWIG to create my site. I suppose I know how to use {%extend%} {%block%} {%include%} and {%set%}, in the most general sense.