Extreme SSIS Newbie needs help with a super simple conditional insert

Question

Extreme SSIS Newbie needs help with a super simple conditional insert

Jeff Moden

SSC Guru

Points: 1004363
More actions
February 2, 2010 at 3:06 pm

#137005

Ok... don't laugh too hard at me, please. 😀 I have finally been thrown into the world of SSIS and I know just enough about it too be really dangerous. I can't seem to find a way to use only SSIS transforms to do a super simple conditional insert from one table to another.
Why only SSIS transforms and no scripts? It's a requirement I've been given for a task by the big guns in NY. As a side bar, I know how to do this very easily in T-SQL but it doesn't make sense for me to have to write T-SQL to support SSIS in what I thought was going to be a simple SSIS task. It would be really great to learn how to do this all in SSIS sans scripts.
Anyway, here are some simple tables with their population scripts...
--===== Create and populate the source table
CREATE TABLE dbo.SourceTable
(ID INT IDENTITY(1,1), SomeValue INT)
INSERT INTO dbo.SourceTable
(SomeValue)
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4
--===== Create and partitially populate the desintation table.
-- NOTE THAT THE VALUE OF '3' IS MISSING.
CREATE TABLE dbo.DestinationTable
(ID INT IDENTITY(1,1), NvarcharID NVARCHAR(256))
INSERT INTO dbo.DestinationTable
(NvarcharID)
SELECT '1' UNION ALL
SELECT '2' UNION ALL
-- SELECT '3' UNION ALL --Missing ID here
SELECT '4'
Notice that the source table has 4 rows numbered 1 - 4 and the destination table has 3 rows numbered 1 -2, 4 and that the number "3" is missing from the destination table.
Notice also that these numbers in the source table are INT and in the destination table, they are NVARCHAR (I did figure out how to use the "Data Conversion" transform). Yeah, I know... but don't pork chop me... I didn't design these things and, apparently, they're not going to change them because there will be other tables feeding the destination table and the data is not guaranteed to be numeric.
All I want to do is a conditional insert from the SourceTable to the DestinationTable and the condition is simple... if the ID from the SourceTable doesn't exist in the NvarcharID of the DestinationTable, then I want to ad that missing ID from the SourceTable to the DestinationTable. That's it.
Heh, I say "that's it", but I'll be damned if I can figure it out. I keep getting a basic "no data returned" from the "Lookup" transform... it never gets to the "Conditional Split".
I sure could use some help on this. I'm a genuine SSIS newbie and I've learned just enough today to know what some of the transforms are supposed to do. :blush: Thanks folks.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Viewing 12 posts - 1 through 11 (of 11 total)

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

Oh yeah... almost forgot. While this seems to be a "natural" for replication, one of the requirements is to not use replication. I also can't simply use a synonym or passthrough view because, like I said the writeup, other things will feed this table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dan Guzman - Not the MVP Hall of Fame Points: 3757 More actions · Answer 2

So, by no scripts, do you mean no Objects called 'Execute SQL Task'? or Just specifically 'Script Task'? or something else outside of SSIS?

The source of your problem is that there is no way to do a join of two or more tables as objects inside the SSIS environment. You can't have two source objects, then create a join object, and have a result set object the is a left join result of the other two tables.

Conditional Splits and any other object for that matter will only work on one table at a time.

Here is what I'm thinking might work:

Use a Flow Control to an OLE Source that outputs the left join result of the missing fields, into a user variable ADORecordset.

For each loop through each record in the ADORecordset:

Inside the loop do

Add a Derived Column to your table that is Zero unless the value field equals the user value from the ADORecordset field, then use 1

Then use the conditional split to only allow the 1's in the derived column to output to the destination.

end loop

end package

Dan

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

Thanks for the response, Dan.

Yep, I know that you can't have a two (or more) table source (well... Merge Join almost does that). I have an OLE DB source for the SourceTable that feeds a dataconversion (unicode string (WSTR) output) that feeds

a "Lookup" that compares it's input (from the dataconversion) to the DestinationTable. I've assigned the columns from the input and the destination table to be equal to each other and there are actually some matching rows in the destination table. Still, I get the basic "no rows returned" error.

It's starting to look like I might as well do this in T-SQL. 😛 Thanks for your time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rka SSCommitted Points: 1599 More actions · Answer 4

Hi Jeff,

You can use the OLEDB Source, Lookup and OLEDB Destination tasks to do this. But you will have to configure the error output in Lookup Task to redirect row to OLEDB Destination to do an Insert. Please refer to the attachment.

Cheers

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

ritz-782912 (2/2/2010)
Hi Jeff,
You can use the OLEDB Source, Lookup and OLEDB Destination tasks to do this. But you will have to configure the error output in Lookup Task to redirect row to OLEDB Destination to do an Insert. Please refer to the attachment.
Cheers

Thanks Ritz! I believe that's the ticket! Glad I posted some table creation examples for you to play with... you're sure helped me. Thanks for being there.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 6

Ritz,

The mapping you did was a bit different than what I wanted but I took care of that. I've been fighting this all day long because I didn't know about the error redirection. I just wanted to say "Thanks" again especially for the time you took to add the graphics in your explanation. If "One picture is worth a thousand words", just imagine what the 8 you included in the Word document solution mean to me!

I'll pass the favor forward in a few by posting what I did in a similar fashion to what you did.

Thanks again!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 7

Dan, thanks again for giving it a shot. I appreciate the time you took.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 8

Ritz,

Just one more question... did you ever take the time to write the article on this subject? If not, I (again) think you'd really be good at it. Consider it... you'd help a lot of good people. Even the person at work who was hired for her abilities in SSIS didn't know this one.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rka SSCommitted Points: 1599 More actions · Answer 9

Hi Jeff,

I am glad to help. I usually do not write any articles but with the feedback I am getting from people may encourage me to write some articles and blogs.

Cheers

Dan Guzman - Not the MVP Hall of Fame Points: 3757 More actions · Answer 10

Very nice ritz,

I learned 2 things here, most importantly that Lookups allow left joins. I don't see it here, so I'll ask: Is the only way to do multiple joins is to have the 'second table' built with any other joins, inside the 'Use results of an SQL query' option for the reference table?

Thanks again.

PS Jeff, someday I'll post my real world 'I bet you can't get rid of this cursor/while loop' stumper

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 11

Dan Guzman - Not the MVP (2/3/2010)
PS Jeff, someday I'll post my real world 'I bet you can't get rid of this cursor/while loop' stumper

I'd imagine that a lot of the heavy hitters on this forum would have some fun with it. Looking forward to it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)