I have:

1. A delimited text file with 2 columns (ID, Value)
2. DB table (ID, Value)

I would like to create a Data Flow Task that:

1. Extracts the data from the text file
2. Compares Value from the text file using ID to bind with the DB
table
3. UPDATES the non-matching (or changed) Values into the DB table

I would like to achieve all of this WITHOUT creating temporary staging
tables. I was under the impression that SSIS and ETL was supposed to
provide the ability to cut down (or even eliminate) the need for
staging tables.

I feel like I am missing a major component or lacking the understanding
of something fundamental in the Data Flow Task process.

Any assistance to point me in the right direction would be greatly
appreciated.

Thanks

I found a solution to my problem. Because I haven't found any related
posts, I will post my solution below:

What I ended up doing was:

In the Data Flow Task I created a Text Source connection which
connected to a recordset. I then connected the recordset to a variable
that was CREATED IN THE CONTROL TASK (not in the Data Flow Task). This
variable's type must be set to OBJECT.

I then went to the Control Task and connected the Data Flow Task to a
For Each Loop.

I set the Enumerator of the For Each Loop to: "For Each ADO
Enumerator". Then I set the ADO Object Source variable to the object
variable that I created for the recordset.

I then went to "Variable Mappings" and created variables to link to
columns in the recordset. These variables will be used later.

I then connected the For Each loop to a SQL Task.

Here is an example of pretty much the same thing (He uses a Script
instead of an SQL Task, same concept though.):

http://blogs.conchango.com/jamiethom...6/15/1693.aspx


In the SQL Task I used Parameter Mapping to map each of the variables
to parameters in my query. I'm using an ODE DB connection so my query
looked like this:

UPDATE <table>
SET Value = ?
WHERE ID = ?

In the parameter mapping section (because I am using ODE DB) I set the
parameter names to the column index (i.e. 0, 1, etc.)

For more information on parameters and connections go here:

http://msdn2.microsoft.com/en-us/library/ms140355.aspx


Essentially, this solution goes through each row of the recordset and
UPDATES my table accordingly.

I'm sure this was probably child's play for most of you...but took me
forever to piece together and figure out!

Hello tbmilk (AT) gmail (DOT) com,


Ok so here is how I would have done it


Text File source adapter

Lookup transform. If the lookup is not a match in the destination table
then it follows the Error output.

The error output then runs to an OLEDB transform. This has the update statement.

Here are some articles that will help to show what I mean



Lookup Error output

http://wiki.sqlis.com/default.aspx/S...rorOutput.html


OLE DB trandform. You do not need to use a proc here. You can just map
the input columns in your case to an update statement.

http://wiki.sqlis.com/default.aspx/S...tyColumns.html



Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Thanks for posting this...this idea definately seems like a slick way
of tackling this issue.

Allan Mitchell wrote:

I have implemented your suggestions and it works WAY better than my
idea.

I'm wondering now, if there is any way (within the Data Flow Task) to
kind of do the reverse.

For example:

Lets say a User ID has been removed from our system and therefor it is
no longer in the text file source.

Is there a way I can perform a lookup that will allow me to run a
DELETE OLE DB command on the DB table?

In essence. If the row IS NOT in the text file, I do not want it in
the DB table.

I find that INSERT and UPDATE work great using your example, however,
DELETE is proving problematic

tbmilk (AT) gmail (DOT) com wrote:

Hello tbmilk (AT) gmail (DOT) com,

OK So a lookup must be against a RDBMS and a Text File is most definitely
not one of them. here is an idea.

Two sources to the pipeline (Text File + OLE DB)
Merge Join (LEFT OUTER JOIN) so your text file is on the left hand side (INPUT)
and the OLE DB Connection is the right hand side (INPUT)
Join the two inputs on the respective columns
When rows come flowing through those that do not match will have a NULL associated
with them.
Use a Conditional split to pipe off these rows to an OLE DB transform.



Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Absolutely brilliant!


Allan Mitchell wrote:

Okay...this will be the last question for this thread :-)

I currently have the ability to UPDATE and DELETE records in my OLE DB
destination based on my Flat File Source.

I am trying to create a OLE DB Destination that will INSERT INTO my DB
table. I am trying to use the MERGE JOIN transform (ala what you said
about DELETING) however it does not work. (I can't check for a <Null>
value because I need that value to INSERT into my table)

Here is an example:

Flat File:

ID = 1, Value = A
ID = 2, Value = C

Database Table:

ID = 2, Value = B
ID = 3, Value = D

Now...

1. The lookup is going to detect that ID = 2 Value needs to be changed
to C....Done!

2. The MERGE JOIN is going to JOIN the Flat File and Table and return
a <Null> for Value on ID = 3....the Condition checks for the <Null> and
DELETES row......Done!

3. I need to INSERT ID = 1 into the table......HELP!

tbmilk (AT) gmail (DOT) com wrote:

Looks like I found a solution quicker than I thought. I will post it
to the thread since we have some great documenation here:

What I did was I took my original lookup (from the Flat File Source)
and dumped that into a multicast. I sent one path to my OLE DB UPDATE
command (which is working fine) and I then sent another path to another
Lookup.

I had the second lookup perform a lookup on the DB table using ONLY ID
as a Join column.

Using the previous example:

The first lookup detects that there are two rows that do not match (ID
= 1 and ID = 2 [ID = 3 is sent down another path to a JOIN]). It
forwards those two rows to the multicast.

One path goes to my UPDATE command....This transform is already
working...see earlier in this thread.

The other path goes to another LOOKUP. This time, it is able to match
ID =2 but it cannot match ID = 3....it creates and Error. I configured
the error output to redirect that row to my OLE DB INSERT command.

Viola!



tbmilk (AT) gmail (DOT) com wrote: