July 30, 2013 at 9:07 am
I'm fairly new at my position and one of the first assigned tasks was to convert an older DTS package to SSIS to automate the process. (Right now the DTS package requires user entry at a few points.)
The package must load all matching (on a substring) flat files from a folder, check against a table to make sure it's not a duplicate batch (based on BatchName), if not a dupe then load to table, update the BatchNumber in the table, and then kick out an email task to the appropriate parties that need to know the process has run.
Most of this seems fairly simple, I've set up the ForEach Loop and the FlatFile Source. I'm just curious as to the best approach to compare the BatchName (also the file name of the flat file) to the BatchName column in the table to check for duplicates.
In the old DTS they executed a stored proc that Validated the Batch Name, I've considered that but I've also thought of using a Lookup transform and the No Match Output. Another suggestion I was given was to load the files to a staging table then check from there.
What is the best/easiest means of performing this operation?
July 30, 2013 at 12:49 pm
If I think I understand properly, you need to use the info gleaned from the filename (inside the for each file container) and compare that against a table value, then based on a no-match scenario, load the data?
You may consider using an Execute SQL task as the first step in your loop. Pass the filename variable (that gets populated in the loop container) into the SQL query that hits the batch table. The query could looks something like this:
SELECT <myKeyValue>
FROM <Batch Table>
WHERE <filename> = ?
Then, pass the returned value (key value) into a variable. Use an expression on the precedence constraint that only moves forward in the loop when a key value is not found. You could do some sort of lookup in your data flow, buy by then, you've already incurred the overhead of reading in the file contents.
I'm sure there are another handful of ways to do this, but this is one that would work and be easy to maintain.
July 30, 2013 at 11:52 pm
John's suggestion is the way I would do it.
I would also have a process to move unwanted / processed / duplicate batch files somewhere else, to keep the 'to be processed' folder tidy.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply