January 30, 2008 at 4:35 am
I'm used to using DTS and have attempted to move over to SSIS. However I can't figure out this lookup stuff.
I have a tab delimited flat file with several columns, such as short name, full name, dob, clock no. I've created this against a flat file datasource. I am trying to import this into a dbo.[STAFF] table.
All I want to do is some kind of check, so that if the full name field from the flat file is the same as the SURNAME field in the STAFF table of SQL, to return the primary key (STAFFNO) so that I can de-dupe.
Can anyone offer some help on this please? I'm really stuck on this.
thanks
January 30, 2008 at 7:17 am
Have you tried using: Data flow, flat file source and the lookup component in SSIS?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
January 30, 2008 at 9:54 am
I have managed to get it to insert new records that don't already exist now. Using
Data Flow with Flat File Source -> Lookup that checks record exists and on failure redirecting to an OLE Db Destination. Based upon a blog here : http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx
That blog doesn't show how he gets the data from the Union Alls into a database though.
Now I need to find a way of handling the ones that are matched and there need to write an update statement which I can't seem to find a way to do.
i.e.
UPDATE STAFF SET
SURNAME = ?,
STAFF = ?,
DOB = ?
WHERE STAFFNO = ?
The ?'s being the data from flatfile.
January 30, 2008 at 9:59 am
Your syntax is correct. Use the OLE DB component.
Rather than redirecting the not found rows, rather ignore them and use a conditional split. It's a cleaner option.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
January 30, 2008 at 10:03 am
Okay thanks, will try the conditional split. I'll switch them from fail/pass lookup to conditional switch.
But I still don't know how to update a record though. I can't see a way of mapping which columns to update. If I map the one's that are duplicates to a OLE Db it tries to insert those duplicates rather than updating them which causes primary key issues.
I think once I understand how to update a record rather than insert I can do most of what I'm trying to do.
January 30, 2008 at 10:15 am
Well, in your lookup you are obviously mapping on the key. So you have the correct values in the source (else it would be deemed missing and become an insert)
So,
Update MyTable Set
Col1 = ?,
Col2 = ? etc
Where
Key1 = ?
and Key2 = ?
In the mappings tab, map the col1 to param 0, col2 to param 1, key1 to param 3 etc.
Make more sense?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
January 30, 2008 at 10:27 am
I've just seen that in the Ole Db Command component, very nice. It's a shame it's not easy to identify which param_0 is when mapping them etc. Gotta remember the sql script and the order of them, but it was the same in DTS you had to remember the order of ? yourself.
Thanks, haven't tried the conditional split, still using lookup at the moment and it works. But will try the conditional later.
Most of my stuff is gonna be simple de-duping like this. And linking order lines to order header records etc. 🙂
Nothing complex just yet. Thanks for the help though!
January 30, 2008 at 12:37 pm
Use a proc for the update. Advantages are plan cache, cleaner oh, and it shows you the param name. Not Param0 etc. Will show you MyParamName 🙂 But mostly for the neatness / plan cache etc.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
February 1, 2008 at 3:18 am
Thanks for all the help so far.
Conditional Splits... You'd recommend Source -> Lookup -> Conditional => Outputs ? With the error outputting of the lookup set to ignore errors?
Edit: Nevermind, I've got it. This is getting much better to work with now.
I think I just need to look at Transformations next and work out how to include SQL functions in the input source (i.e. a few getdate() columns). Or use OLE Db Command for both insert and updates.
February 1, 2008 at 3:56 am
nooooooooooooooooooooo Do not use the OLE DB component for inserts.
It'll be a dog. SQL is set based. Rbar (Pronounced ReeBar) is Row By Agonising Row.
The OLE Source and OLE Component will accept any (most) valid SQL syntaxes. You best bet is to use a proc all the time (for the OLE DB component) and Select col1, col2 from a view / table for the source. This goes for lookups as well.
Do NOT use the Table / View source and select a table / view. This is bad. Slow. Will bite you. Again, holds true for lookups. Also, obviously, do not use select * either.
You understanding on the lookup / ignore failure is correct. Cleanest / simplest way.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply