Re: SSIS and Staging Tables

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@xxxxxxxxx wrote:
Absolutely brilliant!


Allan Mitchell wrote:
Hello tbmilk@xxxxxxxxx,

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

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@xxxxxxxxx wrote:

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

Allan Mitchell wrote:

Hello tbmilk@xxxxxxxxx,

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/SQLISWiki/LookupTransformErrorOut
put.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/SQLISWiki/OLEDBCommandTransformat
ionAndIdentityColumns.html

Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com
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


.

Relevant Pages

  • Re: SSIS and Staging Tables
    ... What I did was I took my original lookup (from the Flat File Source) ... I sent one path to my OLE DB UPDATE ... One path goes to my UPDATE command....This transform is already ...
    (microsoft.public.sqlserver.dts)
  • Re: SSIS and Staging Tables
    ... Allan Mitchell wrote: ... OK So a lookup must be against a RDBMS and a Text File is most definitely ... Two sources to the pipeline (Text File + OLE DB) ... map the input columns in your case to an update statement. ...
    (microsoft.public.sqlserver.dts)
  • Re: SSIS and Staging Tables
    ... OK So a lookup must be against a RDBMS and a Text File is most definitely not one of them. ... Two sources to the pipeline (Text File + OLE DB) ... if there is any way (within the Data Flow Task) to ... map the input columns in your case to an update statement. ...
    (microsoft.public.sqlserver.dts)
  • Re: SSIS, Lookups, and Redirect Row
    ... The rows you pipe to the error output can be used however you want. ... Using the OLE DB transform then you would have to issue a statement ... If you do not like this then stage these rows and outside of the data flow issue a set based UPDATE in an ExecuteSQL task. ... If I have a lookup comaring two sources ...
    (microsoft.public.sqlserver.dts)
  • Re: exporting to access
    ... Statement in your OLE DB Source task inside your data flow. ... To export to Access your destination will need to be an OLE DB ... destination using the Microsoft Jet 4.0 driver, the Excel destination ... package in BIDS and have a look at what has been generated. ...
    (microsoft.public.sqlserver.dts)