June 30, 2008 at 7:21 pm
How would I add a update statement in my transformation:
I have OLEDB Data source that is pointed at Database 1 which is using a SQL Statement for it population.
Code:
Select b.BeeNumber,b.BeeEntityGuid, e.FirstName + ' ' + LastName as ConsultantName,
h.* from HSTBucketAmount h
Inner Join BeeBusiness b ON b.BeeBusinessGuid = h.BeeBusinessGuid
Left Outer jOin BeeEntity e On b.BeeEntityGuid = e.BeeEntityGuid
In my OLEDB Data Destination I have a field called CurrentLevelXID which I need to Update with the following code:
UPDATE consultant.consultant
SET CurrentLevelXID = b.XID
FROM consultant.consultant a
LEFT OUTER JOIN shareddimension..DimConsultantTitle b ON a.ComplevelID = b.TitleAbbrev collate Latin1_General_CI_AS
I was told to use a Lookup task but I don't know how to do that. Please help
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
July 24, 2008 at 12:22 pm
Art
For the OLE DB Destination task, you cannot do updates, that task will only perform inserts into your destination table. The task that would do updates is the OLE DB Command task however the problem with this task is that is goes through record by record in the source to perform an action. As you can imagine, if you have a large data set to work with, this task can take a while to perform. One way to get around this is to put the data into a permanent temp table, using the OLE DB Destination task, and then in the Control Flow, use a Execute SQL task to perform your update via a SET statement.
Regarding the Lookup task, what you will get from that task is looking up information based upon a join from your dataset to an existing table or query. However, one thing to lookout for in the transform is if the lookup is not found, the record will go to the error output which you would have to handle.
From your queries, I do not see how you would be getting the Level Id into your dataset, can you explain on what is supposed to happen in your mind for this process?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply