SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Updating Rows in Incremental Loads – Level 4 of the Stairway to Integration Services Services


Updating Rows in Incremental Loads – Level 4 of the Stairway to Integration Services Services

Author
Message
dwilliscp
dwilliscp
SSC Eights!
SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)

Group: General Forum Members
Points: 818 Visits: 775
Andy,

I have a question about the operation and flow of records. Let's make the following change to your data.. Let's assume that the data coming in was using a super key (FirstName, LastName, Email). Then we write the data using only the PK E-Mail, but we grab all the records.

(Lets asume we had the following data already there.. Tim, Jones, tj@aol.com. : Now the data coming in.. (Tim, Jones, Timj@aol.com), (Tim, Jones, Jonest@aol.dom), (Tim, Jones, tj@aol.com)

I think the operation would be.. All records would go to the matching and then tj@aol.com would be dropped, since it matches. The others would update the existing record, so only the last write would exist in the database.

My test seems to prove this... just want to make sure my logic is correct.
sbramblet
sbramblet
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 74
Yes, I know this is way old but I've used Stairway series before and so I'm just now going through this one as I learn SSIS for work...

First off, thanks for the great series! It's so easy to go through step-by-step and learn the concepts as you go!

Secondly, I am at the part where I'm trying to click the 'New' button next to the Name of the table or the view on the OLE DB Destination Editor window. The problem is, there is no New button! What's up with that?

Thanks and keep up the good work!

EDIT: Nm, I figured it out. I had forgotten to join the Conditional Split first. Ooops!

EDIT2: Sorry, but something I noticed is that even though I have the tasks in the correct order:

TRUNCATE
|
DATA FLOW
|
APPLY STAGED UPDATES

When I view the Progress tab after running it, it appears that Truncate is running last? I could verify this by checking the StageUpdates table after the initial run. It had no records. I assumed the order of task execution was determined by the visual design. What am I missing? thanks!
Louise-604184
Louise-604184
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 3522
I had been scratching my head over this for 2 days. For me it was char 56. Copying the string to Notepad and stripping out the spaces worked for me too. I had no idea hidden characters could be inserted in this way.
dwilliscp
dwilliscp
SSC Eights!
SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)

Group: General Forum Members
Points: 818 Visits: 775
Hey Andy.. When you are working on the OLE DB Destination, why would I get the error "No column information was returned"?

I have the update statement, just like the one I did for this training.. If I put it into SSMS and replace the question marks, it updates the table just fine.

I looked at the data flow path editor and it shows all the columns from the table read, and the column added from the lookup (PK for the table to write to).
skendrick
skendrick
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 24
Hello Andy,
Thank you for a great series and explanations with the figures.
Very straight forward.

My current issue is that I can't not get the parameters shown when I am setting up on the OLE DB Command. I tried using the update * set * = ? and I don't see the parameters list available. like Param_0, Param_1

Is there a way to manually setup the parameters or do you happen to know why they do not show up?
skendrick
skendrick
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 24
I finally got it to work. I was having issue with the data type and also the order of the parameters selected had to match what I was having modified into the database.

Thanks again for the wonderful guide lines.
nishantd
nishantd
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 3
Hi Everyone,
I am a beginner in SSIS and was following the tutorial to learn and got stuck at one step.
In the Advanced Editor for OLEDB Command while column mapping, there are no columns like param_0,1,2,3.
Don't know what I did wrong. Can someone help me.

-Nishant
thomland
thomland
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 8
Hey guys,

can you give me any hint how to implement the delete function in the same dataflow?

Thanks in advance!

Kinde regards Tom
jetboy2k
jetboy2k
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 63
DOH!! Never mind...

If the MiddleName is NULL in both records, it hasn't changed, and we don't need to update that record.

Again...DOH!!

I'm unclear about how the Isnull formula applies in the following expression:

FirstName != LkUp_FirstName) || ((ISNULL(MiddleName) ? “Humperdinck” : MiddleName) != (ISNULL(LkUp_MiddleName) ? “Humperdinck” : LkUp_MiddleName)) || (LastName != LkUp_LastName)


I understand the need to "capture" NULLs, but in looking at the above expression, I'm thinking in terms of a record where the middle name is NULL in both the source and destination tables. In this case, I would think that the following results would be generated for the MiddleName ad LkUp_MiddleName values where the Middle Name is in fact Null in both tables:

(ISNULL(MiddleName) ? “Humperdinck” : MiddleName) => “Humperdinck”
(ISNULL(LkUp_MiddleName) ? “Humperdinck” : LkUp_MiddleName) => “Humperdinck”

And if that's correct, then the result of the comparison operator would be:

“Humperdinck” != “Humperdinck” => FALSE

So, assuming that FirstName and LastName have not been updated, you would get FALSE for all three comparisons, which would of course be a FALSE for the final condition result, and the Lookup Match Output would not include this row, since there are no matches.

That said, it's obvious from the following image that the comparison does work, so can you help me understand what I'm missing?

Thanks.

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search