Conditional Split kicking my tail

Question

Post reply

Conditional Split kicking my tail

sqlsponge2

SSC Eights!

Points: 902
More actions
January 23, 2012 at 4:41 pm

#249542

I am trying to use a conditional split to load a history table. I am trying to determine if the records exists and needs to be updated or if it is new and needs to be inserted.
When I put data viewers on my source and then the step after the lookup I get the screenshot. It is showing the highlighted record on the source, but it is showing nulls on the output of the lookup. This record exists and should not show null. The nulls are the new records requiring an insert.
Basic structure of what I am doing.
Master Production Table loads all data to Staging
Staging is compared to ProductionHistoryTable (determine if records exist from Staging not in History and insert or update as needed)
I am looking for any advise on how I can troubleshoot this further and get it to work.
Thanks

Viewing 15 posts - 1 through 15 (of 24 total)

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

How do you check if a record is an insert or an update?

What datatype does the column OrderGroupID have?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

sqlsponge2 SSC Eights! Points: 902 More actions · Answer 2

I am doing a check for ISNULL to see if the record exists on the target for the inserts and doing a compare on every field for the updates. See the screen shot of the conditional split.

OrderGroupId is a uniqueidentifier.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

OK. How do you match source and target together? Using joins or the Lookup component?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

sqlsponge2 SSC Eights! Points: 902 More actions · Answer 4

The lookup function between my ole db data source and the conditional split that returns all the records in my MasterHisoryTable. The ole db is returnin all records from my Staging table. So I am trying to find the new records in Staging that are not in MasterHistoryTable and insert them and then update any records that already exist.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 5

So you use a Lookup component to match the rows between staging and MasterHistoryTable.

How did you configure this component?

If you would implement this solution using TSQL only - using left outer join - do you get the same results?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

sqlsponge2 SSC Eights! Points: 902 More actions · Answer 6

I know if I run queries in SSMS using joins I get the results I am looking for. Not sure how to incorporate that into SSIS. I am a newby and not sure which components to use and connect?

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 7

sqlsponge2 (1/24/2012)
I know if I run queries in SSMS using joins I get the results I am looking for. Not sure how to incorporate that into SSIS. I am a newby and not sure which components to use and connect?

So maybe there is an issue in the Lookup component. How did you configure this one?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

sqlsponge2 SSC Eights! Points: 902 More actions · Answer 8

The lookup simply looks at the OLE DB connection that connects to the History Database and then runs a query to return all rows from the MasterHistoryTable. Then the columns are mapped and I renamed the Output Alias and put Target_ in front of each column. There are not any other settings in the Lookup component.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 9

Are you using SQL Server 2005 or 2008?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

sqlsponge2 SSC Eights! Points: 902 More actions · Answer 10

sqlsponge2

SSC Eights!

Points: 902

January 24, 2012 at 7:01 am

#1437656

This project is running SQL 2005.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 11

sqlsponge2 (1/24/2012)
This project is running SQL 2005.

So the MERGE statement is not an option, too bad.

Back to the Lookup component. If you run the query that retrieves the records from the MasterHistoryTable, is the row highlighted in your first screenshot returned?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

sqlsponge2 SSC Eights! Points: 902 More actions · Answer 12

Yes, the first screenshot on the left is the data viewer that displays records coming out of the OLD DB Data Source which is the Staging. Then the second screenshot is a data viewer of what is coming out of the Lookup. You see the OrderGroupId has the value, but the Target_OrderGroupId has null. Not sure why it is showing null when it exists in both tables.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 13

sqlsponge2 (1/24/2012)
Yes, the first screenshot on the left is the data viewer that displays records coming out of the OLD DB Data Source which is the Staging. Then the second screenshot is a data viewer of what is coming out of the Lookup. You see the OrderGroupId has the value, but the Target_OrderGroupId has null. Not sure why it is showing null when it exists in both tables.

How do you know it exists in both tables?

If you run the query defined in the Lookup component, is the row retrieved?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

sqlsponge2 SSC Eights! Points: 902 More actions · Answer 14

This is a screenshot of the join I can run in SSMS that shows the record 700 which is in both tables and then the records 701 - 710 are the records only in staging that are to be inserted.