Re: SSIS and Staging Tables

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
    ... 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 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: DTS lookups and connections
    ... Allan Mitchell MCSE,MCDBA, ... >> Having>1 lookup per row is disastrous for performance. ... >>> I ALREADY KNOW THAT WHAT I'M ASKIN ABOUT IS WHY A CONNECTION IS OPEND ... >>> AUDIT LOGIN ...
    (microsoft.public.sqlserver.dts)
  • Re: SSIS and Staging Tables
    ... If the lookup is not a match in the destination table ... The error output then runs to an OLEDB transform. ... the input columns in your case to an update statement. ... understanding of something fundamental in the Data Flow Task process. ...
    (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)
Quantcast