May 14, 2010 at 5:04 pm
I am an SSIS developer who is currently forced to deal with Informatica. However, Informatica has gotten on my final nerve, so I am trying to simulate a bit of Informatica functionality in SSIS 2005.
Informatica has something called a "file list" for flat file sources. That way, Informatica can import data from several flat files into one dataset all at once. I know that SSIS has a For each Loop container that is similar. However, there is a twist. In Informatica, I build the file list and then check it against a look up tranformation consisting of "bad" files that I am to exclude. So basically, I import the file list (which is itself a text file), check it against the "bad" file lookup transformation. Where file list item does NOT equal bad list item, the file list item goes on to a new text file, basically called "FileListMinusBad.txt" How can I do this in SSIS 2005 so I won't have to deal with Informatica for this task?
May 19, 2010 at 2:03 am
I think you can do pretty much the same with SSIS.
Read the file and perform a lookup on a lookup table containing the "bad" files.
Redirect your error output to a destination. Discard the lookups that succeeded, because those are the bad files.
Then use a for each loop to loop over the good files. You have multiple choices for a destination for the good files. A table, a new flat file or even a recordset (which is easy to use within the for each loop).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 19, 2010 at 2:08 pm
What da-zero said. In fact, I prefer using a table whenever possible since it scales much better and you don't have to worry about someone deleting files or changing the markup of the file.
May 19, 2010 at 2:21 pm
I fully agree and I took a similar approach. Thank you very much!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply