February 15, 2011 at 3:08 pm
I am creating an SSIS package that pulls data from a flat file. One of the columns in the flat file is "SalesID".
In the database there is a table with a list of Specialty SalesID #'s.
During the import, I need to check if the SalesID # in the flat file exists in the list of Specialty SalesID's. If it does then I need to insert "Y" into a different column in the destination table. If it does not exist I need to insert "N".
I am having trouble figuring out how to do this. Does anyone have any tips?
Thanks
Jordan
February 15, 2011 at 3:10 pm
Triality (2/15/2011)
I am creating an SSIS package that pulls data from a flat file. One of the columns in the flat file is "SalesID".In the database there is a table with a list of Specialty SalesID #'s.
During the import, I need to check if the SalesID # in the flat file exists in the list of Specialty SalesID's. If it does then I need to insert "Y" into a different column in the destination table. If it does not exist I need to insert "N".
I am having trouble figuring out how to do this. Does anyone have any tips?
Thanks
Jordan
Use a lookup component to match the SalesIDs, and choose to ignore errors on the lookup. This will pass out the Specialty SalesID as a value or NULL. Next component, use a derived column, and test the Specialty SalesID item for a NULL value. If yes, return an 'N', if not null, return a 'Y'.
Feed this derived column into your final destination.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 15, 2011 at 5:14 pm
Ah thats exactly what I was looking for, makes complete sense! Thanks Craig!
February 15, 2011 at 5:29 pm
Triality (2/15/2011)
Ah thats exactly what I was looking for, makes complete sense! Thanks Craig!
My pleasure. I've been crash coursing in more advanced SSIS for the last two months or so, so it's nice to be able to turn around and use it to help others relatively quickly.
If most of the other T-SQL heavy folks saw what I was up to at the moment I fear I might be shot. 🙂 I wonder if 30+ objects in a DataFlow is considered naughty... especially if you've got multiple multi-casts and union all operators going. Sorry... rambling.
Good luck!
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 15, 2011 at 8:32 pm
Haha, how is the performance on 30+ objects in a data flow? I am importing ~15MM rows per month with only about 4 derived columns and it is taking about a half hour. Seems like that might be brutally slow!
February 15, 2011 at 11:38 pm
Triality (2/15/2011)
Haha, how is the performance on 30+ objects in a data flow? I am importing ~15MM rows per month with only about 4 derived columns and it is taking about a half hour. Seems like that might be brutally slow!
Not as bad as you might expect. Preload doesn't take long to cache the lookups because I've tightened the SQL Commands to minimal levels in regards to columns and intelligent where clauses, and I've got no blockers (sorts, merge joins, etc) involved. Most of those objects are just to produce warnings or errors to kick the row out of the process on certain failures, such as missing foreign keys or in other cases the new value already exists.
Since each row succeeds/fails on its own, it really doesn't take much longer than it did without it, and it saved me multiple passes on the data in the staging table after the fact. The Multi-cast/re-Union routine is primarily because just prior to that I do data splits based on information that I want to kick warnings out on, but still process, to notify end users of certain things. Such as telling me something's an update but is actually new. I still want to insert the record, I just make sure I have a note going back to the user for those that aren't actually updates so they're aware.
That's not the actual case here but it makes more sense out of context than what I'm really doing, because that gets complex fast from a business logic point of view.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply