Question: This is my first post and am new to programming so please excuse my novice mistakes in asking a question:

I have two files in the tabular format:

`IDs.txt` is just a list of IDs that looks like this:

```
1
2
3
4
7

```

`dictionary_FE.txt` is a dictionary of sorts with 2 columns that are tab delimited and looks like this:

```
1    a
2    b
5    h
7    g

```

where a key is associated with a value. I would like to write a python code to get me a new file that takes the IDs in `IDs.txt` and looks them up in `dictionary_FE.txt`. Not all the IDs in `IDs.txt` will be in `dictionary_FE.txt`. I would like a file that looks like this:

`File3.txt` as output:

```
1    a    
2    b
3    'not found'    
4    'not found'    
7    g

```

The following is what I have written:

```
#!/usr/bin/env python

infile1=open("dictionary_FE.txt", "r")
infile2=open("IDs.txt", "r")

F=infile1.readlines()

for item in infile2.readlines():
    item=item.strip()                       
    for line in F:
        line=line.strip()
        line=line.split('\t')
        ide=line[1]
        if item == ide:
            print line[0]+"\t"+item

```

I get a blank output.

Answer: 
One the one hand, when reversing engineering and understanding a package, it can get annoying to have to switch to the database tool all the time to work out what some code is doing. It can also be a bit complicated to have to deploy the right database object with the right SSIS package.

On the other hand, embedding code in the database is a good way to reuse code and importantly be able to fix and maintain data flow without having to open up a package (and possibly have it choke when metadata has changed etc.)

Personally one thing that annoys me is over-complicated packages containing unions, joins, lookups etc which could be much more simply written as a single SQL query. Often packages are built that way because the author doesn't know any better, and my opinion is that an ETL author should know how to write and use SQL.

Lastly, some of the SSIS components just aren't good enough, i.e. the row by row update component and the lookup component that for some reason has to do row by row lookups if your lookup key uses a range (i.e date range for an SCD)

So I don't have any best practices for you, just observations. The best practice really is be consistent and build ETL that is suited to the maintenance team skills.
The answer from ElectricLlama is very nice and as an ETL developer with a heavy preference for SQL I can definitly get with his way of thinking. I do have 1 very practical addition that can help.

Practically every component that takes SQL command can be set up to use a script as a file. This makes it possible to sort, organize and review the SQL code outside of the package. When adding some context to the top of the script in a comment this really helps. 

This way any cowboy behavior is discouraged and brought to the front if it does happen.