Lookup Match n No Match

Question

Lookup Match n No Match

yrstruly

Hall of Fame

Points: 3475
More actions
February 20, 2021 at 5:43 am

#3847611

I would like to compare two files, that should give me. a Match and No Match output in separite tables. Can somebody send ma screenshot/link of such a ssis package

-_-----------------
Can somebody tell me, does the following task have to be do difficult?
https://drive.google.com/file/d/0ByFnKe4djAO-Y1JPYktEbVBCQTdkM1ZsSG1jY0k3dmRLbDdz/view?usp=drivesdk

Viewing 6 posts - 1 through 5 (of 5 total)

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

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

What is the criteria for your Match/NoMatch condition?

For example... are you looking for matches by whole line in the files? If not, how do you need to differentiate which parts of the lines of the files do you want to compare with?

Also, are you basing comparisons of lines by the order that they appear in the file or not? In other words, do the files have a meaningful positional sequence? Or, do they have some unique value that identifies the sequence? Or is there no particular sequence and you want to compare all lines of one file to all lines of the other for Match/NoMatch conditions?

Last but not the least, what are you going to do with the lines of data once you've made the Match/NoMatch determination? For example, will you need to "split" the lines into columns and figure out what part of each line changed to drive some updates using only the data that changed from each line or ??? What is the end goal here?

--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)

yrstruly Hall of Fame Points: 3475 More actions · Answer 2

Thank you for the response. I am using unique bar codes in both files. I want the result to split in two, match and no match OlE DBs.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

yrstruly wrote:

Thank you for the response. I am using unique bar codes in both files. I want the result to split in two, match and no match OlE DBs.

When you state 'file' - do you really meant 'table'?

If these are actual files in the file system - then I would recommend using a file comparison utility instead of trying to do this in SQL. I use the tool Beyond Compare by Scooter Software and have found it to be one of the better tools available.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 4

Jeffrey Williams wrote:

yrstruly wrote:
Thank you for the response. I am using unique bar codes in both files. I want the result to split in two, match and no match OlE DBs.
When you state 'file' - do you really meant 'table'?
If these are actual files in the file system - then I would recommend using a file comparison utility instead of trying to do this in SQL. I use the tool Beyond Compare by Scooter Software and have found it to be one of the better tools available.

If they are in files, why not just import the files and do the comparison in the database, which is probably the ultimate destination for the in for any way.

--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: 1004324 More actions · Answer 5

yrstruly wrote:

I would like to compare two files, that should give me. a Match and No Match output in separite tables. Can somebody send ma screenshot/link of such a ssis package
-_-----------------
Can somebody tell me, does the following task have to be do difficult?
https://drive.google.com/file/d/0ByFnKe4djAO-Y1JPYktEbVBCQTdkM1ZsSG1jY0k3dmRLbDdz/view?usp=drivesdk

I'm a bit confused. The Google Drive document that you included a link to explains how to do what you want. Why do you need our help?

--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)