Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Updating Rows in Incremental Loads – Level 4 of the Stairway to Integration Services Services Expand / Collapse
Author
Message
Posted Tuesday, February 26, 2013 7:39 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, January 28, 2016 12:29 PM
Points: 385, Visits: 702
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.
Post #1424082
Posted Thursday, October 24, 2013 8:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 25, 2015 12:10 AM
Points: 3, 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!
Post #1508314
Posted Thursday, March 20, 2014 6:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 3:54 AM
Points: 13, Visits: 3,522
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.
Post #1552977
Posted Friday, May 9, 2014 1:57 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, January 28, 2016 12:29 PM
Points: 385, Visits: 702
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).
Post #1569441
Posted Thursday, May 15, 2014 9:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 23, 2015 11:24 AM
Points: 2, 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?
Post #1571372
Posted Tuesday, May 27, 2014 8:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 23, 2015 11:24 AM
Points: 2, 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.
Post #1574795
Posted Monday, November 17, 2014 11:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 17, 2014 11:59 PM
Points: 1, 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
Post #1635834
Posted Saturday, June 27, 2015 10:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 15, 2015 9:32 AM
Points: 2, 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
Post #1698329
Posted Thursday, January 28, 2016 10:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 5:41 PM
Points: 3, Visits: 13
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.
Post #1756464
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse