Level 3 of the Stairway to Integration Services - Adding Rows in Incremental Loads

Question

Level 3 of the Stairway to Integration Services - Adding Rows in Incremental Loads

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

shalinder.verma Mr or Mrs. 500 Points: 539 More actions · Answer 1

This is a good article for the beginners. Great work Andy!

I would also stress on looking at Merge TSQL statement. Have used it quite a few times. It would be interesting to compare the performance and pros/cons with Lookup transformation.

Also, with Merge we can handle SCD1 and SCD2 quite easily. I reckon this is required in most scenarios for incremental dimension loading. This way we can do away with bad SCD transformation available with SSIS out of the box.

My 2 cents.

JAMESC2003 SSC Enthusiast Points: 138 More actions · Answer 2

Write a book on SSIS.

I suspect it will be 200,000 pages .....

The blow-by-blow (with background explanations) are

immensely helpful.

I will still buy it .... even if it costs $100

Rob de Vos SSChasing Mays Points: 646 More actions · Answer 3

in fact there is a book about SSIS although the title suggests something else..

the title is Implementing a Data Warehouse with Microsoft SQL server 2012 (exam 70-463)

It covers a lot of SSIS but also Master data services, Data Cleansing and Data Warehouse, most of the tasks are done using SSIS.

I found 2 problems,

1 the ODBC connections only work in 32-bit mode

2 the Master Data web interface is in Dutch I have configured the server to be in my home country, suddenly the exercises are a real puzzle 🙂

courtney.fletcher SSC Rookie Points: 25 More actions · Answer 4

I agree but I don't see a reply. Did he reply to you?

Rob de Vos SSChasing Mays Points: 646 More actions · Answer 5

Rob de Vos

SSChasing Mays

Points: 646

May 31, 2015 at 11:46 pm

#1801916

no, never received a reply.

lucianh SSC Rookie Points: 32 More actions · Answer 6

Hi Andy and all.

I am in a bit of a rut. Well I just started on SQL server central and I started the SSIS stairway series, I have played along at home with this up until I got stuck:ermm: at level 3, well the thing is you can only edit so much of another database tables and fields when it's different from the the one you are using in the Stairways SSIS series, I went to the Codeplex to get the Adventure-works 2012 database, just to find out its different, I later returned to download all the 2008 databases 🙁 (all of em)...that's what made it worse, it is almost exactly the same as the Adventure-works 2012 database, what am I missing?where can I find the database with the exact sample code that you were using Andy?

I really love this Stairways series thus far, but I love doing stuff hands on as well. If I don't do something physically myself. I will forget it, not even forget it. It wont even sink in 😉 ...

Please pleeeaaa please help.

Regards,

Lucian

jetboy2k Mr or Mrs. 500 Points: 529 More actions · Answer 7

In Stairway to SSIS Level 2, the Destination OLE DB was configured to host a table named CONTACT by using the SQL CREATE TABLE command. Then, in SSIS Level 3, we see that the Data Flow task does a complete copy of all of the data selected from the Source OLE DB into the Destination OLE DB by running it again and then seeing how many rows a select * from CONTACT query returns.

Then, to clean it up, we are shown the Delete TABLE CONTACT command in SSMS.

This all makes sense to me, but does raise a question in my mind - obviously, the second time the Data Flow Task was run, it didn't balk at the CREATE TABLE CONTACT command, even though the CONTACT table already existed. So, can we assume that somewhere behind the scenes, SSIS "wraps" any CREATE TABLE commands with a conditional check and, if it sees that the table already exists, just connects to it and then executes the rest of the Data Flow Task?

Thanks.

jetboy2k Mr or Mrs. 500 Points: 529 More actions · Answer 8

NYProjectLeader - Friday, September 16, 2011 6:45 AM
Below figure 20, there is a statement that reads, "In SSIS 2008 and SSIS 2008 R2, the Lookup Transformation provides this built-in output to catch records in the Lookup table (the dbo.Contact destination table, in this case) that do not exist in the source (the Person.Contact table) - it's the Lookup No Match Output."Shouldn't this be the other way around? " In SSIS 2008 and SSIS 2008 R2, the Lookup Transformation provides this built-in output to catch records in the Source table (the dbo.Contact destination table, in this case) that do not exist in the lookup (the Person.Contact table) - it's the Lookup No Match Output."

NYPL, while it's been quite some time since you posted this comment, I believe that you are correct. Unfortunately, while this is a great article, and I'm looking forward to going through the entire stairway, when being introduced to a topic such as this, something like the above can be VERY confusing. I had to read it three or four times myself before I realized that it was in fact in reverse (and, if you read the very next sentence, you'll see that it does list the two tables in the reverse order), and then convince myself that I wasn't misunderstanding how it works, it was just written incorrectly.

Again, this is a great article and, I suspect, a great series, but a typo this severe really should be corrected.