January 15, 2013 at 7:07 am
Hi guys,
I'm sure this one is pretty easy, but I', not sure which components to use
I have a flat file - pipe delimited CRLF record end
123456|Some Text
456789|Some Text
The 1st field SHOULD be numeric and the second field should always contain the same text on each line in a particular file
I need to do the following (assuming the file contents are clean):
Create a new header record (with an identity field) and feed the text into one of the columns. This will be an SQL sproc that takes the text and returns the @@Identity to a package variable. This one I know 🙂
For each line in the file I need to validate that the number exists in one data set (use a lookup task?) and that it does not exist in another data set (again use a lookup?)
If it passes both validations I need to write it to a child table that has the identity from the header table and the value from the 1st field.
However I would also like to prevalidate the file if possible:
Check that the text is the same on each line and abort the process if it is not
Check that each record will pass the lookups and abort the process if not
Speed is not particularly an issue: the file it unlikely to be more than 2000 lines long and as long as I can process it in a couple of minutes that will be acceptable. If I need to process the file twice, once to validate and once to process that is OK but I am hoping for an elegant one-pass solution
Obiron
January 15, 2013 at 7:18 am
The fact that you need to check all of the rows in the file before deciding whether to commit any of them makes a one-pass solution a little difficult.
I'd be considering something along these lines:
truncate staging table;
import data to staging table;
validate data;
if (validated)
perform update
else
suitable error;
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 15, 2013 at 10:32 am
Staging tables?
What is this - SQL7? 😛
January 15, 2013 at 1:22 pm
aaron.reese (1/15/2013)
Staging tables?What is this - SQL7? 😛
I would like to hear your reasons behind this comment. In many circumstances, a staging table SSIS solution will be one or more orders of magnitude faster than a pure SSIS solution. These circumstances often have one thing in common - the fact that some sort of set-based processing is required - the SQL engine generally thrashes the SSIS engine when it comes to sorting, grouping and joining. Your requirement has some of these elements and that is why I proposed staging the data.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply